Re: Hung SQL Update Linux Redhat 4U5 Postgres 8.3.1

>>Craig Vosburgh <craig(dot)vosburgh(at)cassatt(dot)com> writes:
>> We've dumped the locks and it shows that all locks have been granted so
>> it appears that it is not a lock that is standing in our way. We've
>> also gone in via psql while the update is hung and were able to perform
>> an update on the offending table without issue. Finally, we have also
>> enabled the statement_timeout and set it to five minutes and it does
>> timeout the hung update and return to normal processing by rolling back
>> the offending transaction but that's not a viable solution for us.
>>
>> Anyone have any words of wisdom on how to track this down?
>
>For starters, attach to the hung backend with gdb and get a stack trace ...
>
> regards, tom lane

Tom,

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 ?? ()
#7 0x00000003 in ?? ()
#8 0x091c3770 in ?? ()
#9 0x091c3758 in ?? ()
#10 0xbfe63158 in ?? ()
#11 0xbfe634a0 in ?? ()
#12 0xbfe634b0 in ?? ()
#13 0x0832f984 in twophase_recover_callbacks ()
#14 0x08e8dc40 in ?? ()
#15 0x00000006 in ?? ()
#16 0xbff32c72 in ?? ()
#17 0x08e68758 in ?? ()
#18 0xb792a9f8 in ?? ()
#19 0xbfe631b8 in ?? ()
#20 0xbfe630d0 in ?? ()
#21 0x081e1d88 in GrantAwaitedLock ()
#22 0x081d6722 in MarkBufferDirty ()
(gdb)

Thanks for the help,
-Craig