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_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)
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, return its OID;
else return NULL.
The probe to see if 'foo' exists then becomes
select * from pg_class
where 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 likeselect * 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 toselect *
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
Import Notes
Resolved by subject fallback
"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
Import Notes
Resolved by subject fallback
