Re: [HACKERS] cache lookup failed in plpgsql - reason?

2012-03-21 Thread Robert Haas
On Wed, Oct 26, 2011 at 10:37 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
 Oct 25 20:13:44  db-s-01 postgres: local5.warning -- 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: local5.warning -- 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?

2011-10-26 Thread Pavel Stehule
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: local5.warning -- 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: local5.warning -- 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: local5.warning -- 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: local5.warning -- postgres[29970]:
[3-4]   PL/pgSQL function update_object_states line 2 at IF
Oct 25 20:13:44  db-s-01 postgres: local5.warning -- 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