GIL Release in copy_from?

[psycoteam]

Originally submitted by: Psycopg website

Submitted by: Walt Woods

Hey, we have an issue with our software where an entire Python interpreter locks up. Every thread is locked on PyEval_RestoreThread except one, which is in psycopg2:

Thread 24 (Thread 0x7fce8aeef700 (LWP 8655)):
#230  0x00007fce9a49eb23 in poll () from /lib/x86_64-linux-gnu/libc.so.6
#231  0x00007fce95fcb5ed in ?? () from /usr/lib/libpq.so.5
#232  0x00007fce95fcb670 in ?? () from /usr/lib/libpq.so.5
#233  0x00007fce95fc9841 in PQgetResult () from /usr/lib/libpq.so.5
#234  0x00007fce961f0f20 in _pq_copy_in_v3 (curs=0x7fcddc58ced8)
    at psycopg/pqpath.c:1322
#235  pq_fetch (curs=0x7fcddc58ced8) at psycopg/pqpath.c:1462
#236  0x00007fce961f12f1 in pq_execute (curs=0x7fcddc58ced8, query=
    0x7fcddc74c880 "COPY myQuery"..., async=0)
    at psycopg/pqpath.c:945
#237  0x00007fce961f5dbc in psyco_curs_copy_from (self=0x7fcddc58ced8, 
    args=<optimized out>, kwargs=<optimized out>) at psycopg/cursor_type.c:1336
#238  0x000000000049d585 in PyEval_EvalFrameEx ()

So... While the query stalling is happening on our side, it's still bothersome that the GIL isn't released during a COPY_FROM, especially since my understanding is that COPY_FROM is an IO intensive operation (waiting on network) that doesn't need to hold the GIL.

It'd be nice if the GIL wasn't locked for cursor copies.

[dvarrazzo]

Hello Walt,

it is surprising PQgetResult blocks during copy: the IO intensive part of the copy should be performed by PQputCopyData, which releases the gil. However the libpq doc say PQgetResult is blocking so generally it should be a good idea to release the gil while calling it.

Can you test with the attached patch if your application starts working correctly?

Thank you very much.

[dvarrazzo]

Looking at the traceback again, the library is probably not blocked in IO but just locked in the poll waiting for the result to arrive. I think we can just wrap each PGgetResult call into a GIL release (provided we have the connection lock, which should be true but I'll double check) as a tentative way to fix this issue. Walt should let us know in the next days if the patch is good for them anyway.

[dvarrazzo]

I've committed a patch for PQgetResult after copy. The other uses of the function shouldn't need it (the most important one is in async code, and by that we should have received the message that it won't block anyway)

I'll mark the issue closed but let me know if it fails to fix the problem.