Hi Tom,
this is the output from 'SELECT datname, age(datfrozenxid) FROM pg_database;'
datname | age
-------------+-------------
xgc | -1950241750
dev_db | -1886587214
template1 | -1884294460
template0 | -1884294460
promo_db | -1884294460
snap_db_new | -1884294460
gendev_db | 1887538137
(7 rows)
dev_db=#
The affected DB is 'dev_db', although it looks like the others except
'gendev_db' have a wraparound problem too (?). To answer your question about
the VACUUM: We VACUUM FULL a few tables with a high amount of INSERT/DEL
operations once per hour, but I have to admit that we perform a VACUUM of the
whole DB not on a regulary basis. I think the last one was several monthes ago.
Further on we use transactions at several places and we have at least 20
transactions per minute.
Does now a normal VACUUM FULL of the whole DB(s) fix our problem?
Michael
On May 11, 11:51am, Tom Lane wrote:
> Subject: Re: [BUGS] Missing tables in postgresql 7.2.4
> "Michael Beckstette" <mbeckste@TechFak.Uni-Bielefeld.DE> writes:
> > we recently discovered on our production database an a little bit bizarre
> > problem (after two years stable operations). Some tables are simply
missing, or
> > sometimes the affected table(s) is/are there but not listed in pg_tables.
>
> This sounds a bit like a transaction ID wraparound problem. Have you
> been vacuuming your whole database on a reasonable schedule? The
> missing tables might conceivably be old enough that their pg_class rows
> have wrapped around "into the future". It'd be useful to look at
> SELECT datname, age(datfrozenxid) FROM pg_database;
>
> regards, tom lane
--
------------------------------------------------------------------------------
Dipl.-Inform. Michael Beckstette Office: M3-129
AG-PI / Technische Fakultaet EMail:mbeckste@techfak.uni-bielefeld.de
Universitaet Bielefeld Fon: +49-521-106-2914
Postfach 100131 Fax: +49-521-106-6411
D-33501 BIELEFELD
Germany
