If I open a connection to Postgres and issue a long-running query, and then break the connection (e.g., kill the client process that opened the connection), will the long-running query continue to run, or will it be automatically aborted? Is this configurable?

(I'm using Postgresql 9.2.9)

3 Answers 3


"It depends".

If the client vanishes due to network connection loss the query will generally run until it's retrieved enough rows to fill its network send buffer, then stop and get stuck until the TCP connection drops, at which point it'll abort. If it completes before it fills the TCP send buffer it'll complete successfully, so if it's autocommit the query will commit.

If the client is killed in a way that the client's operating system can report to the server via a TCP RST (like a client segfault/crash, SIGTERM, SIGKILL, etc), the PostgreSQL server will set the interrupt flag. Next time the query checks for interrupts as it executes it will see the flag and abort. Sometimes a query might be doing CPU-heavy work within code that doesn't check for interrupts - some extensions, and a few places within the PostgreSQL core - in which case it might not notice the interrupt for a long time and keep on running. It'll pretty much always see the interrupt and abort before completing and committing if it's autocommit, though.

If the client is killed by something like a sudden OS reboot, so that the client host suddenly knows nothing about the TCP connection but can still respond on the network, the query will probably get aborted the first time it triest to write a row, like Jeff said, because the client's host will send a TCP RST in response to the first packet sent by the server after the reboot. PostgreSQL checks for interrupts at each row it sends.

This behavour isn't configurable. As far as PostgreSQL is concerned if the client goes away its job is to terminate any queries the client was running. To change that you'd need some kind of query completion token that you could obtain at query start, then use to ask the server about the query via another connection later. Essentially you'd have to implement asynchronous/background queries. Possibly a nice feature, but not currently supported.

If the query is autocommit, or if your query was a COMMIT that was in-flight at the time you killed the client/lost the connection, it is possible for a transaction to be in an indeterminate state where the client doesn't know whether or not it committed. There's no real way to find out, other than looking for the effects of the transaction on the data.

Where that is unacceptable you can use two phase commit and a client-side transaction manager.

  • 1
    Wow, just what I was looking for, an excellent detailed answer! Thanks @Craig_Ringer! Commented Nov 5, 2014 at 15:35
  • 1
    @CraigRinger a related question was asked: Will pgAdmin III let me run an SQL command that works after detaching from my SSH tunnel? Commented Jun 13, 2015 at 19:07
  • I've had a problem with PostGIS running a computationally-intensive process (inner join on ST_Covers) for many minutes after killing a script that executed it using CTRL-C. The server kept running the query, working the CPU heavily, and, on Ubuntu, it hung up the shutdown process if I tried restarting the box while it was still running. I had to hard power-off the computer to get it to shut down. I would like to find a workaround or way to fix this, rather than just manually going in and killing the process. Is there a way to force it to check for interrupts more quickly?
    – cazort
    Commented May 31, 2021 at 18:18
  • @cazort Only with a code patch to add a CHECK_FOR_INTERUPTS() inside the computationally intensive loop in question. That isn't feasible if the process is running in library code, unfortunately. Commented Jun 1, 2021 at 1:38

It will continue to run until it tries to return rows to the connection and detects the breakage. So for a query that does all the work before returning any rows, it will essentially run to completion.

  • 2
    Thanks @jjanes. Can you point to any documentation or source code that indicates this? Commented Oct 29, 2014 at 23:07

Note that if you need a server-side process to continue after breaking the connection to the client, you can use nohup from the server-side command line, then safely close the connection to the server. This is especially useful in conjunction with writing the SQL script to a file and running the file from the command line, e.g.

$ nohup psql -f my_long_query.sql &

(The above expects Postgres trusts the OS authentication of the current user and that user has appropriate permissions. Writing the query output to an appropriate table for a later simple-SELECT * will store results that might otherwise be lost.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.