An option to disable readonly on a `query` using POST

[slvrtrn]

We had a few PRs implementing this in one or another way, but we never decided which implementation we'd like to stabilize.

See:

• Don't specify 'readonly' twice if it appears in the client's option list #176

If a user specifies the "readonly" option at the client level and tries to execute a query too long to fit in the GET request's query parameters, "readonly" is set twice in the header - once as "1" and again as whatever the user specified. In particular, this means it's not possible to set another value for "readonly" on queries that use the POST method. Bypass setting "readonly" the first time if it's part of the Client's options already.

• Exposed method to control the readonly query parameter #181

This PR is meant to improve the control over the readonly option sent by the client to the CH database. Right now, it is defined by some methods like Query::do_execute, but most of the public methods like Query::fetch, Query::fetch_one, or Query::fetch_all don’t have control over this option. This makes it impossible to query the database with queries that contain a lot of columns and require temporary table creation on the database side, which results in the following error:

Code: 164. DB::Exception: db_user: Cannot execute query in readonly mode. (READONLY) (version 24.10.1.2812 (official build))

By adding the changes in this PR, we will gain control over this option, making it possible to request data that requires the creation of temporary tables by CH.

• Always use POST for queries; disable readonly setting by default #184
  (and see also: Always use POST for queries; disable readonly setting by default #184 (comment))

Perhaps the least invasive method to fixing it is to a) keep the GET or POST logic as-is, and b) allow overriding readonly explicitly.

Then we can re-open one of the linked PRs, or re-implement improved logic regarding readonly option.

CC @serprex @loyd

[serprex]

Reviewing past PRs, there's issue because of the DDL vs select defaults

Idea: read_only: Option<bool> where None has current behavior, Some(true) will error for DDL, Some(false) will always use POST

[slvrtrn]

Idea: read_only: Option where None has current behavior, Some(true) will error for DDL, Some(false) will always use POST

We also have a tangential issue:

• Query uses either GET by default, or POST if it's too large
• There are issues with large DDLs that go over POST due to readonly

Looks like ideally we could use even more knobs:

• Preferred HTTP method: always POST, or (current) mostly GET, and POST only when the request is too large. Could even add "always GET" for an odd use case, and throw an error if the request is too large.
• Readonly: - Enabled/Disabled - no effect on GET anyway

[serprex]

Is my understanding correct that those two knobs aren't orthogonal? ie GET-only is incompatible with readonly off, & really on compatible with readonly on

[slvrtrn]

At the second glance, we can have a few variations:

• Always POST - can use both readonly settings
• Preferred GET, POST if large - ignored for smaller queries, has effect on larger ones (kinda odd case, tbh)
• Always GET - readonly has no effect.

Having written all of this, looks like we need to just make it POST with readonly=true by default, with an option to override using .with_option so it does not introduce new API.

[slvrtrn]

Coming back to this ticket, and thinking about keeping the current design intact, as the use cases are fairly uncommon, but still exist, and are worth addressing.

• The HTTP method should always be POST;
  • NB: In general, there is very little reason to use GET except in very nice cases like external data, where the request is created in an entirely different way.
  • NB2: GET with large queries is susceptible to proxy max URL length issues and eats up space that query params (e.g., query settings, server-side parameters) could have used instead.
  • NB3: Surprisingly, it is still possible for a CH user with readonly=1 to set that flag without an exception from the server side, albeit without any real effect.
• Let's keep the current readonly logic as is (fetch* set it to 1, execute set it to 0).
• Let's allow overriding it using with_option and add a runnable example of such use cases.

Effectively, my understanding here is that we need to combine #176 and #184, which yields the cleanest solution to this.