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

Re: strange disappearence of postgres file

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Harald Armin Massa <haraldarminmassa(at)gmail(dot)com>
Cc: Qingqing Zhou <zhouqq(at)cs(dot)toronto(dot)edu>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: strange disappearence of postgres file
Date: 2005-11-23 20:20:56
Message-ID: 20051123202056.GA29007@surnet.cl (view raw or flat)
Thread:
Lists: pgsql-bugs
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.

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-11-23 20:30:01
Subject: Re: Incorrect column identifer using AS in SELECT statement on a VIEW.
Previous:From: Kris JurkaDate: 2005-11-23 19:44:52
Subject: Re: BUG #2060: Issue with Data base connection

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