Re: [PATCHES] Show tablespace name in pg_tables and pg_indexes
Patch applied and catalog version updated. We now have: test= select * from pg_tables; schemaname |tablename| tablespace | tableowner | hasindexes | hasrules | hastriggers +-++++--+- information_schema | sql_packages|| postgres | f | f| f information_schema | sql_sizing || postgres | f | f| f information_schema | sql_sizing_profiles || postgres | f | f| f information_schema | sql_features|| postgres | f | f| f information_schema | sql_implementation_info || postgres | f | f| f information_schema | sql_languages || postgres | f | f| f pg_catalog | pg_shadow | pg_global | postgres | t | f| t pg_catalog | pg_opclass || postgres | t | f| f These columns are individually selectable so if people don't want the column they can just skip it. --- Klaus Naumann wrote: On Mon, 12 Jul 2004, Klaus Naumann wrote: Hi, sorry, the last patch is buggy which didn't show up in the tests :( Two LEFTs were missing - new patch is attached. Greetings, Klaus Hi, the attached patch shows the new column tablespace in the mentioned views. Apply with ~/pgsql$ patch -p1 03_showtblspc.diff Greetings, Klaus -- Full Name : Klaus Naumann | (http://www.mgnet.de/) (Germany) Phone / FAX : ++49/177/7862964 | E-Mail: ([EMAIL PROTECTED]) Content-Description: [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: 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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Show tablespace name in pg_tables and pg_indexes
On Mon, 12 Jul 2004, Klaus Naumann wrote: Hi, I've sent this patch a while ago - was it applied? Is something not ok with it? Don't want to bug anyone - just would like to have some feedback. Grettings, Klaus On Mon, 12 Jul 2004, Klaus Naumann wrote: Hi, sorry, the last patch is buggy which didn't show up in the tests :( Two LEFTs were missing - new patch is attached. Greetings, Klaus Hi, the attached patch shows the new column tablespace in the mentioned views. Apply with ~/pgsql$ patch -p1 03_showtblspc.diff Greetings, Klaus -- Full Name : Klaus Naumann | (http://www.mgnet.de/) (Germany) Phone / FAX : ++49/177/7862964 | E-Mail: ([EMAIL PROTECTED]) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Show tablespace name in pg_tables and pg_indexes
On Mon, 12 Jul 2004, Klaus Naumann wrote: Hi, sorry, the last patch is buggy which didn't show up in the tests :( Two LEFTs were missing - new patch is attached. Greetings, Klaus Hi, the attached patch shows the new column tablespace in the mentioned views. Apply with ~/pgsql$ patch -p1 03_showtblspc.diff Greetings, Klaus -- Full Name : Klaus Naumann | (http://www.mgnet.de/) (Germany) Phone / FAX : ++49/177/7862964 | E-Mail: ([EMAIL PROTECTED])diff -Ncr pgsql-pitr.orig/src/backend/catalog/system_views.sql pgsql-pitr/src/backend/catalog/system_views.sql *** pgsql-pitr.orig/src/backend/catalog/system_views.sqlMon Jul 12 14:20:02 2004 --- pgsql-pitr/src/backend/catalog/system_views.sql Mon Jul 12 14:59:44 2004 *** *** 41,62 --- 41,66 SELECT N.nspname AS schemaname, C.relname AS tablename, +T.spcname AS tablespace, pg_get_userbyid(C.relowner) AS tableowner, C.relhasindex AS hasindexes, C.relhasrules AS hasrules, (C.reltriggers 0) AS hastriggers FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) + LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace) WHERE C.relkind = 'r'; CREATE VIEW pg_indexes AS SELECT N.nspname AS schemaname, C.relname AS tablename, + T.spcname AS tablespace, I.relname AS indexname, pg_get_indexdef(I.oid) AS indexdef FROM pg_index X JOIN pg_class C ON (C.oid = X.indrelid) JOIN pg_class I ON (I.oid = X.indexrelid) LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) + LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace) WHERE C.relkind = 'r' AND I.relkind = 'i'; CREATE VIEW pg_stats AS ---(end of broadcast)--- TIP 3: 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