Обсуждение: Catching query cancelations in PLPython3u
Hi All,
We've been using plpython3u in our pgai project recently and recently got a PR[1]
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
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.
Thanks,
Mat
Mat Arye, Technical lead of all thing AI @ Timescale
Bumping this, because I am genuinely interested if there is a better way to do this.
I'd really like to know if there is a better way than executing dummy queries...it feels dirty.
I've seen plenty of extensions not handle query cancellation / exceptions gracefully. Also seems like something to talk about in the docs a little bit, because most extensions/functions that don't handle this well aren't even aware that those issues are there from what i've seen (searching github issues, etc).