Skip site navigation (1) Skip section navigation (2)

Re: Missing tables in postgresql 7.2.4

From: "Michael Beckstette" <mbeckste(at)TechFak(dot)Uni-Bielefeld(dot)DE>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Missing tables in postgresql 7.2.4
Date: 2005-05-11 16:11:41
Message-ID: 1050511181141.ZM13388@TechFak.Uni-Bielefeld.DE (view raw or flat)
Thread:
Lists: pgsql-bugs
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(at)TechFak(dot)Uni-Bielefeld(dot)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(at)techfak(dot)uni-bielefeld(dot)de
Universitaet Bielefeld                 Fon: +49-521-106-2914
Postfach 100131                        Fax: +49-521-106-6411
D-33501 BIELEFELD
Germany

In response to

Responses

pgsql-bugs by date

Next:From: Michael BeckstetteDate: 2005-05-11 17:55:21
Subject: Re: Missing tables in postgresql 7.2.4
Previous:From: Tom LaneDate: 2005-05-11 16:02:04
Subject: Re: Bug Report with Postgres 7.4 on AIX 5.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group