Re: [HACKERS] cache lookup failed in plpgsql - reason?
On Wed, Oct 26, 2011 at 10:37 AM, Pavel Stehule wrote: > Oct 25 20:13:44 db-s-01 postgres: -- postgres[29970]: > [3-1] 2011-10-25 20:13:44 CEST adifd 29970 ERROR: cache lookup failed > for relation 319883311 > Oct 25 20:13:44 db-s-01 postgres: -- postgres[29970]: > [3-2] 2011-10-25 20:13:44 CEST adifd 29970 CONTEXT: SQL statement > "SELECT NOT EXISTS( SELECT relname FROM pg_class WHERE relname = This appears to be a race condition in pg_table_is_visible. It checks whether the table exists; if it does not, it returns NULL. Having verified that the relation exists, it then calls RelationIsVisible(), which then does a new syscache lookup for the same tuple, throwing an error if none is found. But there's no guarantee that things can't change between the first test and the second one, so you get this error. Perhaps we could make RelationInVisible return a three-valued enum type, rather than bool. VISIBLE_YES, VISIBLE_NO, and VISIBLE_NOTFOUND, or something like that. I wouldn't want to back-patch such a fix, since there could be third-party code calling RelationIsVisible, but we could do it in master. Sadly it's not a trivial patch, since there are a gazillion WhateverIsVisible() functions and we'd have to fix them all, but at least it's mostly mechanical. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] cache lookup failed in plpgsql - reason?
Hello one my customer reported a random issue. He uses a procedure with following fragment IF NOT EXISTS( SELECT relname FROM pg_class WHERE relname = 'tmp_object_state_change' AND relkind = 'r' AND pg_table_is_visible(oid) ) THEN CREATE TEMPORARY TABLE tmp_object_state_change ( object_id INTEGER, object_hid INTEGER, new_states INTEGER[], old_states INTEGER[] ); ELSE TRUNCATE tmp_object_state_change; END IF; These lines sometimes raise a error Oct 25 20:13:44 db-s-01 postgres: -- postgres[29970]: [3-1] 2011-10-25 20:13:44 CEST adifd 29970 ERROR: cache lookup failed for relation 319883311 Oct 25 20:13:44 db-s-01 postgres: -- postgres[29970]: [3-2] 2011-10-25 20:13:44 CEST adifd 29970 CONTEXT: SQL statement "SELECT NOT EXISTS( SELECT relname FROM pg_class WHERE relname = Oct 25 20:13:44 db-s-01 postgres: -- postgres[29970]: [3-3] 'tmp_object_state_change' AND relkind = 'r' AND pg_table_is_visible(oid) )" Oct 25 20:13:44 db-s-01 postgres: -- postgres[29970]: [3-4] PL/pgSQL function "update_object_states" line 2 at IF Oct 25 20:13:44 db-s-01 postgres: -- postgres[29970]: [3-5] 2011-10-25 20:13:44 CEST adifd 29970 STATEMENT: SELECT update_object_states($1::integer) I don't see a reason why on this query cache should be broken, He uses Pg 8.3.15. Any idea? Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
