From: hjeancha@freesurf.fr
To: Decibel! <decibel@decibel.org>
Cc: pgsql-admin@postgresql.org
Subject: Re: vacuumdb not enough stack items
Date: Mon, 23 Jun 2008 13:59:20 +0200
Message-ID: <20080623135920.209778s2iyk9se80@webmail-html.freesurf.fr> (raw)
In-Reply-To: <89B74A56-663D-4334-B5BE-B3E73617AA02@decibel.org>
References: <20080621154752.33867bg7xhmsv04c@webmail-html.freesurf.fr>
<89B74A56-663D-4334-B5BE-B3E73617AA02@decibel.org>
Decibel! <decibel@decibel.org> a écrit :
> 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
>> configured to run on this database.
>> But recently, we lost data for a database, we were able to connect
>> the database but we couldn't see any table anymore.
>> I suspected a transaction ID wraparound, and to fix it, i just
>> imported data from a dump file.
>> I tried to execute the following query:
>> SELECT datname, age(datfrozenxid) FROM pg_database where age
>> (datfrozenxid)<0;
>> template1=# SELECT datname, age(datfrozenxid) FROM pg_database
>> where age(datfrozenxid)<0;
>> datname | age
>> ------------------------------------------+-------------
>> template0 | -597653514
>> ez_info1 | -205024652
>> XtDB | -205024658
>>
>>
>> Does the negative value of age means that a transaction ID
>> 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
>> enough stack items
>
> That's probably a bug in vacuum, but even so, you'll want to re-
> 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
> really upgrade to a more recent version. You'll get integrated
> autovacuum and code that makes wraparound impossible. I would not
> expect you to have any major code issues going to 8.0 or 8.1. Of
> course going to 8.3 would be better, but you could run into some
> compatibility issues. You'll get a performance gain to boot.
> --
> 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
«pg_statistic_relid_att_index».
As suggested on this list it's seems to be an index problem.
That could explain why we got "transaction wraparound" for some databases that
were never really vacuumed because of duplicate key error in
«pg_statistic_relid_att_index».
I red to solve this problem, one must "delete from pg_statistic" then
reindex database.
I just want to know:
1) if it's safe to do this, because data is still accessible even for
databases with transaction wraparound xid.
If This can fix the «pg_statistic_relid_att_index», i will be able to
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
wraparound xid won't happen until reaching the limit of 2 billions
transactions.
Thank you,
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: hjeancha@freesurf.fr, decibel@decibel.org
Subject: Re: vacuumdb not enough stack items
In-Reply-To: <20080623135920.209778s2iyk9se80@webmail-html.freesurf.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox