Re: [HACKERS] Re: (fwd) Problems with Postgres

"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/VTL
0/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/VTL
0/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 version 6.5.1-5 hoping that might fix the problem..no luck.
> >
> >any ideas?
> >
> >
> >BTW, i turned on debugging in postmaster.init and this is a sample of
> >what it shows - "ERROR: vacuum: can't destroy lock file!". there are 2
> >or 3 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(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"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