Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 042959FB30B for ; Sun, 25 Mar 2007 21:28:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 04457-05 for ; Sun, 25 Mar 2007 21:27:59 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id 296439FB269 for ; Sun, 25 Mar 2007 21:27:58 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id k3so1357891ugf for ; Sun, 25 Mar 2007 17:27:57 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=QhBF9x11pn/qQvr5lrULgYJCunlni2yJ3m7CkMptoK0tBDZuA5Gr7++wn5MiCd/ph+pD9TQL24ZSqqK2izejVcxwiC/an4/QdfCYC/eqvXQaOi4dGu2JTnCB+qFWsUGo6GRm3lLomSNAirzr8lauwLNexvxU5wz5lX+Hejoq/Q8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=X7uDP0GgRFvwnTFDNZgYCQO2WxCO2cZbhdOWcC+YGJ1U2lXau1AykVyd8yX3Z282i5+AxHGWi882dSXWIi/u8y2wQ3mxUgSuhfE9sqhxr9a4ljZSQmql8RmsL5xd1nX9nLOToY5pYNhiV3jdStJz4j8SOUGYv8Aln+Ke53CCk8M= Received: by 10.114.78.1 with SMTP id a1mr2402900wab.1174868876288; Sun, 25 Mar 2007 17:27:56 -0700 (PDT) Received: by 10.114.199.18 with HTTP; Sun, 25 Mar 2007 17:27:56 -0700 (PDT) Message-ID: <4544e0330703251727xd988c1ue4de1494f79b8933@mail.gmail.com> Date: Sun, 25 Mar 2007 19:27:56 -0500 From: "Peter Koczan" To: "Tom Lane" Subject: Re: URGENT TABLE PG_SHADOW CORRUTEP Cc: "Daniel Ricardo Medina" , pgsql-admin@postgresql.org In-Reply-To: <8911.1174848707@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_230484_352911.1174868876239" References: <873F3E256E0BF14EA84EAC54C2886B5D0B9832@computecnew.redcorp.computec.com> <8911.1174848707@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/184 X-Sequence-Number: 25008 ------=_Part_230484_352911.1174868876239 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I saw something along these lines recently when I was running stuff from postgres 7.4.7 (we recently upgraded to 8.2.3). Basically, a unique index somehow fails and allows duplicate rows...but I don't know why. Deleting doesn't work because postgres only looks for the record shown by the index (though this may have been fixed in more recent releases). At least this is the behaviour I observed. The way I fixed it was to drop the unique index, remove the offending rows, reinsert only one of the offending rows (so things would once again be unique), and remake the index. It worked. There are two things that concern me: 1. Trying to do this on a system table is likely ill-advised at best, and dangerous at worst. 2. If the table only has 3 rows in it and it got corrupted, there's something really wrong. My table in question was a user table that had almost a million rows. On 3/25/07, Tom Lane wrote: > > "Daniel Ricardo Medina" writes: > > I have try to run this command in single user mod > > > > select * from pg_shadow; > > delete from pg_shadow; > > CREATE USER postgres WITH CREATEDB CREATEUSER PASSWORD '***' SYSID 1; > > CREATE USER userradar PASSWORD '***' > > > but the result is the same..... > > What do you see if you look in pg_shadow right after doing the DELETE? > What PG version is this? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > ------=_Part_230484_352911.1174868876239 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I saw something along these lines recently when I was running stuff from postgres 7.4.7 (we recently upgraded to 8.2.3). Basically, a unique index somehow fails and allows duplicate rows...but I don't know why.

Deleting doesn't work because postgres only looks for the record shown by the index (though this may have been fixed in more recent releases). At least this is the behaviour I observed.

The way I fixed it was to drop the unique index, remove the offending rows, reinsert only one of the offending rows (so things would once again be unique), and remake the index. It worked.

There are two things that concern me:
1. Trying to do this on a system table is likely ill-advised at best, and dangerous at worst.
2. If the table only has 3 rows in it and it got corrupted, there's something really wrong. My table in question was a user table that had almost a million rows.

On 3/25/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Daniel Ricardo Medina" <dmedina@computec.com.co> writes:
> I have try to run this command in single user mod
>
> select * from pg_shadow;
> delete from pg_shadow;
> CREATE USER postgres WITH CREATEDB CREATEUSER PASSWORD '***' SYSID 1;
> CREATE USER userradar PASSWORD '***'

> but the result is the same.....

What do you see if you look in pg_shadow right after doing the DELETE?
What PG version is this?

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate

------=_Part_230484_352911.1174868876239--