Quantcast

[GENERAL] Temporary table visibility

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[GENERAL] Temporary table visibility

James Croft

Hi all,

I've had a look at through the list archives but haven't found an  
answer to this one. Any suggestions appreciated (aside from ones  
suggesting that I should not need to do this ;-)...

- A normal table foo is created in a database.
- Clients connect to the database, some create a temp table foo some  
don't.

(only one postgresql user is being used to connect to the database if  
that matters)

How does a client determine if table foo is temporary or not?


Or put another way...

How can I determine what temporary tables exist in my session,  
bearing in mind that other sessions contain temp tables using the  
same names?


Many thanks,

James
--

This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.  
If you have received this email in error please notify the sender. Any
offers or quotation of service are subject to formal specification.  
Errors and omissions excepted.  Please note that any views or opinions
presented in this email are solely those of the author and do not
necessarily represent those of Lumison, nplusone or lightershade ltd.  
Finally, the recipient should check this email and any attachments for the
presence of viruses.  Lumison, nplusone and lightershade ltd accepts no
liability for any damage caused by any virus transmitted by this email.

--
--
Virus scanned by Lumison.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [GENERAL] Temporary table visibility

Jaime Casanova
> 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...

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [GENERAL] Temporary table visibility

James Croft

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



--

This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.  
If you have received this email in error please notify the sender. Any
offers or quotation of service are subject to formal specification.  
Errors and omissions excepted.  Please note that any views or opinions
presented in this email are solely those of the author and do not
necessarily represent those of Lumison, nplusone or lightershade ltd.  
Finally, the recipient should check this email and any attachments for the
presence of viruses.  Lumison, nplusone and lightershade ltd accepts no
liability for any damage caused by any virus transmitted by this email.

--
--
Virus scanned by Lumison.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [GENERAL] Temporary table visibility

Jaime Casanova
On 1/25/06, James Croft <[hidden email]> 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 ;)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [GENERAL] Temporary table visibility

Tom Lane-2
Jaime Casanova <[hidden email]> writes:
> On 1/25/06, James Croft <[hidden email]> wrote:
>> 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)?

> 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);

Close, but you really ought to escape the _ to avoid it being a LIKE
wildcard.  I'd tend to use a regex instead since _ isn't a wildcard
in regex patterns.  So the essential part of this is something like

        select relname
        from pg_catalog.pg_class c
             join pg_catalog.pg_namespace n on n.oid = c.relnamespace
        where nspname ~ '^pg_temp_'
             and pg_catalog.pg_table_is_visible(c.oid);

The test on the namespace name tells you it's temp (yes, this is a
legit way to do it, it's the same way the backend decides it's a
temp namespace) and the test on visibility is an easy way to see if
it's your temp namespace or someone else's.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [GENERAL] Temporary table visibility

Jaime Casanova
On 1/25/06, Tom Lane <[hidden email]> wrote:

> Jaime Casanova <[hidden email]> writes:
> > On 1/25/06, James Croft <[hidden email]> wrote:
> >> 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)?
>
> > 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);
>
> Close, but you really ought to escape the _ to avoid it being a LIKE
> wildcard.  I'd tend to use a regex instead since _ isn't a wildcard
> in regex patterns.  So the essential part of this is something like
>

jeje... need more coffee... and i really have to put my hands on that
regex book on the corner...

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [GENERAL] Temporary table visibility

Patrick TJ McPhee
In reply to this post by James Croft
In article <[hidden email]>,
James Croft <[hidden email]> wrote:

[given a bunch of temporary tables called session_data]

% 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)?

If there's data in the table, you could "select tableoid from session_data
limit 1", then check the namespace corresponding to that table.
--

Patrick TJ McPhee
North York  Canada
[hidden email]

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

Loading...