Thread: Simplifying identification of temporary tables

Currently, the recommended way to ask "have I already created a temp
table named foo" is something like
select * from pg_classwhere relname = 'foo' and pg_table_is_visible(oid);

If there's a possibility that a regular table named 'foo' exists,
then this isn't good enough and you have to resort to
select *from pg_class c join pg_namespace n on n.oid = c.relnamespacewhere relname = 'foo' and nspname like 'pg_temp_%'
andpg_table_is_visible(c.oid)

which is truly ugly, and pretty inefficient as well.  And both of
these cases have a race condition if multiple sessions might be
creating and dropping temp tables named 'foo': pg_table_is_visible()
might fail because the table is one that someone else dropped just
before control got to the function.

It occurs to me that a much better solution is possible if we create
a function defined along the following lines:
pg_my_temp_namespace() returns oid    If a temporary table namespace has been established    for the current session,
returnits OID;    else return NULL.
 

The probe to see if 'foo' exists then becomes
select * from pg_classwhere relname = 'foo' and relnamespace = pg_my_temp_namespace();

No join, no race condition, and a fully indexable WHERE clause.

You can sort of do this now at the SQL level by inspecting the result of
current_schemas(true), but it's fairly tedious to write such a function.
As a C function it'd be a one-liner.

Seems worthwhile to me --- any objections?  Any better ideas about a
name?
        regards, tom lane

Tom Lane wrote:
> Currently, the recommended way to ask "have I already created a temp
> table named foo" is something like
>
>     select * from pg_class
>     where relname = 'foo' and pg_table_is_visible(oid);
>
> If there's a possibility that a regular table named 'foo' exists,
> then this isn't good enough and you have to resort to
>
>     select *
>     from pg_class c join pg_namespace n on n.oid = c.relnamespace
>     where relname = 'foo' and nspname like 'pg_temp_%' and
>     pg_table_is_visible(c.oid)

Well now that we have savepoints you have another approach.  In
non-dynamic pl/pgsql functions my preferred method is to probe the table
via normal sql and recreate it on the appropriate exception.

That said, I think what you are proposing is good since it causes less
log pollution.  Although I would prefer to return the name of the
namespace, not the oid, or just go right to the point and create
function accepting temp table name and returning bool.  That way the oid
is abstracted into the function.

Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> That said, I think what you are proposing is good since it causes less
> log pollution.  Although I would prefer to return the name of the
> namespace, not the oid,

I thought about that, but it pushes you right back to having to do a
join with pg_namespace, which certainly doesn't increase your level of
abstraction from the system catalogs.

> or just go right to the point and create
> function accepting temp table name and returning bool.

That would respond to the abstraction concern in a more complete
fashion.  I thought about that one too, but felt that the ability to
look at the whole pg_class row (and not only check existence) had some
value.  Also, I've seen people doing things like... where relname LIKE 'pattern' and ...
which still works with the namespace OID function but would not work
with a does_temp_table_exist() function.  I wouldn't object to doing
both this and the OID function, though.  Do you have a proposal for
the exact spelling of the exists() function?
        regards, tom lane

> That would respond to the abstraction concern in a more complete
> fashion.  I thought about that one too, but felt that the ability to
> look at the whole pg_class row (and not only check existence) had some
> value.  Also, I've seen people doing things like
>     ... where relname LIKE 'pattern' and ...
> which still works with the namespace OID function but would not work
> with a does_temp_table_exist() function.  I wouldn't object to doing
> both this and the OID function, though.  Do you have a proposal for
> the exact spelling of the exists() function?

I guess pg_temp_table_exists(text) is the most straightforward.

Merlin

> Seems worthwhile to me --- any objections?  Any better ideas about a
> name?

pg_session_temp_namespace()