Обсуждение: Reliably finding temporary table
We have some functions which need to dynamically create a temporary table if it does not already exist. We use the following function: CREATE OR REPLACE FUNCTION test_date_time_exists() RETURNS BOOLEAN AS ' BEGIN RETURN EXISTS ( SELECT * FROM pg_class WHERE relname = ''test_date_time'' AND pg_table_is_visible(oid) ); END; ' LANGUAGE 'plpgsql' STABLE However, this has been failing occassionally with errors like ERROR: cache lookup failed for relation 3454264547 From looking in the list archives, I found a description of pg_table_is_visible failing because it has different snapshot semantics than the SELECT. Is there a solution for this problem? Is there another function I can use? What is a better way to detect temp tables visible to the session? - Ian
Help! I have a similar problem. Does anybody know how to solve a problem
--
Verba volent, scripta manent
My ISP - http://www.netbynet.ru
2005/6/8, Ian Burrell <ianburrell@gmail.com>:
We have some functions which need to dynamically create a temporary
table if it does not already exist. We use the following function:
CREATE OR REPLACE FUNCTION test_date_time_exists() RETURNS BOOLEAN AS '
BEGIN
RETURN EXISTS (
SELECT * FROM pg_class
WHERE relname = ''test_date_time''
AND pg_table_is_visible(oid)
);
END;
' LANGUAGE 'plpgsql' STABLE
However, this has been failing occassionally with errors like
ERROR: cache lookup failed for relation 3454264547
From looking in the list archives, I found a description of
pg_table_is_visible failing because it has different snapshot
semantics than the SELECT. Is there a solution for this problem? Is
there another function I can use? What is a better way to detect temp
tables visible to the session?
- Ian
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--
Verba volent, scripta manent
My ISP - http://www.netbynet.ru
On Tue, Jun 07, 2005 at 04:01:02PM -0700, Ian Burrell wrote: > > CREATE OR REPLACE FUNCTION test_date_time_exists() RETURNS BOOLEAN AS ' > BEGIN > RETURN EXISTS ( > SELECT * FROM pg_class > WHERE relname = ''test_date_time'' > AND pg_table_is_visible(oid) > ); > END; > ' LANGUAGE 'plpgsql' STABLE > > However, this has been failing occassionally with errors like > > ERROR: cache lookup failed for relation 3454264547 The following message suggests using CASE and has_schema_privilege() along with pg_table_is_visible(): http://archives.postgresql.org/pgsql-hackers/2005-06/msg00319.php If you're using PostgreSQL 8.0 then I'd expect an exception handler to work: CREATE FUNCTION create_test_date_time() RETURNS void AS $$ BEGIN BEGIN CREATE TEMPORARY TABLE test_date_time (...); EXCEPTION WHEN DUPLICATE_TABLE THEN NULL; END; RETURN; END; $$ LANGUAGE plpgsql VOLATILE; -- Michael Fuhr http://www.fuhr.org/~mfuhr/