RE: Beta2 Vacuum and pg_dump failures and mangled datab ases

You should probably write off your databases as toast ... update to
beta3 and do an initdb. Sorry about that ...

And try to reproduce bug.
Sorry.

Vadim

"Mikheev, Vadim" wrote:

[ . . . ]

Restarting the server didn't make a difference.

I upgraded to beta3 just now and the problem persists. I
didn't do an initdb obviously cuz
I cannot save the data via pg_dump. Beta3 will read beta2
data OK (I guess this means that
an initdb is not required when going from beta2 to beta3?!)
but I can't vacuum or dump on
any database.

So, server doesn't restart?

Yes, it does restart, that is not the problem (did I explain that properly?).

Could add

wal_debug = 1

to postgresql.conf, start postmaster and send me stderr output?

I did add wal_debug = 1 to postgresql.conf. Starting up is OK, when I then try a vacuum
verbose on a database, it goes:

-------------------------------------- start log --------------------------------------
DEBUG: database system is shut down
DEBUG: starting up
DEBUG: database system was shut down at 2001-01-12 20:11:37
DEBUG: CheckPoint record at (0, 11629776)
DEBUG: Redo record at (0, 11629776); Undo record at (0, 0); Shutdown TRUE
DEBUG: NextTransactionId: 8284; NextOid: 98635
DEBUG: database system is in production state
NOTICE: --Relation pg_type--
NOTICE: Pages 2: Changed 0, reaped 1, Empty 0, New 0; Tup 131: Vac 0, Keep/VTL 0/0, Crash
0, UnUsed 2, MinLen 106, MaxLen 109; Re-using: Free/Avail. Space 1428/0; EndEmpty/Avail.
Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE: Index pg_type_oid_index: Pages 2; Tuples 131: Deleted 0. CPU 0.00s/0.00u sec.
NOTICE: Index pg_type_typname_index: Pages 2; Tuples 131: Deleted 0. CPU 0.00s/0.00u sec.
INSERT @ 0/11629840: prev 0/11629776; xprev 0/0; xid 8291; bkpb 1: Heap - clean: node
95464/1247; blk 1
XLogFlush: rqst 0/11638108; wrt 0/0; flsh 0/0
INSERT @ 0/11638108: prev 0/11629840; xprev 0/11629840; xid 8291: Transaction - commit:
2001-01-12 20:12:51
XLogFlush: rqst 0/11638144; wrt 0/11638108; flsh 0/11638108
NOTICE: --Relation pg_attribute--
NOTICE: Pages 9: Changed 0, reaped 1, Empty 0, New 0; Tup 649: Vac 0, Keep/VTL 0/0, Crash
0, UnUsed 18, MinLen 98, MaxLen 98; Re-using: Free/Avail. Space 5500/0; EndEmpty/Avail.
Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE: Index pg_attribute_relid_attnam_index: Pages 10; Tuples 649: Deleted 0. CPU
0.01s/0.00u sec.
NOTICE: Index pg_attribute_relid_attnum_index: Pages 5; Tuples 649: Deleted 0. CPU
0.00s/0.00u sec.
INSERT @ 0/11638144: prev 0/11638108; xprev 0/0; xid 8292; bkpb 1: Heap - clean: node
95464/1249; blk 8
XLogFlush: rqst 0/11646412; wrt 0/11638144; flsh 0/11638144
INSERT @ 0/11646412: prev 0/11638144; xprev 0/11638144; xid 8292: Transaction - commit:
2001-01-12 20:12:52
XLogFlush: rqst 0/11646448; wrt 0/11646412; flsh 0/11646412
NOTICE: --Relation pg_class--
NOTICE: Pages 7: Changed 0, reaped 6, Empty 2, New 0; Tup 155: Vac 29, Keep/VTL 0/0,
Crash 0, UnUsed 81, MinLen 115, MaxLen 160; Re-using: Free/Avail. Space 37060/30444;
EndEmpty/Avail. Pages 0/5. CPU 0.00s/0.00u sec.
INSERT @ 0/11646448: prev 0/11646412; xprev 0/0; xid 8293; bkpb 1: Btree - delete: node
95464/17118; tid 1/81
INSERT @ 0/11654720: prev 0/11646448; xprev 0/11646448; xid 8293: Btree - delete: node
95464/17118; tid 1/81
INSERT @ 0/11654768: prev 0/11654720; xprev 0/11654720; xid 8293: Btree - delete: node
95464/17118; tid 1/81
INSERT @ 0/11654816: prev 0/11654768; xprev 0/11654768; xid 8293: Btree - delete: node
95464/17118; tid 1/82
INSERT @ 0/11654864: prev 0/11654816; xprev 0/11654816; xid 8293: Btree - delete: node
95464/17118; tid 1/83
INSERT @ 0/11654912: prev 0/11654864; xprev 0/11654864; xid 8293: Btree - delete: node
95464/17118; tid 1/84
INSERT @ 0/11654960: prev 0/11654912; xprev 0/11654912; xid 8293: Btree - delete: node
95464/17118; tid 1/85
INSERT @ 0/11655008: prev 0/11654960; xprev 0/11654960; xid 8293: Btree - delete: node
95464/17118; tid 1/86
INSERT @ 0/11655056: prev 0/11655008; xprev 0/11655008; xid 8293: Btree - delete: node
95464/17118; tid 1/87
INSERT @ 0/11655104: prev 0/11655056; xprev 0/11655056; xid 8293: Btree - delete: node
95464/17118; tid 1/88
NOTICE: Index pg_class_oid_index: Pages 2; Tuples 88: Deleted 10. CPU 0.00s/0.00u sec.
NOTICE: Index pg_class_oid_index: NUMBER OF INDEX' TUPLES (88) IS NOT THE SAME AS HEAP'
(155).
Recreate the index.
INSERT @ 0/11655152: prev 0/11655104; xprev 0/11655104; xid 8293; bkpb 1: Btree - delete:
node 95464/17121; tid 1/1
INSERT @ 0/11663424: prev 0/11655152; xprev 0/11655152; xid 8293: Btree - delete: node
95464/17121; tid 1/1
INSERT @ 0/11663472: prev 0/11663424; xprev 0/11663424; xid 8293: Btree - delete: node
95464/17121; tid 1/1
INSERT @ 0/11663520: prev 0/11663472; xprev 0/11663472; xid 8293: Btree - delete: node
95464/17121; tid 1/71
INSERT @ 0/11663568: prev 0/11663520; xprev 0/11663520; xid 8293: Btree - delete: node
95464/17121; tid 1/72
INSERT @ 0/11663616: prev 0/11663568; xprev 0/11663568; xid 8293: Btree - delete: node
95464/17121; tid 1/73
INSERT @ 0/11663664: prev 0/11663616; xprev 0/11663616; xid 8293: Btree - delete: node
95464/17121; tid 1/74
INSERT @ 0/11663712: prev 0/11663664; xprev 0/11663664; xid 8293: Btree - delete: node
95464/17121; tid 1/86
INSERT @ 0/11663760: prev 0/11663712; xprev 0/11663712; xid 8293: Btree - delete: node
95464/17121; tid 1/87
INSERT @ 0/11663808: prev 0/11663760; xprev 0/11663760; xid 8293: Btree - delete: node
95464/17121; tid 1/88
NOTICE: Index pg_class_relname_index: Pages 2; Tuples 88: Deleted 10. CPU 0.00s/0.00u
sec.
NOTICE: Index pg_class_relname_index: NUMBER OF INDEX' TUPLES (88) IS NOT THE SAME AS
HEAP' (155).
Recreate the index.
INSERT @ 0/11663856: prev 0/11663808; xprev 0/11663808; xid 8293; bkpb 1: Heap - clean:
node 95464/1259; blk 6
INSERT @ 0/11672124: prev 0/11663856; xprev 0/11663856; xid 8293; bkpb 1: Heap - clean:
node 95464/1259; blk 1
INSERT @ 0/11680392: prev 0/11672124; xprev 0/11672124; xid 8293: Heap - move: node
95464/1259; tid 6/3; new 1/1
INSERT @ 0/11680592: prev 0/11680392; xprev 0/11680392; xid 8293: Btree - insert: node
95464/17118; tid 1/81
INSERT @ 0/11680652: prev 0/11680592; xprev 0/11680592; xid 8293: Btree - insert: node
95464/17121; tid 1/1
INSERT @ 0/11680740: prev 0/11680652; xprev 0/11680652; xid 8293: Heap - move: node
95464/1259; tid 6/5; new 1/2
INSERT @ 0/11680940: prev 0/11680740; xprev 0/11680740; xid 8293: Btree - insert: node
95464/17118; tid 1/81
INSERT @ 0/11681000: prev 0/11680940; xprev 0/11680940; xid 8293: Btree - insert: node
95464/17121; tid 1/1
INSERT @ 0/11681088: prev 0/11681000; xprev 0/11681000; xid 8293: Heap - move: node
95464/1259; tid 6/11; new 1/3
INSERT @ 0/11681288: prev 0/11681088; xprev 0/11681088; xid 8293: Btree - insert: node
95464/17118; tid 1/83
INSERT @ 0/11681348: prev 0/11681288; xprev 0/11681288; xid 8293: Btree - insert: node
95464/17121; tid 1/88
INSERT @ 0/11681436: prev 0/11681348; xprev 0/11681348; xid 8293: Heap - move: node
95464/1259; tid 6/17; new 1/4
INSERT @ 0/11681596: prev 0/11681436; xprev 0/11681436; xid 8293: Btree - insert: node
95464/17118; tid 1/87
INSERT @ 0/11681656: prev 0/11681596; xprev 0/11681596; xid 8293: Btree - insert: node
95464/17121; tid 1/74
INSERT @ 0/11681744: prev 0/11681656; xprev 0/11681656; xid 8293: Heap - move: node
95464/1259; tid 6/18; new 1/5
INSERT @ 0/11681916: prev 0/11681744; xprev 0/11681744; xid 8293: Btree - insert: node
95464/17118; tid 1/86
INSERT @ 0/11681976: prev 0/11681916; xprev 0/11681916; xid 8293: Btree - insert: node
95464/17121; tid 1/73
INSERT @ 0/11682064: prev 0/11681976; xprev 0/11681976; xid 8293: Heap - move: node
95464/1259; tid 6/28; new 1/6
INSERT @ 0/11682224: prev 0/11682064; xprev 0/11682064; xid 8293: Btree - insert: node
95464/17118; tid 1/92
INSERT @ 0/11682284: prev 0/11682224; xprev 0/11682224; xid 8293: Btree - insert: node
95464/17121; tid 1/78
INSERT @ 0/11682372: prev 0/11682284; xprev 0/11682284; xid 8293: Heap - move: node
95464/1259; tid 6/29; new 1/7
INSERT @ 0/11682532: prev 0/11682372; xprev 0/11682372; xid 8293: Btree - insert: node
95464/17118; tid 1/91
INSERT @ 0/11682592: prev 0/11682532; xprev 0/11682532; xid 8293: Btree - insert: node
95464/17121; tid 1/77
INSERT @ 0/11682680: prev 0/11682592; xprev 0/11682592; xid 8293: Heap - move: node
95464/1259; tid 6/33; new 1/8
INSERT @ 0/11682880: prev 0/11682680; xprev 0/11682680; xid 8293: Btree - insert: node
95464/17118; tid 1/90
INSERT @ 0/11682940: prev 0/11682880; xprev 0/11682880; xid 8293: Btree - insert: node
95464/17121; tid 1/3
INSERT @ 0/11683028: prev 0/11682940; xprev 0/11682940; xid 8293: Heap - move: node
95464/1259; tid 6/36; new 1/9
INSERT @ 0/11683188: prev 0/11683028; xprev 0/11683028; xid 8293: Btree - insert: node
95464/17118; tid 1/96
INSERT @ 0/11683248: prev 0/11683188; xprev 0/11683188; xid 8293: Btree - insert: node
95464/17121; tid 1/96
INSERT @ 0/11683336: prev 0/11683248; xprev 0/11683248; xid 8293: Heap - move: node
95464/1259; tid 6/37; new 1/10
INSERT @ 0/11683536: prev 0/11683336; xprev 0/11683336; xid 8293: Btree - insert: node
95464/17118; tid 1/85
INSERT @ 0/11683596: prev 0/11683536; xprev 0/11683536; xid 8293: Btree - insert: node
95464/17121; tid 1/95
INSERT @ 0/11683684: prev 0/11683596; xprev 0/11683596; xid 8293; bkpb 1: Heap - clean:
node 95464/1259; blk 5
INSERT @ 0/11691952: prev 0/11683684; xprev 0/11683684; xid 8293: Heap - move: node
95464/1259; tid 5/1; new 1/11
ERROR: Cannot insert a duplicate key into unique index pg_class_oid_index
-------------------------------------- start log --------------------------------------

Which makes me pause . . . are OIDs unique per database or per PostgreSQL installation? I
think per database. Therefore if databases are mangled togeher, then things would be
pretty messed up, oid-wise. Maybe I did something really stupid on importing mpi into
this new installation, but I don't think so. Basically what I did was

createdb mpi

and then

psql -e mpi < whatevernameIassignedtothefilewhichIdumpedtheorignaldatabaseinto

I managed to rescue my data via COPY but if this is a 7.1-related error and not
Frank-confusedness, then it looks like an evil issue indeed.

Regards, Frank

ERROR: Cannot insert a duplicate key into unique index
pg_class_oid_index
-------------------------------------- start log
--------------------------------------

Which makes me pause . . . are OIDs unique per database or
per PostgreSQL installation? I think per database. Therefore

per installation

Vadim

Frank Joerdens <frank@joerdens.de> writes:

I managed to rescue my data via COPY

Oh, good.

but if this is a 7.1-related error and not
Frank-confusedness, then it looks like an evil issue indeed.

Evil it was. The haste with which beta3 appeared should've tipped you
off that beta2 was badly broken :-(. What's puzzling us, though, is
that this bug was in the WAL code from day one, and no one noticed it
till this week. Seems like someone should have reported trouble with
beta1, if not before.

regards, tom lane

Evil it was. The haste with which beta3 appeared should've tipped you
off that beta2 was badly broken :-(. What's puzzling us, though, is
that this bug was in the WAL code from day one, and no one noticed it

Just for accuracy - this bug is not related to WAL anyhow.
This bug was in new file naming code, which was committed in Oct.

Vadim