{"action":"created","issue":{"url":"https://api.github.com/repos/confluentinc/ksql/issues/2177","repository_url":"https://api.github.com/repos/confluentinc/ksql","labels_url":"https://api.github.com/repos/confluentinc/ksql/issues/2177/labels{/name}","comments_url":"https://api.github.com/repos/confluentinc/ksql/issues/2177/comments","events_url":"https://api.github.com/repos/confluentinc/ksql/issues/2177/events","html_url":"https://github.com/confluentinc/ksql/issues/2177","id":383078250,"node_id":"MDU6SXNzdWUzODMwNzgyNTA=","number":2177,"title":"DROP [STREAM|TABLE] should support termination of query started during creation.","user":{"login":"big-andy-coates","id":8012398,"node_id":"MDQ6VXNlcjgwMTIzOTg=","avatar_url":"https://avatars0.githubusercontent.com/u/8012398?v=4","gravatar_id":"","url":"https://api.github.com/users/big-andy-coates","html_url":"https://github.com/big-andy-coates","followers_url":"https://api.github.com/users/big-andy-coates/followers","following_url":"https://api.github.com/users/big-andy-coates/following{/other_user}","gists_url":"https://api.github.com/users/big-andy-coates/gists{/gist_id}","starred_url":"https://api.github.com/users/big-andy-coates/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/big-andy-coates/subscriptions","organizations_url":"https://api.github.com/users/big-andy-coates/orgs","repos_url":"https://api.github.com/users/big-andy-coates/repos","events_url":"https://api.github.com/users/big-andy-coates/events{/privacy}","received_events_url":"https://api.github.com/users/big-andy-coates/received_events","type":"User","site_admin":false},"labels":[{"id":1076527231,"node_id":"MDU6TGFiZWwxMDc2NTI3MjMx","url":"https://api.github.com/repos/confluentinc/ksql/labels/operability","name":"operability","color":"f963b8","default":false},{"id":866292357,"node_id":"MDU6TGFiZWw4NjYyOTIzNTc=","url":"https://api.github.com/repos/confluentinc/ksql/labels/user-experience","name":"user-experience","color":"6231c4","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2018-11-21T11:37:16Z","updated_at":"2018-11-21T14:52:43Z","closed_at":null,"author_association":"MEMBER","body":"If I create a stream that starts a query in the background, e.g.\r\n\r\n```sql\r\nCREATE STREAM S AS SELECT c1, c2 FROM T;\r\n```\r\n\r\nThen I should be able to drop this stream _without_ needing to _find_ the query Id and terminate the query. e.g. I should be able to do:\r\n\r\n```sql\r\nDROP STREAM S;\r\n```\r\n\r\nThis currently complains, due to inter-stream referential integrity checking.  (The query is seen as a source to stream 'S', so 'S' can't be dropped while the query is running).\r\n\r\nThe issue here is that the query id is non-deterministic, being based on an internal counter.  So its not possible to submit a script to the rest api that drops and recreates a stream, as you can't add suitable `TERMINATE` call.\r\n\r\nThe complexity comes about when we have `INSERT INTO S` queries running into the same stream/table. I think it is OK/correct in such a situation that the `DROP STREAM S` to complain. However, we should have a way of overriding this so that even that query is stopped.\r\n\r\nI would propose that `DROP STREAM S` should automatically stop any query that was started as part of the original `CREATE STREAM S AS` statement.  But the presence of other queries that insert _into_ the stream should cause it to fail, unless the user requests us to stop them, e.g. we could support something like:\r\n\r\n```sql\r\n-- use if you want any other source queries to automatically be stopped\r\nFORCE DROP STREAM S;  \r\n```\r\n\r\nThe `DROP` command will also fail if this stream is used as a source for other queries. This should continue to cause the DROP to fail.\r\n\r\nI guess an alternative would be some syntax to allow your to terminate a queries based on the stream/table they output too, e.g. \r\n\r\n```sql\r\n-- terminate all queries that output to stream S.\r\nTERMINATE ALL FOR STREAM S;\r\n```\r\n\r\nThis approach differs from having a straight `DROP STREAM S` automatically stopping the query started by its CSAS statement in that with the above `TERMINATE FOR STREAM` syntax the user can't choose to drop _only if the only query running is the one auto created during the CSAS statement_.\r\n\r\nI prefer the implicit terminate approach because it differentiates between those queries created during the CSAS/CTAS and those that weren't.  So you can do this:\r\n\r\n#####  Straight create / drop example:\r\n```sql\r\nCREATE STREAM S1 AS SELECT * FROM FOO;\r\n-- This should work, automatically stopping the query created in the line above\r\nDROP STREAM S1;\r\n```\r\n\r\n#####  With insert into example:\r\n```sql\r\nCREATE STREAM S2 AS SELECT * FROM FOO;\r\nINSERT INTO S2 SELECT * FROM BAR;\r\n\r\n-- Drop should fail as there are other queries that write to S2.\r\nDROP STREAM S2;\r\n\r\n-- a forced drop should still succeed:\r\nFORCE DROP STREAM S2;\r\n```\r\n\r\n#####  With downstream dependencies example:\r\n```sql\r\nCREATE STREAM S2 AS SELECT * FROM FOO;\r\nCREATE STREAM S3 AS SELECT * FROM S2;\r\n\r\n-- Drop should fail as as this would break the query behind S3;\r\nDROP STREAM S2;\r\n\r\n-- Drop should _still_ fail as as this would break the query behind S3;\r\nFORCE DROP STREAM S2;\r\n```\r\n\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/confluentinc/ksql/issues/comments/440690801","html_url":"https://github.com/confluentinc/ksql/issues/2177#issuecomment-440690801","issue_url":"https://api.github.com/repos/confluentinc/ksql/issues/2177","id":440690801,"node_id":"MDEyOklzc3VlQ29tbWVudDQ0MDY5MDgwMQ==","user":{"login":"miguno","id":294849,"node_id":"MDQ6VXNlcjI5NDg0OQ==","avatar_url":"https://avatars3.githubusercontent.com/u/294849?v=4","gravatar_id":"","url":"https://api.github.com/users/miguno","html_url":"https://github.com/miguno","followers_url":"https://api.github.com/users/miguno/followers","following_url":"https://api.github.com/users/miguno/following{/other_user}","gists_url":"https://api.github.com/users/miguno/gists{/gist_id}","starred_url":"https://api.github.com/users/miguno/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/miguno/subscriptions","organizations_url":"https://api.github.com/users/miguno/orgs","repos_url":"https://api.github.com/users/miguno/repos","events_url":"https://api.github.com/users/miguno/events{/privacy}","received_events_url":"https://api.github.com/users/miguno/received_events","type":"User","site_admin":false},"created_at":"2018-11-21T14:52:43Z","updated_at":"2018-11-21T14:52:43Z","author_association":"MEMBER","body":"@big-andy-coates: Thanks for putting this together.  At large, this make sense to me.\r\n\r\n## Impact on dependencies?\r\n\r\nCould you please clarify the behavior for a stream's/table's dependencies:\r\n\r\n1. What's the desired behavior for queries/streams/tables that *write to* the [stream/table]-to-be-dropped when using a normal DROP?  In comparison, what's the desired behavior for a forced DROP? This is, as you already alluded to above, not just about the [stream/table]-to-be-dropped itself but also what happens to these related queries/streams/tables.\r\n    - From what I understand: For this scenario there's only one case today (apart from a stream's/table's \"own\" query as part of CTAS), which is a query, and that query is INSERT INTO, right?  And, fwiw, INSERT INTO is a DML statement.\r\n\r\n2. What's the desired behavior for streams/tables/queries that *read from* the [stream/table]-to-be-dropped?  In comparison, what's the desired behavior for a forced DROP? This is, as you already alluded to above, not just about the [stream/table]-to-be-dropped itself but also what happens to these related queries/streams/tables.\r\n    - From what I understand: This scenario seems to be DDL only, and that would be CTAS and CSAS statements.\r\n    - But what about temporary queries (SELECT * FROM streamOrTableToBeDropped) in case user A runs a temporary query on the stream/table while, in parallel, user B issues the DROP statement?\r\n\r\n## Relation of this ticket to other tickets\r\n\r\nAs you pointed out above, we also have the related ticket https://github.com/confluentinc/ksql/issues/2112 (~ `DROP CASCADE`).\r\n\r\nMy questions here:\r\n\r\n- _Can_ we really differentiate between the two tickets?\r\n- _Should_ we really differentiate?\r\n\r\nI think your argument is that any \"collateral damage\" of this ticket (beyond terminating its own query from a CSAS/CTAS) should be limited to what I listed as (1) further above in this comment, whereas https://github.com/confluentinc/ksql/issues/2112 is more about (2)?\r\n\r\n## Syntax feedback\r\n\r\nRegarding the syntax:  I'd prefer an approach based on `DROP`, too, rather than sth like `TERMINATE ALL FOR STREAM S.`\r\n\r\nFor what it's worth, here's what some RDBMS have chosen to implement.\r\n\r\n**PostgreSQL ([docs](https://www.postgresql.org/docs/11/sql-droptable.html)):**\r\n\r\nUses `DROP TABLE myTable` and `DROP TABLE myTable CASCADE`.  `DROP TABLE myTable` is actually an alias for `DROP TABLE myTable RESTRICT`.\r\n\r\n> CASCADE\r\n> Automatically drop objects that depend on the table (such as views), and in turn all objects that depend on those objects (see Section 5.13).\r\n>\r\n> RESTRICT\r\n> Refuse to drop the table if any objects depend on it. This is the default.\r\n\r\n**MySQL ([docs](https://dev.mysql.com/doc/refman/8.0/en/drop-table.html)):**\r\n\r\nConsiders `DROP TABLE ... [RESTRICT | CASCADE]` as valid syntax to make migration from other DBs like PostgreSQL easier, but itself it doesn't support this functionality.\r\n\r\n> The RESTRICT and CASCADE keywords do nothing. They are permitted to make porting easier from other database systems.\r\n\r\n**SQL Server:**\r\n\r\nFrom what I understand it doesn't support the DROP syntax directly.  Instead, it says:\r\n\r\n> DROP TABLE cannot be used to drop a table that is referenced by a FOREIGN KEY constraint. The referencing FOREIGN KEY constraint or the referencing table must first be dropped. If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.\r\n>\r\n> Multiple tables can be dropped in any database. If a table being dropped references the primary key of another table that is also being dropped, the referencing table with the foreign key must be listed before the table holding the primary key that is being referenced.\r\n>\r\n> When a table is dropped, rules or defaults on the table lose their binding, and any constraints or triggers associated with the table are automatically dropped. If you re-create a table, you must rebind the appropriate rules and defaults, re-create any triggers, and add all required constraints.\r\n\r\nNote: There's also a related option, which is the `ON DELETE CASCADE` clause when you are defining the table (DDL), but its use case is different because it deletes *records* from any child tables but it doesn't cause SQL Server to delete the child tables when doing a DROP."}}
{"actor":{"display_login":"miguno"}}