Catching query cancelations in PLPython3u

Hi All,

We've been using plpython3u in our pgai project recently and recently got a
PR[1]https://github.com/timescale/pgai/pull/219
that uses some async code to communicate with openAI. That code uses async
to be able to respond in a timely manner to queries being cancelled.
Right now it uses a timed loop to test for query cancellations using:

def is_query_cancelled(plpy):
try:
plpy.execute("SELECT 1")
return False
except plpy.SPIError:
return True

The big problem with this I think is that it uses up a subtransaction,
which seems suboptimal. Is there a better way? Should Postgres add a
function to plpy for this?

I've thought about trying to catch the sigint signal from Python, but it
seems that the Python signal machinery would conflict badly with the
Postgres signal machinery.

[1]: https://github.com/timescale/pgai/pull/219

Thanks,
Mat

--
Mat Arye, Technical lead of all thing AI @ Timescale