Postgres: processes terminated after connetion break / invalidation

Question

I don't understand some of Postgres mechanism and it makes me quite upset. I usually use DBeaver as SQL client to query external pg base. If run create.. or insert.. queries and then connection for some reason is broken or invalidated, the pid is still running and finishes transaction.

But for some more complicated PL/pgSQL functions (with temp tables, loops, inserts, etc.) we wrote, breaking connection always causes process termination (it disappears from session list just before making next sql operation, eg. inserting a row in logtable). No matter if it's DBeaver editor or psql command.

I know that maybe disconnecting is critical problem, which should be eliminated and maybe I shouldn't expect process to successfully continue, but I do:) Or just to know why it happened and is it possible to prevent it?

Answer 1

If the network connection fails, the database server can detect that in two ways:

• if it tries to send data to the client, it will figure out pretty quickly that the connection is down

• if it tries to receive data from the client, it will only notice when the kernel's TCP keepalive mechanism has determined that the connection is down

When you say that sometimes execution of a function is terminated right away, I would say that is because the function returned data to the client.

In the case where a query keeps running, it is not attempting to return any data yet.

There is no cure for the former, but in PostgreSQL v14 you can prevent the latter by setting client_connection_check_interval. In addition, you have to set the PostgreSQL keepalive parameters so that the dead connection becomes known quickly.

See my article for more.