Re: Temp table exists test??

>On Thu, Feb 03, 2005 at 09:39:08PM -0500, Michael Guerin wrote:
>
>
>>I'm trying to detect the existence of a temp table in a function for
>>that connection.
>>
>>
>
>Why do you need to know this?  So you don't try to create a temporary
>table multiple times?
>
>
It's in reference to a post the other day "Function to blame?"  I'm
running into an issue that causes the database to get corrupted  under a
heavy load.    Everytime it get corrupted, it's always in this function
that creates a temp table, fills it, sends back the results and drops
the table.  This one function is heavily used.

That said, we know that every thread has it's own connection.   So, I
would like to modify the function to create the temp table the first
time its used, and truncate it every other time reducing the number of
entries in the pg_class, pg_type,... tables that we experienced
corruption in.  This is why I need to know if the connection created the
temp table.

>>Connection 1:
>>Create table foo (i int);
>>
>>Connection 2:
>>select * from pg_class where relname = 'foo'
>>
>>returns the table from connection 1, however I need to know if there's a
>>temp table foo for connection 2 not connection 1.  Each row in pg_class
>>is associated with a namespace, so I'm looking for something like:
>>
>>select * from pg_class where relname = 'foo' and relnamespace = ???
>>
>>
>
>If you don't care whether the table is temporary or not then you
>could simply test if it's visible:
>
>SELECT *
>FROM pg_class
>WHERE relname = 'foo'
>  AND relkind = 'r'
>  AND pg_table_is_visible(oid);
>
>If you want to limit the query to temporary tables, then you could
>join pg_class with pg_namespace and look for temporary schema names:
>
>SELECT n.nspname, c.relname
>FROM pg_class AS c
>JOIN pg_namespace AS n ON n.oid = c.relnamespace
>WHERE c.relname = 'foo'
>  AND c.relkind = 'r'
>  AND n.nspname LIKE 'pg_temp_%'
>  AND pg_table_is_visible(c.oid);
>
>Is that what you're looking for?
>
>
>
select * from pg_class with relname = 'foo' and
pg_table_is_visible(oid)  doesn't seem to work.  I just created a table,
opened another connection and ran this query and it came back with the
new table I just created.  I'll will try you other example as well as Tom's.

Thanks
Michael