Re: ACK table corrupted, unique index violated.

Tom,

I'm a little uncomfortable about giving ssh access to our box. We have a
lot of sensitive information in the database, and we would be violating our
privacy policy by giving someone access. If there is some way I could give
you any information, or help you out that would be better. I ended up
shutting down postgres and copying the pgdata directory somewhere else and
re-creating the database -- so i do have a copy of the corrupted database.

I've been doing a little investigating and i might have a possible lead.
The two tables that were corrupted recently had new indexes put on them that
are based on a plpgsql function. Basically in the form "create index blah
on table(myfunction(blah_id))" These are the only two tables in my system
that have an index using a plpgsql function. Both tables became corrupt on
the same day, and the corruption happened the night that i added the
indexes. I have no imperical evidence to support that this is the cause,
but it seems possible.

One other note, even after recreating the database, I'm getting NOTICE
"InvalidateShardeInvalid" and "RegisterSharedInvalid: SI buffer overflow".
I never used to get them and now I'm getting tons of them. Should this
concern me? I don't understand the implications.

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Brian Hirt" <bhirt(at)mobygames(dot)com>
Cc: "Postgres Hackers" <pgsql-hackers(at)postgresql(dot)org>;
<pgsql-general(at)postgresql(dot)org>; "Brian A Hirt" <bhirt(at)berkhirt(dot)com>
Sent: Wednesday, December 12, 2001 11:01 AM
Subject: Re: [GENERAL] ACK table corrupted, unique index violated.

> "Brian Hirt" <bhirt(at)mobygames(dot)com> writes:
> > In a nutshell, my primary key index got a NOTICE to recreate when the
> > database was vacuumed. I dropped the index and tried to recreate it. I
get
> > a key violation when i try to do this. I find there are some 200 rows
> > with the exact same developer_id and oid.
>
> You're the third person to have reported something like this, so there's
> something strange going on. Can you give access to your system to
> someone who can poke into it (probably me or Vadim)?
>
> > There are some NOTICES the day
> > before that i don't know what they mean, but don't look good.
>
> > NOTICE: Cannot rename init file
> > /moby/pgsql/base/156130/pg_internal.init.19833 to
> > /moby/pgsql/base/156130/pg_internal.init: No such file or directory
>
> These seem extremely bizarre.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>