Re: strange disappearence of postgres file

Harald Armin Massa wrote:
> Alvaro,
>
> > How many database-wide vacuums did you run during these 9 months? I'm
> > smelling transaction Id wraparound in pg_class or some other system
> > catalog.
>
> "database wide" vacuums have been very very seldom; I fear the only one was
> at initial db bulk load. at the moment I am on a way to upgrade to 8.1, with
> autovacuum enabled ... so in the long run THAT possible reason should
> vanish.
>
> And I understand correctly that your suspicion is: file gets killed because
> of transaction id wraparound in system table?

No, the file doesn't get killed, but an old tuple on the pg_class system
catalog magically appears because it was deleted and marked with a very
old Xmax, that after the wraparound is seen as future; and thus the
tuple that was deleted is suddenly visible. For this to happen you need
to have failed to vacuum pg_class in a very long time.

There may be other explanations, but we'd need more data, like how much
tuples for that table you got on pg_class and what do they look like.
Try something like

set enable_indexscan to off;
select xmin, xmax, relname from pg_class where relname = 'the table';

You can search the archives using http://www.pgsql.ru

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.