ksql-migrations and sequential dependencies / race conditions

[vcrfxia]

When using the ksql-migrations tool to apply a migration containing statements with depend on each other, sometimes race conditions are encountered. For example, the following migration may fail if the first command (TERMINATE QUERY) has not completed by the time the second command (DROP STREAM) is run, as the DROP STREAM will error saying that there is still a query writing into the stream.

TERMINATE <persistent_query_ID>;
DROP STREAM <persistent_query_sink>;

For CREATE STREAM AS SELECT queries, this specific situation can be avoided by using DROP STREAM directly (without the first command) to both terminate the query and drop the stream (c.f. #6143) but for INSERT INTO AS SELECT queries, this is not an option.

For other types of dependencies, such as CREATE CONNECTOR and CREATE STREAM (on the topic created by the connector), or dependencies involving schema creation in Schema Registry, users can use ASSERT TOPIC and ASSERT SCHEMA to resolve the timing / dependency constraints in their migrations (c.f. #8752). This approach was developed to resolve timing issues with commands which are inherently asynchronous, due to requiring interactions with ksql's dependency systems (Kafka, Connect, and Schema Registry), but does not apply in situations such as the TERMINATE and DROP STREAM example above, which affect only ksql itself.

To resolve this specific issue, we'd have to leverage the "command sequence number" functionality supported by the ksql REST API, whereby ksql clients can require that the ksql server handling the request has executed previous commands prior to handling the new one. To add support for this in the migrations tool, we'd have to first add support for this functionality in the ksqlDB Java client (for related context, see #6269 and search for "command sequence number").