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 INTO ss_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's still 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 backends will 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 that don#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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match