Hi everyone...
I have a python-based application which provides a user interface
to a Postgresql database. The latest version of the application suddenly
lost the inability to insert new data in some of the tables. The SQL
statement(s) used still work if manually typed in psql, but it appears as
though there are some locks that are persisting and prevent inserts and
updates when the application is still running. Examining pg_locks shows a
number of locks (almost all of mode 'AccessShareLock'). Other inserts
and updates (to other tables) still work. The database logs (default
noise level setting) don't show any errors.
It would be really helpful to be able to be able to find out what
is causing the persistent locks. Is there any way to determine what table(s),
function(s), or other database items are involved in the lock? Ideally
what SQL statement(s) led to any given lock being set? (well, I can dream
can't I?) I've already done a "pg_dump -o"; the relation values show in
pg_locks don't appear in the dump. Is there some way to interpret the
relation values?
I tried searching the documentation, including the list archives,
but didn't see the answer (not even 'you cant get there from here'). I'd
happily accept that I'm wrong -- feel free to point out the documentation
that lays this all out.
Oh yes. Postgresql 8.0.3 on Linux (Debian/standard testing package).
TIA!
-frank
P.S. Yes, I'm going to be running various truncations of the user application
to investigate this as well, though it's pretty big by now and this won't
be pretty. It would be nice to have a more general approach to this kind
of problem.