Re: URGENT TABLE PG_SHADOW CORRUTEP

["Daniel Ricardo Medina" <dmedina(at)computec(dot)com(dot)co>]

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