Re: Missing tables in postgresql 7.2.4 - Mailing list pgsql-bugs

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@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
>
> ---------------------------(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@techfak.uni-bielefeld.de
Universitaet Bielefeld                 Fon: +49-521-106-2914
Postfach 100131                        Fax: +49-521-106-6411
D-33501 BIELEFELD
Germany