Thanks for that clarification Merlin,
The server/client is on a workstation that is essentially private (I
share some time with students, but they don't have pg access). The locks
are across sessions. There are three perl scripts that connect to a pg
db, one loads the database and creates some computed tables (a legacy of
what the database was designed for that makes sense to retain) that are
used for the subsequent analysis. The following two script query the db
with a set of defined queries and provide the glue to R to generate a
statistical analysis and produce graphs etc. The two analysis scripts
seem to start/finish before the population and indexing has been
completed.
As I've mentioned in the previous post to Tom, I won't have the machine
time to look at the suggestion you have provided for the next week of
so, but I will follow it up one the analyses that are pending have been
completed. The complexity of the system suggests to me that regeneration
of connections I unlikely to be a problem (there are only three sessions
in the whole system).
I was initially considering making a process state table for
communication between the three scripts. (This is what I initially
thought you were refering to in your first post.) This would essentially
be a soft lock.
thanks again
Dan
On Wed, 2009-12-09 at 00:28 -0500, Merlin Moncure wrote:
> 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.
