Обсуждение: Temp table exists test??
Hi All,
Is there a way to find out the namespace id (relnamespace) for your
connection? I grabbed this function from one of the list, however it
doesn't work for temp tables when multiple connections are involved b/c
it will always return true.
Thanks
Michael
-------------------
CREATE OR REPLACE FUNCTION table_exists("varchar")
RETURNS bool AS
$BODY$
DECLARE
t_name ALIAS for $1;
t_result VARCHAR;
BEGIN
--find table, case-insensitive
SELECT relname INTO t_result
FROM pg_class
WHERE relname ~* ('^' || t_name || '$')
AND relkind = 'r';
IF t_result IS NULL THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
On Thu, Feb 03, 2005 at 08:16:30PM -0500, Michael Guerin wrote: > > Is there a way to find out the namespace id (relnamespace) for your > connection? It's not clear what you're trying to do. Are you looking for current_schema()? Or maybe one of the other system information functions? http://www.postgresql.org/docs/8.0/static/functions-info.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/
I'm trying to detect the existence of a temp table in a function for that connection. So, 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 = ??? -Michael Michael Fuhr wrote: >On Thu, Feb 03, 2005 at 08:16:30PM -0500, Michael Guerin wrote: > > >> Is there a way to find out the namespace id (relnamespace) for your >>connection? >> >> > >It's not clear what you're trying to do. Are you looking for >current_schema()? Or maybe one of the other system information >functions? > >http://www.postgresql.org/docs/8.0/static/functions-info.html > > >
Michael Guerin <guerin@rentec.com> writes:
> I'm trying to detect the existence of a temp table in a function for
> that connection.
Perhaps something like
select * from pg_class where relname = 'foo' and pg_table_is_visible(oid);
However this will potentially trigger on non-temp tables, if you have
any of matching names that are in your search path. Dunno if that's a
problem for your application.
Another way is to look at current_schemas(), though you would need to
check whether the first entry is a temp schema or not (it isn't until
you've created at least one temp table):
regression=# select current_schemas(true);
current_schemas
---------------------
{pg_catalog,public}
(1 row)
regression=# create temp table foo(f1 int);
CREATE TABLE
regression=# select current_schemas(true);
current_schemas
-------------------------------
{pg_temp_1,pg_catalog,public}
(1 row)
regression=# select (current_schemas(true))[1];
current_schemas
-----------------
pg_temp_1
(1 row)
regression=#
regards, tom lane
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? > 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? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
>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
>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); > > > Ignore my last post that pg_table_is_visible didn't work, I created the table but forgot the keyword temp :( .. Guess its time for me to goto bed. This is exactly what I'm looking for. Thanks Michael
> 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. 1) You could query the system tables. 2) You could attempt to query the temp table -- and if an error message occurs, assume the table does not exist. 3) You could issue the create command and ignore the "table already exists" error. Then follow it up with the truncate command. #2/#3 would be problematic if you commonly wrapped these commands inside a transaction becaues the errors would kick your transaction out.