Thread: Discover temporary INDEX/TABLE name
Hello! How could I find out if a temporary table (or index on a temporary table) was created by current session? The problem is something like SELECT COUNT(*) FROM PG_INDEXES WHERE INDEXNAME='tmpind1' does not work since temporary indexes from other sessions are visible. I need a way to make a distinguish between temporary things belong to current session and others. I really do appreciate any help. -- Best regards Ilja Golshtein
Hello! >How could I find out if a temporary table >(or index on a temporary table) was created >by current session? May be the better question to ask is how one can find out the temporary schema name associated with the session. -- Best regards Ilja Golshtein
Ilja Golshtein skrev: > Hello! > > How could I find out if a temporary table > (or index on a temporary table) was created > by current session? > > The problem is something like > SELECT COUNT(*) FROM PG_INDEXES WHERE INDEXNAME='tmpind1' > does not work since temporary indexes from other sessions > are visible. I need a way to make a distinguish > between temporary things belong to current session > and others. > > > I really do appreciate any help. > Hi, Would it be terrible stupid of me to suggest you name the temporary things with f.ex pg_backend_pid() appended to the name? Or is the naming outside of your control? Best regards, Marcis
"Ilja Golshtein" <ilejn@yandex.ru> writes:
> Hello!
>
> >How could I find out if a temporary table
> >(or index on a temporary table) was created
> >by current session?
>
> May be the better question to ask is
> how one can find out the temporary
> schema name associated with the session.
select (current_schemas(true))[1];
In the typical case;
Will be pg_temp_* if you have created a temp object
or pg_catalog otherwise.
HTH
> --
> Best regards
> Ilja Golshtein
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) Production Database Administrator
305 321-1144 (mobil WWW E-Commerce Consultant
Also take a look at the queries that psql performs for \d (start psql with the -E option). On Nov 15, 2006, at 2:11 PM, Jerry Sievers wrote: > "Ilja Golshtein" <ilejn@yandex.ru> writes: > >> Hello! >> >>> How could I find out if a temporary table >>> (or index on a temporary table) was created >>> by current session? >> >> May be the better question to ask is >> how one can find out the temporary >> schema name associated with the session. > > select (current_schemas(true))[1]; > > In the typical case; > > Will be pg_temp_* if you have created a temp object > or pg_catalog otherwise. > > HTH > >> -- >> Best regards >> Ilja Golshtein >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 2: Don't 'kill -9' the postmaster >> > > -- > ---------------------------------------------------------------------- > --------- > Jerry Sievers 305 854-3001 (home) Production Database > Administrator > 305 321-1144 (mobil WWW E-Commerce Consultant > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that > your > message can get through to the mailing list cleanly > -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)