Thread: Apparent race in information_schema.tables

This query:

SELECT 1 FROM information_schema.tables WHERE table_schema = $1 AND table_name = $2;

fails sporadically with the error "relation with OID <number> does not
exist".  The query is run by a non-superuser, and the table/schema
combination exists in the database.  The query may have been PREPAREd
(it's submitted using DBD::Pg with the default flags)---I would have
to turn on logging to discover this, and I'm somewhat reluctant to do
so.

I guess the OID refers to a temporary table because I can't find it in
pg_class, and the cause is a race between listing the tables and
applying the permission checks to them (which I don't need anyway, I
think) because tables in the system catalog are not subject to MVCC.
That suggests the problem should go away when I query pg_tables
instead, but I haven't tried that yet.

This happens with 8.2.6 and 8.2.13, and only while there is
significant CREATE TEMPORARY TABLE/DROP TABLE activity in an other
backend process.

--
Florian Weimer                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstraße 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99

Florian Weimer <fweimer@bfk.de> writes:
> This query:
> SELECT 1 FROM information_schema.tables WHERE table_schema = $1 AND table_name = $2;

> fails sporadically with the error "relation with OID <number> does not
> exist".

> I guess the OID refers to a temporary table because I can't find it in
> pg_class, and the cause is a race between listing the tables and
> applying the permission checks to them (which I don't need anyway, I
> think) because tables in the system catalog are not subject to MVCC.

Yeah, that's what I guess too.  There is a change in 8.4 that should
prevent this class of failures:

2008-12-15 13:09  tgl

    * src/: backend/catalog/namespace.c, backend/utils/adt/acl.c,
    test/regress/expected/privileges.out: Arrange for the
    pg_foo_is_visible and has_foo_privilege families of functions to
    return NULL, instead of erroring out, if the target object is
    specified by OID and we can't find that OID in the catalogs.  Since
    these functions operate internally on SnapshotNow rules, there is a
    race condition when using them in user queries: the query's MVCC
    snapshot might "see" a catalog row that's already committed dead,
    leading to a failure when the inquiry function is applied.
    Returning NULL should generally provide more convenient behavior.
    This issue has been complained of before, and in particular we are
    now seeing it in the regression tests due to another recent patch.

            regards, tom lane