Re: [HACKERS] Re: (fwd) Problems with Postgres - Mailing list pgsql-hackers

"Oliver Elphick" wrote: >Hackers: has anyone any insight on this one, please?
This is where things are going wrong:

regression=> vacuum verbose analyze;
NOTICE:  --Relation pg_type--
NOTICE:  Pages 4: Changed 0, Reapped 1, Empty 0, New 0; Tup 248: Vac 0, 
Keep/VTL0/0, Crash 0, UnUsed 31, MinLen 105, MaxLen 109; Re-using: Free/Avail. Space 
41
16/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
NOTICE:  Index pg_type_typname_index: Pages 4; Tuples 248: Deleted 0. Elapsed 
0/
0 sec.
NOTICE:  Index pg_type_oid_index: Pages 2; Tuples 248: Deleted 0. Elapsed 0/0 
se
c.
NOTICE:  --Relation pg_attribute--
NOTICE:  Pages 22: Changed 0, Reapped 1, Empty 0, New 0; Tup 1666: Vac 0, 
Keep/V
TL 0/0, Crash 0, UnUsed 50, MinLen 97, MaxLen 100; Re-using: Free/Avail. Space 
5
072/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
NOTICE:  Index pg_attribute_attrelid_index: Pages 10; Tuples 1666: Deleted 0. 
El
apsed 0/0 sec.
NOTICE:  Index pg_attribute_relid_attnum_index: Pages 12; Tuples 1666: Deleted 
0
. Elapsed 0/0 sec.
NOTICE:  Index pg_attribute_relid_attnam_index: Pages 28; Tuples 1666: Deleted 
0
. Elapsed 0/0 sec.
NOTICE:  --Relation pg_proc--
NOTICE:  Pages 24: Changed 0, Reapped 0, Empty 0, New 0; Tup 1070: Vac 0, 
Keep/V
TL 0/0, Crash 0, UnUsed 0, MinLen 145, MaxLen 2369; Re-using: Free/Avail. 
Space
0/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
NOTICE:  Index pg_proc_prosrc_index: Pages 10; Tuples 1070. Elapsed 0/0 sec.
NOTICE:  Index pg_proc_proname_narg_type_index: Pages 17; Tuples 1070. Elapsed 
0
/0 sec.
NOTICE:  Index pg_proc_oid_index: Pages 5; Tuples 1070. Elapsed 0/0 sec.
NOTICE:  --Relation pg_class--
NOTICE:  Pages 3: Changed 0, Reapped 2, Empty 0, New 0; Tup 210: Vac 0, 
Keep/VTL0/0, Crash 0, UnUsed 14, MinLen 102, MaxLen 132; Re-using: Free/Avail. Space 
14
32/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
NOTICE:  Index pg_class_relname_index: Pages 5; Tuples 210: Deleted 0. Elapsed 
0
/0 sec.
NOTICE:  Index pg_class_oid_index: Pages 2; Tuples 210: Deleted 0. Elapsed 0/0 
s
ec.

The relation now being vacuumed is bt_text_heap

the pg_vlock file gets deleted in vc_abort with this backtrace:
(gdb) bt
#0  vc_abort () at vacuum.c:252
#1  0x8078f28 in TransactionIdAbort (transactionId=156164) at transam.c:578
#2  0x80e0269 in XactLockTableWait (xid=156164) at lmgr.c:332
#3  0x806c9a9 in heap_delete (relation=0x8216ac8, tid=0x8225c48, ctid=0x0) at 
heapam.c:1149
#4  0x808ce04 in vc_delhilowstats (relid=2073242, attcnt=0, attnums=0x0) at 
vacuum.c:2484
#5  0x80897fc in vc_vacone (relid=2073242, analyze=1, va_cols=0x0) at 
vacuum.c:510
#6  0x80891fe in vc_vacuum (VacRelP=0x0, analyze=1 '\001', va_cols=0x0) at 
vacuum.c:279
#7  0x80890df in vacuum (vacrel=0x0, verbose=1, analyze=1 '\001', va_spec=0x0) 
at vacuum.c:164
#8  0x80e7ee0 in ProcessUtility (parsetree=0x8237e08, dest=Remote) at 
utility.c:638
#9  0x80e4e66 in pg_exec_query_dest (query_string=0xbfffa2b4 "vacuum verbose 
analyze;", dest=Remote, aclOverride=0) at postgres.c:727
#10 0x80e4d74 in pg_exec_query (query_string=0xbfffa2b4 "vacuum verbose 
analyze;") at postgres.c:656
#11 0x80e5edf in PostgresMain (argc=6, argv=0xbffff42c, real_argc=10, 
real_argv=0xbffff9b4) at postgres.c:1647
#12 0x80cec5f in DoBackend (port=0x81d5140) at postmaster.c:1628
#13 0x80ce73a in BackendStartup (port=0x81d5140) at postmaster.c:1373
#14 0x80cde59 in ServerLoop () at postmaster.c:823
#15 0x80cd989 in PostmasterMain (argc=10, argv=0xbffff9b4) at postmaster.c:616
#16 0x80a4245 in main (argc=10, argv=0xbffff9b4) at main.c:97
 >Craig Sanders wrote: >  >one of my clients is having problems with postgresql. i've upgraded to >  >the latest
version6.5.1-5 hoping that might fix the problem..no luck. >  > >  >any ideas? >  > >  > >  >BTW, i turned on debugging
inpostmaster.init and this is a sample of >  >what it shows - "ERROR: vacuum: can't destroy lock file!". there are 2 >
>or3 instances of this in the logs. >  > >... >  >ERROR:  vacuum: can't destroy lock file! >  >AbortCurrentTransaction
>...> >This does seem to be a problem with 6.5.1.  I have got a similar one coming      >up >in the regression test
database. Very interestingly, it has arisen since >my last clean vacuum and I have not touched the database since then.
I>wonder if it is possible that vacuum has itself corrupted the database. > >I have found no useful information in the
logs;the actual error seems to >indicate that the vc_shutdown() routine is being called a second time for >the same
database,but I cannot yet see why
 

In fact vc_abort unlinks pg_vlock, but then the vacuum keeps running and
vc_shutdown prints the error when it tries to unlink pg_vlock again.

--      Vote against SPAM: http://www.politik-digital.de/spam/                ========================================
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver              PGP key from public servers; key
ID32B8FAA1                ========================================    "If ye abide in me, and my words abide in you, ye
shall    ask what ye will, and it shall be done unto you."                                          John 15:7