TANIDA Yutaka <[EMAIL PROTECTED]> writes: > My customer found a problem about PL/pgsql functions and TRUNCATE command. > If you execute PL/pgsql function includeing TRUNCATE command concurrently, > causes "relation ... does not exist." or "relation with OID XXXXX does not > exist" against > exists table. > Here's a testcase to reproduce this.
After some thought I have a theory about what's happening here. The test case involves lots of TRUNCATEs, which each will do an update on the relation's pg_class row. Now an incoming operation on the table has to look up the relation's OID before it can obtain lock, so that means that it is scanning pg_class using the relname index concurrently with these updates. That scan is done using SnapshotNow rules, which means that it's possible for this sequence of events to occur: 1. TX A updates pg_class row. 2. TX B visits the updated row while scanning; it's not committed good, so it's ignored. 3. TX A commits. 4. TX B visits the old row in its scan. By now it's committed dead, so it's also ignored. 5. Hence TX B fails to find any live row matching the requested table name, and comes back with "relation does not exist". I'm not sure about a good way to fix this. It sorta looks like we need a different visibility rule for scanning pg_class when we don't yet have any lock on the relation, but I'm unclear what that rule ought to be. This also ties into the discussions we've had off-and-on about making catalog lookups behave in an MVCC fashion instead of using SnapshotNow. I'm still pretty hesitant to go there, but maybe we could do something involving MVCC for unlocked lookups and then SnapshotNow for (re)reading a table's schema info once we've got lock on it. Ideas anyone? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend