Search Postgresql Archives

Re: Temporary table visibility

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 1/25/06, James Croft <james.croft@xxxxxxxxxxx> wrote:
>
> On 25 Jan 2006, at 14:17, Jaime Casanova wrote:
>
> >> How can I determine what temporary tables exist in my session,
> >> bearing in mind that other sessions contain temp tables using the
> >> same names?
> >>
> >
> > just the ones you have created in your session, temporary tables in
> > other sessions are invisible to you...
>
>
> Thanks Jaime but that's not really what I meant.
>
> I know that if a session creates a temporary table it is only visible
> to that session. I'm not doing a good job of explaining this but
> basically given the following results...
>
> test=> select relname, relnamespace, reltype from pg_class where
> relname = 'session_data';
>    relname    | relnamespace | reltype
> --------------+--------------+----------
> session_data |         2200 | 16114367
> session_data |     16120903 | 16314010
> session_data |     16120709 | 16314030
> session_data |     16122659 | 16314133
> session_data |     16123201 | 16314285
> session_data |     16124398 | 16315049
> session_data |        16767 | 16315527
> session_data |     16120382 | 16315818
> session_data |     16125558 | 16315816
> session_data |     16114413 | 16316810
> session_data |     16127654 | 16317471
> session_data |     16114683 | 16317551
> session_data |     16118447 | 16317563
> session_data |     15035529 | 16317579
> (14 rows)
>
> How can I determine if one of the above relations is a temporary
> table in the current session (one of them, the first in ns 2200, is a
> normal permanent table)?
>
>
> Thanks,
> James
>
>

SELECT n.nspname as "Schema", c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'índex' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
  r.rolname as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
      AND n.nspname LIKE 'pg_temp%'
      AND pg_catalog.pg_table_is_visible(c.oid);

Maybe this is what you want?

FWIW, this was make just with psql -E (to view what query \d executes
and changing the "AND n.nspname NOT IN " line for something more
apropiate...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux