Skip site navigation (1) Skip section navigation (2)

Re: strange error with temp table: pg_type_typname_nsp_index

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Janning Vygen <vygen(at)planwerk6(dot)de>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: strange error with temp table: pg_type_typname_nsp_index
Date: 2005-07-14 17:18:44
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Janning Vygen <vygen(at)planwerk6(dot)de> writes:
>    PERFORM n.nspname ,c.relname
>     FROM
>       pg_catalog.pg_class c
>       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
>     WHERE
>       n.nspname like 'pg_temp_%'
>       AND pg_catalog.pg_table_is_visible(c.oid)
>       AND Upper(relname) = 'TEMP_GC'
>     ;

> but as i looked at the system catalogs pg_temp it is like that every session 
> can see the temporary tables of any other session. so the whole story about 
> the query above is wrong. It checks if ANY session has a temporrary table 
> gc_temp and not my own session.

No, not at all: the pg_table_is_visible check will fail on temp tables
of other sessions.

I think the real problem here is a race condition: pg_table_is_visible
will give the "cache lookup failed" error if the OID is for a table that
no longer exists, which means you could have a problem when the select
picks up a pg_class row for another session's temp table just before the
other session drops the temp table.  (The window for this is wider than
it might seem, because pg_table_is_visible operates under SnapshotNow
rules instead of MVCC.)  We've gone back and forth about whether it'd be
better for pg_table_is_visible to silently return FALSE if the OID is
not a valid table OID, but that doesn't seem real attractive from an
error-detection perspective.

In any case I don't think this has anything to do with your original
report about a duplicate key error.  If you can reproduce that one
again, let us know.

			regards, tom lane

In response to

pgsql-general by date

Next:From: Russ BrownDate: 2005-07-14 17:36:38
Subject: Re: Quotation marks in queries
Previous:From: Thomas F. O'ConnellDate: 2005-07-14 16:59:39
Subject: Re: ERROR: could not open relation

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group