From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@postgresql.org
Subject: Re: destroyed db/index (corruption)
Date: Tue, 6 Feb 2007 09:46:34 +0200
Message-ID: <200702060946.35367.achill@matrix.gatewaynet.com> (raw)
In-Reply-To: <200702052236.13498@zmi.at>
References: <200702051500.19196@zmi.at>
<200702051622.55632.achill@matrix.gatewaynet.com>
<200702052236.13498@zmi.at>
Στις Δευτέρα 05 Φεβρουάριος 2007 23:36, ο/η Michael Monnerie έγραψε:
> On Montag, 5. Februar 2007 15:22 Achilleas Mantzios wrote:
> > This ERROR is normally thrown when you *try* to violate a unique
> > constraint. Can you demonstrate the presence of the UNIQUE constraint
> > on your table and the pair of identical key values rows?
> > Post a description (\d) of your table, and then the select stmt which
> > return the 2 bad rows.
>
> Kalispera Achilleas,
:)
>
> I will do this soon. Just now I'm upgrading that VM from SUSE 10.1 to
> 10.2, so later this night I'll reload the databases.
>
> > I might be wrong, but i would bet the whole issue began because
> > some rows were inserted by a client (other than the reloading psql)
> > during reload but before the UNIQUE KEY DDL
> > was executed, or smth like that, or simply because this bayes_seen
> > value is attempted to be inserted again
> > (for reasons beyond our scope)
>
> No, I started those DBs fresh with the command
>
> pg_restore -v pg.fulldump.bayes_power2u.sqlz | \
> psql -U postgres bayes_pg3 -f -
>
Thats what i mean, postmaster is alive, and accepting connections
during the above command, so what would prevent a 2nd client (from your mail
server setup most likely)
from inserting a value that is later present in the dump,
which is later attempted to be COPY'ed to the table
and ofcource that violates the PRIMARY KEY CONSTRAINT?
In other words, do you have full control of your DB when that happens?
If some 2nd client, not controled by you, inserts a value that is later
found in the dump, it is completely normal that this ERROR will be thrown.
Have you tried, for instance, disabling all unwanted access to postmaster,
e.g. through data/pg_hba.conf ?
> Is there an easy way to find out which key is duplicated while I have
> that DB running? The log doesn't show it, and the last time I did
> reload with trial-and-error:
> - start reload
> - it stops somewhere complaining about line 34587328 or similiar
> - make some tail & head foo, or vi, to delete the one duplicate line
> - start again...
>
The "steps" you mentioned earlier are not what the average admin whould do.
For the average admin that would trigger some kind of panic mode!
It is simply not normal to have this ERROR produced by the dump alone.
Try to isolate your DB, by controling access to it, and try to replicate
the problem (if any).
> It would be nice to have a simpler process. Could it be that the stored
> procedure could cause a problem? I took all that from the SpamAssassin
> Wiki, without modification.
>
> mfg zmi
--
Achilleas Mantzios
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: destroyed db/index (corruption)
In-Reply-To: <200702060946.35367.achill@matrix.gatewaynet.com>
References: <200702051500.19196@zmi.at> <200702051622.55632.achill@matrix.gatewaynet.com> <200702052236.13498@zmi.at> <200702060946.35367.achill@matrix.gatewaynet.com>
* 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