Visibility issue with pg_table_is_visible - Mailing list pgsql-hackers
From | Alexander M. Pravking |
---|---|
Subject | Visibility issue with pg_table_is_visible |
Date | |
Msg-id | 20050607230131.GI990@dyatel.antar.bryansk.ru Whole thread Raw |
List | pgsql-hackers |
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
pgsql-hackers by date: