Synchronous mutation and classic update/delete statement support

[zhicwu]

Mutation executes asynchronously in ClickHouse. And unlike other databases following SQL standard, it uses DDL instead of DML. As a result, this may break assumption in certain tools and generate overhead in downstream applications.

In order to follow standards, I hope we can:

1. introduce a new connection setting mutationsSync(same as mutations_sync in ClickHouse) defaults to 1, meaning synchronous mutation
2. <no longer needed as it's going to be implemented on server-side>
   take classic update/delete and transform them into ALTER TABLE... for mutation in ClickHouse
   So if you give UPDATE tbl SET col1=1, col2=2 WHERE col3=3, it will be translated into ALTER TABLE tbl UPDATE col1=1, col2=2 WHERE col3=3.

[filimonov]

2 is a bad idea. Mutations were made this way (with special syntax) intentionally - current ALTER UPDATE / DELETE are very expensive operations, which should be used with care, not too often.

Drivers should NOT introduce any magic to pretend that clickhouse has updates or deletes (hopefully they will be added in 2021).

[zhicwu]

2 is a bad idea. Mutations were made this way (with special syntax) intentionally - current ALTER UPDATE / DELETE are very expensive operations, which should be used with care, not too often.

Drivers should NOT introduce any magic to pretend that clickhouse has updates or deletes (hopefully they will be added in 2021).

Thanks @filimonov. Got same feedback from @den-crane :) Any concern for synchronous mutation by default?

[filimonov]

Any concern for synchronous mutation by default?

My preference is to map clickhouse settings as is (not inverting them) and having the same defaults as clickhouse.

So if ClickHouse has mutations_sync, driver may have mutationsSync, and if the user need to run them syncronously he can just set it to 1 (or 2 if he also care about replicas) during driver initialization.

[zhicwu]

My preference is to map clickhouse settings as is (not inverting them) and having the same defaults as clickhouse.

OK, the "ClickHouse way".

Update:
On a second thought, I think it's better to add a jdbcCompliance setting to let user to choose. By default everything stays in the clickhouse way(jdbcCompliance=false), but once it's enabled, JDBC driver will do the translation and execute mutation in synchronous way. Make sense?

[zhicwu]

Before light-weight mutation in place, since multi-statement SQL is now supported, we can run SQL like below in one run - session will be enabled automatically if the SQL contains multiple statements:

set mutations_sync = 1;

ALTER TABLE tbl UPDATE col1=1, col2=2 WHERE col3=3;