Re: 7.4Beta1 hang? - Mailing list pgsql-hackers

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

> Robert Creager <Robert_Creager@LogicalChaos.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         | t34527020 |    17140 |             | 6426 | ShareLock                |
t34527020|    17140 |             | 6426 | AccessExclusiveLock      | t   18015 |    17140 |             | 6615 |
ShareUpdateExclusiveLock| t   17971 |    17140 |             | 6426 | AccessShareLock          | t   17971 |    17140 |
           | 6426 | RowExclusiveLock         | t   16759 |    17140 |             | 8069 | AccessShareLock          |
t34526324|    17140 |             | 6426 | AccessShareLock          | t34526324 |    17140 |             | 6426 |
ShareLock               | t34526324 |    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          | t34527019 |    17140 |             | 6426 | ShareLock                |
t34527019|    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