Hanging VACUUM

Started by Bruce Momjianalmost 25 years ago2 messageshackers
Jump to latest
#1Bruce Momjian
bruce@momjian.us

I just noticed that if I do BEGIN;CREATE TABLE..., and then start VACUUM
of the database in another psql session, the VACUUM hangs until the
transaction completes. Is this expected?

---------------------------------------------------------------------------

#0 0x28256145 in semop () from /shlib/libc.so.2
#1 0x8118755 in IpcSemaphoreLock (semId=1441793, sem=0, interruptOK=1)
at ipc.c:426
#2 0x811dda3 in ProcSleep (lockMethodTable=0x81ff1c0, lockmode=7,
lock=0x283ace9c, holder=0x283addbc) at proc.c:667
#3 0x811c898 in WaitOnLock (lockmethod=1, lockmode=7, lock=0x283ace9c,
holder=0x283addbc) at lock.c:955
#4 0x811c553 in LockAcquire (lockmethod=1, locktag=0x8046bcc, xid=9198,
lockmode=7) at lock.c:739
#5 0x811b67a in LockRelation (relation=0x826b01c, lockmode=7) at lmgr.c:141
#6 0x80735c5 in heap_open (relationId=1259, lockmode=7) at heapam.c:596
#7 0x80bf3cd in vacuum_rel (relid=1259) at vacuum.c:455
#8 0x80befa1 in vacuum (vacstmt=0x82ef1d0) at vacuum.c:239
#9 0x8124790 in ProcessUtility (parsetree=0x82ef1d0, dest=Remote)
at utility.c:718
#10 0x81222cb in pg_exec_query_string (query_string=0x82ef038 "vacuum;",
dest=Remote, parse_context=0x82666f4) at postgres.c:777
#11 0x81234d8 in PostgresMain (argc=7, argv=0x8046f4c, real_argc=4,
real_argv=0x804789c, username=0x8258661 "postgres") at postgres.c:1908
#12 0x81094c0 in DoBackend (port=0x8258400) at postmaster.c:2119
#13 0x8109006 in BackendStartup (port=0x8258400) at postmaster.c:1902
#14 0x8108189 in ServerLoop () at postmaster.c:1000
#15 0x8107b6e in PostmasterMain (argc=4, argv=0x804789c) at postmaster.c:690

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Bruce Momjian (#1)
Re: Hanging VACUUM

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I just noticed that if I do BEGIN;CREATE TABLE..., and then start VACUUM
of the database in another psql session, the VACUUM hangs until the
transaction completes. Is this expected?

Sure. You have a write lock on pg_class ... not to mention a few other
system tables, but that's the one this trace shows VACUUM is waiting for
exclusive lock on:

#5 0x811b67a in LockRelation (relation=0x826b01c, lockmode=7) at lmgr.c:141
#6 0x80735c5 in heap_open (relationId=1259, lockmode=7) at heapam.c:596

regards, tom lane