Re: how to ensure a client waits for a previous transaction to finish?

On Tue, Dec 8, 2009 at 10:13 PM, Dan Kortschak
<dan(dot)kortschak(at)adelaide(dot)edu(dot)au> wrote:
> I've spoken to people on the torque user mailing list and tried merlin's
> suggestion below (which looked like it should work - but unfortunately
> did not prevent the problem).
>
> >From working through things with the torque list, it seems to be the
> case that postgresql is behaving differently because it is not attached
> to a terminal (this has caused problems for others on that list with
> sqlite and mysql).
>
> Can anyone confirm/refute this? And if it is the case, is there
> something that I can do about it?

Advisory locks are basically only useful if the locker of the resource
maintains a database session (that is, stays connected and enjoys
private use of that connection) for the duration of the lock. Aside:
there is a way to hold locks from unconnected sessions...2PC, but the
feature is dangerous and probably not useful in your case.

Can you give a clearer explanation of the problem? You can monitor
the output from:
select * from pg_stat_activity;
in psql. Take special note of 'idle in transaction' backends and if
the connection is being regenerated behind your back by watching for
the pid changing.

merlin