Re: Missing tables in postgresql 7.2.4

Hi,

after reading the docs (I know it was a little bit late), I am now relatively
sure that I trapped into a transaction ID wraparound problem. For me its now a
little bit unclear, how to proceed in order to minimize the caused damage.

I checked all tables in the affected DB. Till now, the results are as follows:

6 user tables are completely lost.
8 user tables are not listed in pg_tables but still accessible by a
SELECT.

The 6 completely lost tables are not so dramatical, because they contain only
static data, that I can restore from the development system. But what happens
with the 8 tables that are still accessable, but not listed in pg_tables, after
a VACUUM? Will they be removed completely or 'reinserted' into pg_tables?

Does anyone has an advise how to proceed in this situation?

Regards
Michael

On May 11, 6:11pm, Michael Beckstette wrote:
> Subject: Re: [BUGS] Missing tables in postgresql 7.2.4
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>-- End of excerpt from Michael Beckstette

--

------------------------------------------------------------------------------
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