Re: 7.4Beta1 hang?

On Sat, 09 Aug 2003 20:12:36 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> said something like:

> Robert Creager <Robert_Creager(at)LogicalChaos(dot)org> writes:
> > Anything to look at before I kick it?
>
> pg_locks and pg_stat_activity, if you can select from them in a
> non-stuck backend.

tassiv=# select * from pg_locks;
relation | database | transaction | pid | mode | granted
----------+----------+-------------+------+--------------------------+---------
17846 | 17140 | | 6426 | AccessShareLock | t
17846 | 17140 | | 6426 | RowShareLock | t
17846 | 17140 | | 6426 | RowExclusiveLock | t
17835 | 17140 | | 6426 | AccessShareLock | t
17835 | 17140 | | 6426 | RowShareLock | t
18096 | 17140 | | 6426 | AccessExclusiveLock | t
18045 | 17140 | | 6426 | AccessShareLock | t
18045 | 17140 | | 6426 | RowExclusiveLock | t
34527020 | 17140 | | 6426 | ShareLock | t
34527020 | 17140 | | 6426 | AccessExclusiveLock | t
18015 | 17140 | | 6615 | ShareUpdateExclusiveLock | t
17971 | 17140 | | 6426 | AccessShareLock | t
17971 | 17140 | | 6426 | RowExclusiveLock | t
16759 | 17140 | | 8069 | AccessShareLock | t
34526324 | 17140 | | 6426 | AccessShareLock | t
34526324 | 17140 | | 6426 | ShareLock | t
34526324 | 17140 | | 6426 | AccessExclusiveLock | t
18101 | 17140 | | 6426 | ShareLock | t
18101 | 17140 | | 6426 | AccessExclusiveLock | t
18096 | 17140 | | 6615 | AccessExclusiveLock | f
18028 | 17140 | | 6426 | AccessShareLock | t
18028 | 17140 | | 6426 | RowExclusiveLock | t
18028 | 17140 | | 6426 | ShareLock | t
18028 | 17140 | | 6426 | AccessExclusiveLock | t
17879 | 17140 | | 6426 | AccessShareLock | t
17879 | 17140 | | 6426 | RowExclusiveLock | t
| | 79764 | 8069 | ExclusiveLock | t
18015 | 17140 | | 6426 | AccessShareLock | t
18015 | 17140 | | 6426 | RowExclusiveLock | t
| | 79690 | 6615 | ExclusiveLock | t
17855 | 17140 | | 6426 | AccessShareLock | t
17857 | 17140 | | 6426 | AccessShareLock | t
17857 | 17140 | | 6426 | RowShareLock | t
17857 | 17140 | | 6426 | RowExclusiveLock | t
18099 | 17140 | | 6426 | ShareLock | t
18099 | 17140 | | 6426 | AccessExclusiveLock | t
17912 | 17140 | | 6426 | AccessShareLock | t
| | 79712 | 6426 | ExclusiveLock | t
17984 | 17140 | | 6426 | AccessShareLock | t
17984 | 17140 | | 6426 | RowExclusiveLock | t
17984 | 17140 | | 6426 | ShareLock | t
17984 | 17140 | | 6426 | AccessExclusiveLock | t
17844 | 17140 | | 6426 | AccessShareLock | t
34527019 | 17140 | | 6426 | ShareLock | t
34527019 | 17140 | | 6426 | AccessExclusiveLock | t
18015 | 17140 | | 6615 | ShareUpdateExclusiveLock | t

tassiv=# select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename | current_query | query_start
-------+---------+---------+----------+---------+---------------+-------------
17140 | tassiv | 8069 | 100 | robert | |
17140 | tassiv | 6426 | 100 | robert | |
17140 | tassiv | 6427 | 100 | robert | |
17140 | tassiv | 6615 | 100 | robert | |

>
> > It's not built with debug, but I
> > can still get a backtrace.
>
> Might be useful.
>
> regards, tom lane
>
>

00:00:01 /usr/local/pgsql/bin/postmaster -D /var/lib/pgsql/data
#0 0x4031fec2 in select () from /lib/i686/libc.so.6
#1 0xbffff368 in ?? ()
#2 0x081340bd in PostmasterMain ()
#3 0x08109edc in main ()
#4 0x4025e7f7 in __libc_start_main () from /lib/i686/libc.so.6

postgres: robert tassiv 192.168.0.250 INSERT
(gdb) backtrace
#0 0x403279d7 in semop () from /lib/i686/libc.so.6
#1 0x08133151 in PGSemaphoreLock ()
#2 0x081517cc in LWLockAcquire ()
#3 0x081482fd in LockBuffer ()
#4 0x0808439f in _bt_getbuf ()
#5 0x080827e4 in _bt_split ()
#6 0x08082202 in _bt_insertonpg ()
#7 0x08081ce8 in _bt_doinsert ()
#8 0x080857ea in btinsert ()
#9 0x081b77df in OidFunctionCall6 ()
#10 0x08081190 in index_insert ()
#11 0x080f75f4 in ExecInsertIndexTuples ()
#12 0x080f2034 in ExecInsert ()
#13 0x080f1d08 in ExecutePlan ()
#14 0x080f1059 in ExecutorRun ()
#15 0x08157eec in ProcessQuery ()
#16 0x08158859 in PortalRunMulti ()
#17 0x081582de in PortalRun ()
#18 0x081555ec in exec_simple_query ()
#19 0x08157602 in PostgresMain ()
#20 0x081362e8 in BackendFork ()
#21 0x08135da3 in BackendStartup ()
#22 0x081346cd in ServerLoop ()
#23 0x081340bd in PostmasterMain ()
#24 0x08109edc in main ()
#25 0x4025e7f7 in __libc_start_main () from /lib/i686/libc.so.6

00:02:38 postgres: stats buffer process
#0 0x4031fec2 in select () from /lib/i686/libc.so.6
#1 0x00000005 in ?? ()
#2 0x08138376 in pgstat_main ()
#3 0x0813711e in pgstat_start ()
#4 0x08134093 in PostmasterMain ()
#5 0x08109edc in main ()
#6 0x4025e7f7 in __libc_start_main () from /lib/i686/libc.so.6

00:02:30 postgres: stats collector process
#0 0x4031fec2 in select () from /lib/i686/libc.so.6
#1 0xbfffefb0 in ?? ()
#2 0x0813711e in pgstat_start ()
#3 0x08134093 in PostmasterMain ()
#4 0x08109edc in main ()
#5 0x4025e7f7 in __libc_start_main () from /lib/i686/libc.so.6

00:00:00 postgres: checkpoint subprocess
#0 0x403279d7 in semop () from /lib/i686/libc.so.6
#1 0x08133151 in PGSemaphoreLock ()
#2 0x081517cc in LWLockAcquire ()
#3 0x08147454 in BufferSync ()
#4 0x0814785a in FlushBufferPool ()
#5 0x08095007 in CreateCheckPoint ()
#6 0x0809900a in BootstrapMain ()
#7 0x08136a69 in SSDataBase ()
#8 0x08134797 in ServerLoop ()
#9 0x081340bd in PostmasterMain ()
#10 0x08109edc in main ()
#11 0x4025e7f7 in __libc_start_main () from /lib/i686/libc.so.6

00:00:34 postgres: robert tassiv localhost VACUUM waiting
#0 0x403279d7 in semop () from /lib/i686/libc.so.6
#1 0x08133151 in PGSemaphoreLock ()
#2 0x0815034c in ProcSleep ()
#3 0x0814f2a4 in WaitOnLock ()
#4 0x0814eeb9 in LockAcquire ()
#5 0x0814e4a9 in LockRelation ()
#6 0x080edf72 in lazy_vacuum_index ()
#7 0x080ed6e3 in lazy_scan_heap ()
#8 0x080ed347 in lazy_vacuum_rel ()
#9 0x080e99c4 in vacuum_rel ()
#10 0x080e91e1 in vacuum ()
#11 0x08158752 in PortalRunUtility ()
#12 0x081589a8 in PortalRunMulti ()
#13 0x081582de in PortalRun ()
#14 0x081555ec in exec_simple_query ()
#15 0x08157602 in PostgresMain ()
#16 0x081362e8 in BackendFork ()
#17 0x08135da3 in BackendStartup ()
#18 0x081346cd in ServerLoop ()
#19 0x081340bd in PostmasterMain ()
#20 0x08109edc in main ()
#21 0x4025e7f7 in __libc_start_main () from /lib/i686/libc.so.6

--
18:23:04 up 8 days, 11:08, 4 users, load average: 2.04, 2.02, 1.55