Re: Rare corruption of pg_class index

On Wed, 2006-12-20 at 18:06 +0000, Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> I'm encountering some disconcerting problems on a 8.1.3 database.
> Very occasionally, I get a "could not open relation with OID xxx".
> This always occurs inside of a plpgsql function, and always refers
> to a normal, stable table that has not been dropped. The first
> time this occured, I reindexed the system indexes, but it has occured
> again, and on more than one database, which seems to lessen the chance
> of a hardware issue or a temporary index corruption issue. The functions
> in question are called many times, but the error only happens once in
> a blue moon. The last time it happened, the function ran with no problem
> one minute before the error, and again four minutes afterwards. The
> table name is hard-coded into the functions. This happens on average
> five times a day or so, on a very busy database (> 10M statements/day,
> the functions fire tens of thousands of times)
>

It has to do with PL/pgSQL caching the plans of the statements inside.
If there is some kind of change that causes that plan to become invalid,
that could cause that error.

You can almost certainly fix it by using EXECUTE in the function, which
will not cache the plan, or telling all clients to reconnect when you
make a change that could invalidate the plan.

Regards,
Jeff Davis