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
