Re: URGENT TABLE PG_SHADOW CORRUTEP

ok thank, in single mode i try to erase two unique index on pg_shadow but postgres show this messages

this index is used by system postgres... can´t erase it....



Daniel Ricardo Medina
Ing. de Desarrollo
Computec S.A.
Tel 260 71 11 Ext 1640

________________________________

De: Peter Koczan [mailto:pjkoczan(at)gmail(dot)com]
Enviado el: Sunday, March 25, 2007 7:28 PM
Para: Tom Lane
CC: Daniel Ricardo Medina; pgsql-admin(at)postgresql(dot)org
Asunto: Re: [ADMIN] URGENT TABLE PG_SHADOW CORRUTEP

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

"Daniel Ricardo Medina" <dmedina(at)computec(dot)com(dot)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