Visibility issue with pg_table_is_visible

A week ago, I had a discussion with AndrewSN (Andrew Sullivan, I
suppose) on #postgresql IRC channel about the following issue.

I have a function, ss_info(text, text) which stores/replaces given key
and value in ss_info temporary table; the table is created unless exists
yet. The function looked like this:

CREATE OR REPLACE FUNCTION ss_info(text, text) RETURNS text AS '
DECLARE       _x      integer;
BEGIN       SELECT  1 INTO _x FROM pg_class       WHERE   relname = ''ss_info''       AND     relkind = ''r''       AND
   table_is_visible(oid);
 
       IF NOT FOUND THEN               EXECUTE ''CREATE TEMP TABLE ss_info (var text, value text) WITHOUT OIDS'';
ELSE              EXECUTE ''DELETE FROM ss_info WHERE var = ''||quote_literal($1);       END IF;       EXECUTE ''INSERT
INTOss_info VALUES ('' ||               quote_literal($1) || '', '' || coalesce(quote_literal($2), ''NULL'') ||
     '')'';       RETURN $2;
 
END' LANGUAGE 'plPgSQL';

And a similar function ss_info(text) getting a value by key from that
table.

Sometimes, very infrequently (up to several times from nearly
10,000..20,000 executions a week), I beheld the following error
on 8.0.1-3 (not sure about 7.4.x):

ERROR:  cache lookup failed for relation 1522203
CONTEXT:  SQL statement "SELECT  1 FROM pg_class WHERE relname = 'ss_info' AND relkind = 'r' AND
pg_table_is_visible(oid)"

Here's the end of our discussion:

<AndrewSN>    the problem is this:
<AndrewSN>    that query on pg_class will first find the oid of _every_ ss_info table, including ones in other
backends,
<AndrewSN>    and then call pg_table_is_visible
<AndrewSN>    _but_
<AndrewSN>    if another backend exits or drops the table, its ss_info table can be gone from SnapshotNow even though
it'sstill visible in the query snapshot
 
<AndrewSN>    and pg_table_is_visible uses the syscache, which is always in SnapshotNow
<fduch-m>    AndrewSN: Much clearer now... Is there any workaround?
<AndrewSN>    hm, there might be another way to form the query that doesn't have the same risk
<AndrewSN>    maybe check for has_schema_privilege(relnamespace,'USAGE') rather than pg_table_is_visible
<AndrewSN>    no, that's not enough in itself
<AndrewSN>    how about: WHERE relname='ss_info' AND relkind='r' AND CASE WHEN
has_schema_privilege(relnamespace,'USAGE')THEN pg_table_is_visible(oid) ELSE FALSE END;
 
<AndrewSN>    that checks visibility only when we already know the namespace is accessible, so temp schemata of other
backendswill already be excluded (since we have no permissions on them)
 
<AndrewSN>    (the CASE is needed to control evaluation order)
<fduch-m>    AndrewSN: Won't has_schema_privilege have a similar effect when other temp namespace is also dropped
already?
<AndrewSN>    temp namespaces aren't dropped, they're recycled instead
<AndrewSN>    (you'll see them accumulate in pg_namespace if you look)
<AndrewSN>    there's never more than max_connections of them, though, because they're named by the backend slot
number
<AndrewSN>    fduch-m: btw, you should post this issue to the mailing lists, for the benefit of those of the developers
thatdon#t do irc
 
<fduch-m>    AndrewSN: Thanks, I'll try it. But I'm not sure I can certainly reproduce the same case...

After that I modified my functions as suggested, and never seen that
error anymore, so Andrew seems right. I'd like to thank him once again
and share this issue with other developers for solving/documenting/etc.


-- 
Fduch M. Pravking