Re: Reliably finding temporary table

Re: Reliably finding temporary table

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/

Re: Reliably finding temporary table

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