Reliably finding temporary table

From: Ian Burrell <ianburrell(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Reliably finding temporary table
Date: 2005-06-07 23:01:02
Message-ID: d91f09cd050607160138aa384f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Treat 2005-06-07 23:58:05 Re: postgresql books
Previous Message Chris Browne 2005-06-07 22:29:29 Re: pl/pgsql list