Hi all.
I'd like to get a function to run automatically on the server when a
postgresql client disconnects (either all disconnections, or just
unclean ones). This is to do some cleanup that can't be done with
transactions easily from what I can tell. Is it possible?
To give you an idea of why I need it, here's an outline of what I'm
doing:
1 server, several clients.
Each client retrieves an unprocessed record from the server, marks
that it's processing that record, does some processing (this will take
some time), and adds some extra data to the server based on the
processing.
Because I want several clients to be able to do this processing, I
need to mark which records are currently being processed - e.g. set a
status flag to 'processing'
For clients that complete successfully, as part of the transaction
they will change the status flag to 'processed' and move on.
However, if a client disconnects (which, because of the system I am
using, is relatively likely) then the record will still be marked as
processing even though nothing is processing it.
Do I have any alternatives? The best I've come up with is that each
client takes an advisory lock based on the row they're working on, and
every so often a program checks to see if there are any records
labelled processing that don't have an advisory lock (as these are
released on disconnection). But the polling seems more awkward than
just having the cleanup done whenever the server times out an unclean
disconnection.
Thanks all
Stuart Moore
