"Greg Sabino Mullane" <greg@turnstep.com> writes:
> 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.
> Doesn't this violate ACID, or am I misunderstanding something?
No, it's not a violation of ACID. In this case what you are doing is
altering a table's schema without a sufficiently strong lock on the
table, and that's a no-no, whether you would like it to be or not.
>> 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.
Well, ENABLE/DISABLE TRIGGER can't conceivably operate correctly without
locking out writes, because it wouldn't be clear whether any particular
write operation should fire the trigger or not. A hypothetical
ENABLE/DISABLE RULE would be worse: AFAICS it'd have to lock out reads
too, else it wouldn't be clear whether SELECTs should notice an ON
SELECT rule. You can't get around those restrictions by trying to
implement the enable/disable yourself via UPDATE pg_class; as you've
found out, it just doesn't work.
regards, tom lane