Postgres Pro
Downloads
Home   >   mailing lists

Thread: how do I check if a temporary table exists?

how do I check if a temporary table exists?

From
"datactrl"
Date:
How do I check if a temporary table exists? Searching pg_tables with a
temporary table name we chose always fails.

JACK

Re: how do I check if a temporary table exists?

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
Thus spake datactrl
> How do I check if a temporary table exists? Searching pg_tables with a
> temporary table name we chose always fails.

What do you mean?  You know that it exists because you just created it
and the create succeeded.  Can you describe a situation where you need
to know about a temp table and don't already know?

It can't go into the pg_tables table because then it would be visible
to other processes.  Are you perhaps misunderstanding temp tables?  You
cannot see them except from the process that created them.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Re: how do I check if a temporary table exists?

From
Tom Lane
Date:
darcy@druid.net (D'Arcy J.M. Cain) writes:
> Thus spake datactrl
>> How do I check if a temporary table exists? Searching pg_tables with a
>> temporary table name we chose always fails.

> What do you mean?  You know that it exists because you just created it
> and the create succeeded.  Can you describe a situation where you need
> to know about a temp table and don't already know?

I can see that it might take some unpleasant restructuring of his client
application to make that knowledge available where & when it's needed.

> It can't go into the pg_tables table because then it would be visible
> to other processes.  Are you perhaps misunderstanding temp tables?  You
> cannot see them except from the process that created them.

I agree with datactrl that this is a deficiency: the whole point of
system catalogs is that you are supposed to be able to look in them
to see what you have.  It should be possible to look up temp tables
in pg_class.  They actually are in there (and quite visible to other
processes BTW); what you don't know is the mapping from your logical
name for the table to its real name pg_tempXXXX.

I'm hoping that this can be fixed when we implement schemas.  Seems
like it would be natural to make temp tables live in a per-backend
schema.  Then they wouldn't need names like pg_tempXXXX.  You would
probably need to know the name of your current temp schema, but that
seems like a more tractable thing to deal with than mappings of
individual temp table names.
        regards, tom lane

Re: how do I check if a temporary table exists?

From
Bruce Momjian
Date:
Multiple backends can create temp tables with the same name and each
person will see their own version.


> Thus spake datactrl
> > How do I check if a temporary table exists? Searching pg_tables with a
> > temporary table name we chose always fails.
> 
> What do you mean?  You know that it exists because you just created it
> and the create succeeded.  Can you describe a situation where you need
> to know about a temp table and don't already know?
> 
> It can't go into the pg_tables table because then it would be visible
> to other processes.  Are you perhaps misunderstanding temp tables?  You
> cannot see them except from the process that created them.
> 
> -- 
> D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
> http://www.druid.net/darcy/                |  and a sheep voting on
> +1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026