4 ms·

I can see the argument for letting a query finish if it is doing any kind of mutation. For a read-only query though, surely it would be a good addition in postg

by klysm 2y ago

I can see the argument for letting a query finish if it is doing any kind of mutation. For a read-only query though, surely it would be a good addition in postgres to cancel the query? Cancelling things can be quite difficult though in practice.

atombender 2y ago
My understanding is that the difficulty is knowing the client disconnected. Postgres doesn't know until it tries to write the result to the stream. That's the core team's explanation in this [1] ancient thread, at least. I don't know why Postgres can't write keepalive messages while executing the query; speculating here, but it's possible that the architecture is synchronous and doesn't support doing anything with the connection while the query is executing. It's an old threading model where one process is started per connection. I could have sworn I had read about a new configuration option introduced in Postgres 14, but I can't find anything about it. [1] https://www.postgresql.org/message-id/e09785e00907280622v9b1c2cem2bf8ecc02e21a25a@mail.gmail.com https://www.postgresql.org/message-id/e09785e00907280622v9b1...
- anarazel 2y ago
  Link to the option in https://news.ycombinator.com/item?id=43598217 https://news.ycombinator.com/item?id=43598217
- nextaccountic 2y ago
  > I don't know why Postgres can't write keepalive messages while executing the query Someone linked this https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-CLIENT-CONNECTION-CHECK-INTERVAL https://www.postgresql.org/docs/current/runtime-config-conne... > client_connection_check_interval (integer) > Sets the time interval between optional checks that the client is still connected, while running queries. The check is performed by polling the socket, and allows long running queries to be aborted sooner if the kernel reports that the connection is closed. > This option relies on kernel events exposed by Linux, macOS, illumos and the BSD family of operating systems, and is not currently available on other systems. > If the value is specified without units, it is taken as milliseconds. The default value is 0, which disables connection checks. Without connection checks, the server will detect the loss of the connection only at the next interaction with the socket, when it waits for, receives or sends data. > For the kernel itself to detect lost TCP connections reliably and within a known timeframe in all scenarios including network failure, it may also be necessary to adjust the TCP keepalive settings of the operating system, or the tcp_keepalives_idle, tcp_keepalives_interval and tcp_keepalives_count settings of PostgreSQL. So it appears that they can now indeed use tcp keepalive but only if you configure it
  - atombender 2y ago
    Indeed, that was added in Postgres 14! (I was looking for that option earlier and couldn't find it, and ChatGPT confidently hallucinated an option that doesn't exist, so I gave up.)
marcosdumay 2y ago
The database must stay correct if you cut power to the machine instead of disconnecting the client, so I don't see how mutation would make it impossible to drop partial work.
rini17 2y ago
If you're doing mutations, you're supposed to explicitly use transactions anyway, with well defined logical units of work. "DELETE FROM table LIMIT 10" does not look like anything well defined. In case of connection issues you can check whether it was all actually committed or rolled back. Often it was propagated to the user, they got error message, refreshed and checked and retried and all was okay in the end. That's probably why the DB engines did not bother micromanaging individual SQL statemens.

by klysm 2y ago

atombender 2y ago

My understanding is that the difficulty is knowing the client disconnected. Postgres doesn't know until it tries to write the result to the stream. That's the core team's explanation in this [1] ancient thread, at least. I don't know why Postgres can't write keepalive messages while executing the query; speculating here, but it's possible that the architecture is synchronous and doesn't support doing anything with the connection while the query is executing. It's an old threading model where one process is started per connection. I could have sworn I had read about a new configuration option introduced in Postgres 14, but I can't find anything about it. [1] https://www.postgresql.org/message-id/e09785e00907280622v9b1c2cem2bf8ecc02e21a25a@mail.gmail.com https://www.postgresql.org/message-id/e09785e00907280622v9b1...

anarazel 2y ago
Link to the option in https://news.ycombinator.com/item?id=43598217 https://news.ycombinator.com/item?id=43598217
nextaccountic 2y ago
> I don't know why Postgres can't write keepalive messages while executing the query Someone linked this https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-CLIENT-CONNECTION-CHECK-INTERVAL https://www.postgresql.org/docs/current/runtime-config-conne... > client_connection_check_interval (integer) > Sets the time interval between optional checks that the client is still connected, while running queries. The check is performed by polling the socket, and allows long running queries to be aborted sooner if the kernel reports that the connection is closed. > This option relies on kernel events exposed by Linux, macOS, illumos and the BSD family of operating systems, and is not currently available on other systems. > If the value is specified without units, it is taken as milliseconds. The default value is 0, which disables connection checks. Without connection checks, the server will detect the loss of the connection only at the next interaction with the socket, when it waits for, receives or sends data. > For the kernel itself to detect lost TCP connections reliably and within a known timeframe in all scenarios including network failure, it may also be necessary to adjust the TCP keepalive settings of the operating system, or the tcp_keepalives_idle, tcp_keepalives_interval and tcp_keepalives_count settings of PostgreSQL. So it appears that they can now indeed use tcp keepalive but only if you configure it
- atombender 2y ago
  Indeed, that was added in Postgres 14! (I was looking for that option earlier and couldn't find it, and ChatGPT confidently hallucinated an option that doesn't exist, so I gave up.)

marcosdumay 2y ago

The database must stay correct if you cut power to the machine instead of disconnecting the client, so I don't see how mutation would make it impossible to drop partial work.

rini17 2y ago

If you're doing mutations, you're supposed to explicitly use transactions anyway, with well defined logical units of work. "DELETE FROM table LIMIT 10" does not look like anything well defined. In case of connection issues you can check whether it was all actually committed or rolled back. Often it was propagated to the user, they got error message, refreshed and checked and retried and all was okay in the end. That's probably why the DB engines did not bother micromanaging individual SQL statemens.