Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 86D2E9FA15F for ; Wed, 7 Feb 2007 18:08:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 90160-05 for ; Wed, 7 Feb 2007 18:07:58 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from power2u.goelsen.net (mail.goelsen.net [195.202.170.130]) by postgresql.org (Postfix) with ESMTP id 023359F93A4 for ; Wed, 7 Feb 2007 18:07:57 -0400 (AST) X-Envelope-From: michael.monnerie@it-management.at Received: from localhost (localhost [127.0.0.1]) by power2u.goelsen.net (Postfix) with ESMTP id 6A40F77555; Wed, 7 Feb 2007 23:07:55 +0100 (CET) Received: from power2u.goelsen.net ([127.0.0.1]) by localhost (power2u.goelsen.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 30273-02; Wed, 7 Feb 2007 23:07:47 +0100 (CET) X-Envelope-From: michael.monnerie@it-management.at Received: from mailsrv.i.zmi.at (h081217116113.dyn.cm.kabsi.at [81.217.116.113]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "mailsrv.i.zmi.at", Issuer "mailsrv.i.zmi.at" (not verified)) by power2u.goelsen.net (Postfix) with ESMTP id 751F670136; Wed, 7 Feb 2007 23:07:47 +0100 (CET) From: Michael Monnerie Organization: it-management http://it-management.at To: pgsql-admin@postgresql.org Subject: Re: destroyed db/index (corruption) [SOLVED] Date: Wed, 7 Feb 2007 23:07:41 +0100 User-Agent: KMail/1.9.5 References: <200702051500.19196@zmi.at> <200702052236.13498@zmi.at> <200702060946.35367.achill@matrix.gatewaynet.com> In-Reply-To: <200702060946.35367.achill@matrix.gatewaynet.com> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart2645279.sHLbvs4RVu"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <200702072307.45566@zmi.at> X-Virus-Scanned: amavisd-new at goelsen.net X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.019 tagged_above=0 required=5 tests=AWL, BAYES_40, FORGED_RCVD_HELO, SPF_HELO_PASS X-Spam-Level: X-Archive-Number: 200702/114 X-Sequence-Number: 24579 --nextPart2645279.sHLbvs4RVu Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Dienstag, 6. Februar 2007 08:46 Achilleas Mantzios wrote: > 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 ? After your tip, I rechecked the old logs. There, I had "Tabelle enth=C3=A4lt duplizierte Werte" (table contains duplicate values) but now the log message was "duplizierter Schl=C3=BCssel verletzt Unique-Constraint =C2=BBbayes_token_p= key=C2=AB" (duplicate key violates unique constraint) The latter message is just an INFO, though its logged as ERROR. It=20 happens when you try to INSERT a primary key that's already there, but=20 this got only logged once: ZUSAMMENHANG: SQL-Anweisung =C2=BBINSERT INTO bayes_token (id, token,=20 spam_count, ham_count, atime) VALUES ( $1 , $2 , $3 , $4 , $5 )=C2=AB PL/pgSQL function "put_tokens" line 18 at SQL statement All other times just the violation was logged. I double checked with a dump and reload to a fresh db - there is no=20 spoon [The Matrix], and also no duplicate values exist. The error I got december 2006 with the duplicate values was really a=20 situation where values seem to have been inserted during a reload, but=20 before the index was there. I was just nervous and mixed the two=20 different warnings. But I'd like to suggest changing the ERROR to WARNING for "duplicate key=20 violates unique constraint", as it's not serious - but it sounds like a=20 big problem. Efharisto, Achilleas! mfg zmi =2D-=20 // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0676/846 914 666 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi4.asc | gpg --import" // Fingerprint: EA39 8918 EDFF 0A68 ACFB 11B7 BA2D 060F 1C6F E6B0 // Keyserver: www.keyserver.net Key-ID: 1C6FE6B0 --nextPart2645279.sHLbvs4RVu Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQBFyk2xui0GDxxv5rARAhZyAJ9yIpgnpbyv9p2HdBJADQpXg8hTpACdFsTg OXTGEXeyYM8Dpten07jCG9I= =bPUf -----END PGP SIGNATURE----- --nextPart2645279.sHLbvs4RVu--