Re: Hung SQL Update Linux Redhat 4U5 Postgres 8.3.1

Tom,

Yea, about that lock that I *said* I didn't have... We had been joining
through the pg_class and pg_tables tables to get some additional data and it
turns out the row in the pg_locks that shows as locked doesn't have a
relation so it was filtered out due to the join.

So, now that I have accurate data (just doing a select * from pg_locks where
granted = 'f') I indeed have an outstanding lock that isn't granted that is
causing the holdup. However, now I have a couple of other questions.

First, I thought I've seen in the past where Postgres will recognize a
deadlock and will log an error and then move on. Do I need to do something
with the more recent code base to get that behavior? I think the last time
I saw that was back on an 8.1.X code base.

Second, any words of wisdom to help run to ground who's keeping me from
getting the lock on the offending row? The row in the lock table that shows
granted false does not show as belonging to a database or relation (both
null) so I can't join through to get the table info from pg_table.

Thanks for all the help,
-Craig

On 5/12/08 12:16 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Craig Vosburgh <craig(dot)vosburgh(at)cassatt(dot)com> writes:
>> Got GDB installed on the errant node and did a back trace call (I'm guessing
>> that is what you were looking for when you said stack trace) on the process
>> that shows in the process table as executing the hung SQL command.
>
>> The backtrace is:
>> (gdb) bt
>> #0 0x0088b7a2 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2
>> #1 0x0096f3ab in semop () from /lib/tls/libc.so.6
>> #2 0x081ba8f8 in PGSemaphoreLock ()
>> #3 0x081e4d9e in ProcSleep ()
>> #4 0x081e1db3 in GrantAwaitedLock ()
>> #5 0x0832f984 in twophase_recover_callbacks ()
>> #6 0x00000006 in ?? ()
>
> Hmm. gdb is lying to you, because GrantAwaitedLock doesn't call
> ProcSleep --- probably line 4 should refer to WaitOnLock instead.
> You could try installing a non-symbol-stripped postgres executable
> (or in RPM environments, install the matching debuginfo RPM) to
> get a more trustworthy backtrace.
>
> However, what I suspect you are looking at is just a run-of-the-mill
> lock wait. You *sure* there's no ungranted locks showing in pg_locks?
>
> regards, tom lane