Psycopg2 connection unusable after SELECTs interrupted by OS signal
<h1>Problem</h1>

<p>I am working on a long-running python process that performs a lot of database access (mostly reads, occasional writes). Sometimes it may be necessary to terminate the process before it finishes (e.g. by using the <code>kill</code> command) and when this happens I would like to log a value to the database indicating that the particular run was canceled. (I am also logging the occurrence to a log file; I would like to have the information in both places.)</p>

<p>I have found that if I interrupt the process while the database connection is active, the connection becomes unusable; specifically, it hangs the process if I try to use it in any way.</p>

<h1>Minimum working example</h1>

<p>The actual application is rather large and complex, but this snippet reproduces the problem reliably.</p>

<p>The table <code>test</code> in the database has two columns, <code>id</code> (serial) and <code>message</code> (text). I prepopulated it with one row so the <code>UPDATE</code> statement below would have something to change.</p>

<pre><code>import psycopg2
import sys
import signal


pg_host = 'localhost'
pg_user = 'redacted'
pg_password = 'redacted'
pg_database = 'test_db'


def write_message(msg):
    print "Writing: " + msg
    cur.execute("UPDATE test SET message = %s WHERE id = 1", (msg,))
    conn.commit()


def signal_handler(signal, frame):
    write_message('Interrupt!')
    sys.exit(0)
signal.signal(signal.SIGINT, signal_handler)
signal.signal(signal.SIGTERM, signal_handler)


if __name__ == '__main__':
    conn = psycopg2.connect(host=pg_host, user=pg_user, password=pg_password, database=pg_database)
    cur = conn.cursor()

    write_message("Starting")
    for i in xrange(10000):
        # I press ^C somewhere in here
        cur.execute("SELECT * FROM test")
        cur.fetchall()
    write_message("Finishing")
</code></pre>

<p>When I run this script without interruption, it completes as expected. That is, the row in the database is updated to say "Starting" then "Finishing".</p>

<p>If I press <code>ctrl-C</code> during the loop indicated by the comment, python hangs indefinitely. It no longer responds to keyboard input, and the process has to be killed from elsewhere. Looking in my postgresql log, the <code>UPDATE</code> statement with "Interrupted!" is never received by the database server.</p>

<p>If I add a debugging breakpoint at the beginning of signal_handler() I can see that doing almost anything with the database connection at that point causes the same hang. Trying to <code>execute</code> a <code>SELECT</code>, issuing a <code>conn.rollback()</code>, <code>conn.commit()</code>, <code>conn.close()</code> or <code>conn.reset()</code> all cause the hang. Executing <code>conn.cancel()</code> does not cause a hang, but it doesn't improve the situation; subsequent use of the connection still causes a hang. If I remove the database access from <code>write_message()</code> then the script is able to exit gracefully when interrupted, so the hang is definitely database connection related.</p>

<p>Also worth noting: if I change the script so that I am interrupting something other than database activity, it works as desired, logging "Interrupted!" to the database. E.g., if I replace the <code>for i in xrange(10000)</code> loop with a simple <code>sleep(10)</code> and interrupt that, it works fine. So the problem seems to be specifically related to interrupting psycopg2 with a signal while it is performing database access, <em>then</em> trying to use the connection.</p>

<h1>Questions</h1>

<p>Is there any way to salvage the existing psycopg2 connection and use it to update the database after this kind of interruption?</p>

<p>If not, is there at least a way to terminate it cleanly so if some subsequent code tried to use it, it wouldn't cause a hang?</p>

<p>Finally, is this somehow expected behavior, or is it a bug that should be reported? It makes sense to me that the connection could be in a bad state after this kind of interruption, but ideally it would throw an exception indicating the problem rather than hanging.</p>

<h1>Workaround</h1>

<p>In the meantime, I have discovered that if I create an entirely new connection with <code>psycopg2.connect()</code> after the interrupt and am careful not to access the old one, I can still update the database from the interrupted process. This is probably what I'll do for now, but it feels untidy. </p>

<h1>Environment</h1>

<ul>
<li>OS X 10.11.6</li>
<li>python 2.7.11</li>
<li>psycopg2 2.6.1</li>
<li>postgresql 9.5.1.0</li>
</ul>

<p>I filed an <a href="https://github.com/psycopg/psycopg2/issues/471" rel="nofollow">issue</a> for this on the psycopg2 github and received a helpful response from the developer. In summary:</p>

<ul>
<li>The behavior of an existing connection within a signal handler is OS dependent and there's probably no way to use the old connection reliably; creating a new one is the recommended solution.</li>
<li>Using <code>psycopg2.extensions.set_wait_callback(psycopg2.extras.wait_select)</code> improves the situation a bit (at least in my environment) by causing <code>execute()</code> statements called from within the signal handler to throw an exception rather than hang. However, doing other things with the conneciton (e.g. <code>reset()</code>) still caused a hang for me, so ultimately it's still best to just create a new connection within the signal handler rather than trying to salvage the existing one.</li>
</ul>

python postgresql python-2.7 signals psycopg2