TANIDA Yutaka <tanida@sraoss.co.jp> 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 2: Don't 'kill -9' the postmaster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> 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.

No ideas, but a strong +1 for making catalog lookups MVCC. Can this perhaps
become a TODO so we don't forget about it and possibly entice people to
give it a go?

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200703262326
http://biglumber.com/x/web?pk=2529DF...9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFGCI8ZvJuQZxSWSsgRA71vAKCNHCRtQUhxVoYKiSmxUA ohFSE6TgCeN5qt
sdb4PWjhBn+6sepNPTWkArQ=
=18qw
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster