Message-ID: <20080623135920.209778s2iyk9se80@webmail-html.freesurf.fr>
Date: Mon, 23 Jun 2008 13:59:20 +0200
From: hjeancha@freesurf.fr
To: Decibel! <decibel@decibel.org>
Cc: pgsql-admin@postgresql.org
Subject: Re: vacuumdb not enough stack items
References: <20080621154752.33867bg7xhmsv04c@webmail-html.freesurf.fr>
	<89B74A56-663D-4334-B5BE-B3E73617AA02@decibel.org>
In-Reply-To: <89B74A56-663D-4334-B5BE-B3E73617AA02@decibel.org>
MIME-Version: 1.0
Content-Type: text/plain;
	charset=ISO-8859-1;
	DelSp="Yes";
	format="flowed"
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
User-Agent: Internet Messaging Program (IMP) H3 (4.2-RC2)

Decibel! <decibel@decibel.org> a =E9crit=A0:

> On Jun 21, 2008, at 8:47 AM, hjeancha@freesurf.fr wrote:
>> i use postgresql version7.4.7 on i386-pc-linux-gnu, autovacuum is  =20
>> configured to run on this database.
>> But recently, we lost data for a database, we were able to connect  =20
>> the database but we couldn't see any table anymore.
>> I suspected a transaction ID wraparound, and to fix it, i just  =20
>> imported data from a dump file.
>> I tried to execute the following  query:
>> SELECT datname, age(datfrozenxid) FROM pg_database where age =20
>> (datfrozenxid)<0;
>> template1=3D# SELECT datname, age(datfrozenxid) FROM pg_database  =20
>> where age(datfrozenxid)<0;
>>                 datname                  |     age
>> ------------------------------------------+-------------
>> template0                                |  -597653514
>> ez_info1                                 |  -205024652
>> XtDB                                     |  -205024658
>>
>>
>> Does the negative value of age means that a transaction ID  =20
>> wraparound has already occured?
>
> That would be my guess.
>
>> Doing a manual vacuumdb on each database raise an error message  saying:
>> vacuumdb: vacuuming of database "ez_info1" failed: ERREUR:  not  =20
>> enough stack items
>
> That's probably a bug in vacuum, but even so, you'll want to re- =20
> initdb if you want to just pull back in from a pg_dump.
>
> If you can take the downtime to do a pg_dump/restore, you should  =20
> really upgrade to a more recent version. You'll get integrated  =20
> autovacuum and code that makes wraparound impossible. I would not  =20
> expect you to have any major code issues going to 8.0 or 8.1. Of  =20
> course going to 8.3 would be better, but you could run into some  =20
> compatibility issues. You'll get a performance gain to boot.
> --=20
> Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
> Give your computer some brain candy! www.distributed.net Team #1828
>
>
>


Hello,

We plan to upgrade to postgresql release 8.1 very soon.
But for now, i found that there is a problem with vacuum.
Doing a vaccuum verbose analyze manually on a database,
we got the error "primary keys constraint broken by duplicate key  =20
=ABpg_statistic_relid_att_index=BB.
As suggested on this list it's seems to be an index problem.
That could explain why we got "transaction wraparound" for some databases th=
at
were never really vacuumed because of duplicate key error in  =20
=ABpg_statistic_relid_att_index=BB.
I red to solve this problem, one must "delete  from pg_statistic" then =20
reindex database.
I just want to know:

1) if it's safe to do this, because data is still accessible even for =20
databases with transaction wraparound xid.
If This can fix the =ABpg_statistic_relid_att_index=BB, i will be able to =
=20
vacuum cleanly every databases.

2)
The age(datfrozenxid) for other databases are
  ez_modele                   |  1074290700
  ez_modele2                  |  1074290002
Does that means, if there are not a lot of transactions,  transaction =20
wraparound xid won't happen until reaching the limit of 2 billions =20
transactions.


Thank you,