Re: Rare corruption of pg_class index

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
> Oh! Duh, that's your issue right there, I'll bet. The problem is that
> relcache-open tries to read the pg_class row under SnapshotNow rules,
> and if there is another xact concurrently modifying the row, it is
> entirely possible for none of the row versions to be committed good at
> the instant they are visited. (The new row version either isn't seen at
> all or isn't committed good yet when it's visited, and later when the
> old row version is visited, it has become committed dead.) This results
> in ScanPgRelation failing (returning NULL) which leads to exactly the
> "could not open relation with OID xxx" symptom --- and in fact I see no
> other code path that yields that failure.

Doesn't this violate ACID, or am I misunderstanding something? (FWIW, I'm
using a serializable isolation level for the process that changes pg_class)

> As of 8.2 we have this problem fixed for system-initiated changes to the
> pg_class row, but you're still going to be at risk if you are doing
> manual "UPDATE pg_class" operations. Can you get away from needing to
> do that? ALTER TABLE DISABLE TRIGGER might help, but we haven't got
> anything like ALTER TABLE DISABLE RULE.

Yeah, triggers alone won't do it. Sounds like a TODO item - Bruce?

> In any case the important point is that you have to take AccessExclusive
> lock on a relation whose pg_class row you would like to change, and you
> need to be on 8.2 because prior releases weren't careful about obtaining
> lock *before* reading the row.

Obtaining an AccessExclusive lock is a last resort, as the tables in question
are very busy. That's another reason why DISABLE TRIGGER might not work out
either.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200701021325
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFFmqU9vJuQZxSWSsgRAgZ4AJ4wBUI6APz658zaE8bFQ5xmILFiugCgxfsW
GI2zgdF6l/tmxWpnO4J9dms=
=NtIn
-----END PGP SIGNATURE-----