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
