3 ms·
Postgres does have "reset" support. There are several ways commands depending on what you want to reset. If you're only interested in mutable state changeable
by atombender 2y ago
Postgres does have "reset" support.
There are several ways commands depending on what you want to reset. If you're only interested in mutable state changeable with SET, you can use RESET ALL instead. This resets configuration settings such as the time zone, statement timeout, and so on.
Postgres has different fine-grained ways to reset other aspects of a session, such as closing cursors (CLOSE ALL), unlistening from notifications (UNLISTEN), deallocating prepared plans (DEALLOCATE), and so on. Finally, there's DISCARD ALL, which resets everything.
I agree about error handling. Most clients just disconnect on a connection error.
One gotcha about Postgres queries is that running queries do not get cancelled when the client disconnects. This has always been a weird one that's quite dangerous, as it can lead to dog-piling — if a client starts a slow query, then times out or cancels its connection, the query lives on; then if another identical request starts, same thing happens, and soon you have 100 queries running that are doing the same thing and getting slower and slower. You have to be very careful with retrying.
- klysm 2y agoI 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 agoMy 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 agoLink 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 agoIndeed, 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 agoThe 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 agoIf 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.
- anarazel 2y ago> One gotcha about Postgres queries is that running queries do not get cancelled when the client disconnects You can configure that these days, at least if the server is running on some common platforms: https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-CLIENT-CONNECTION-CHECK-INTERVAL https://www.postgresql.org/docs/current/runtime-config-conne...
- atombender 2y agoThank you, that's the one. I kept looking for the option and couldn't find it, and ChatGPT confidently hallucinated an option that doesn't exist.