Re: [GENERAL] Temporary table visibility
In article [EMAIL PROTECTED], James Croft [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(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
Re: [GENERAL] Temporary table visibility
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
Re: [GENERAL] Temporary table visibility
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
Re: [GENERAL] Temporary table visibility
On 1/25/06, James Croft [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Temporary table visibility
Jaime Casanova [EMAIL PROTECTED] writes: On 1/25/06, James Croft [EMAIL PROTECTED] 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
Re: [GENERAL] Temporary table visibility
On 1/25/06, Tom Lane [EMAIL PROTECTED] wrote: Jaime Casanova [EMAIL PROTECTED] writes: On 1/25/06, James Croft [EMAIL PROTECTED] 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