From: Peter Koczan <pjkoczan@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Daniel Ricardo Medina <dmedina@computec.com.co>
Cc: pgsql-admin@postgresql.org
Subject: Re: URGENT TABLE PG_SHADOW CORRUTEP
Date: Sun, 25 Mar 2007 19:27:56 -0500
Message-ID: <4544e0330703251727xd988c1ue4de1494f79b8933@mail.gmail.com> (raw)
In-Reply-To: <8911.1174848707@sss.pgh.pa.us>
References: <873F3E256E0BF14EA84EAC54C2886B5D0B9832@computecnew.redcorp.computec.com>
<8911.1174848707@sss.pgh.pa.us>
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
>
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: pjkoczan@gmail.com, tgl@sss.pgh.pa.us, dmedina@computec.com.co
Subject: Re: URGENT TABLE PG_SHADOW CORRUTEP
In-Reply-To: <4544e0330703251727xd988c1ue4de1494f79b8933@mail.gmail.com>
References: <873F3E256E0BF14EA84EAC54C2886B5D0B9832@computecnew.redcorp.computec.com> <8911.1174848707@sss.pgh.pa.us> <4544e0330703251727xd988c1ue4de1494f79b8933@mail.gmail.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