Re: [GENERAL] 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@sss.pgh.pa.us>
To: "Brian Hirt" <bhirt@mobygames.com>
Cc: "Postgres Hackers" <pgsql-hackers@postgresql.org>;
<pgsql-general@postgresql.org>; "Brian A Hirt" <bhirt@berkhirt.com>
Sent: Wednesday, December 12, 2001 11:01 AM
Subject: Re: [GENERAL] ACK table corrupted, unique index violated.


> "Brian Hirt" <bhirt@mobygames.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@postgresql.org so that your
> message can get through to the mailing list cleanly
>