Thread: Eliminate information_schema from oid2name listing
This small patch eliminates relations in information_schema from oid2name
listing.
Index: oid2name.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/oid2name/oid2name.c,v
retrieving revision 1.18
diff -u -r1.18 oid2name.c
--- oid2name.c 14 May 2003 03:25:56 -0000 1.18
+++ oid2name.c 21 Jul 2003 03:49:57 -0000
@@ -355,7 +355,10 @@
if (systables == 1)
snprintf(todo, 1024, "select relfilenode,relname from pg_class order by relname");
else
- snprintf(todo, 1024, "select relfilenode,relname from pg_class where relname not like 'pg_%%' order by
relname");
+ snprintf(todo, 1024, "select relfilenode,relname "
+ "from pg_class c, pg_namespace n "
+ "where c.relnamespace = n.oid and n.nspname != 'information_schema' and c.relname not like 'pg_%%' "
+ "order by c.relname");
sql_exec(conn, todo, 0);
}
Kenji Sugita <sugita@srapc1327.sra.co.jp> writes:
> This small patch eliminates relations in information_schema from oid2name
> listing.
Seems like it'd be a good idea to eliminate views and composite types as
well.
regards, tom lane
From: Tom Lane <tgl@sss.pgh.pa.us> Subject: Re: [PATCHES] Eliminate information_schema from oid2name listing Date: Mon, 21 Jul 2003 00:32:46 -0400 ;;; Kenji Sugita <sugita@srapc1327.sra.co.jp> writes: ;;; > This small patch eliminates relations in information_schema from oid2name ;;; > listing. ;;; ;;; Seems like it'd be a good idea to eliminate views and composite types as ;;; well. It can be displayed by option -x. Information_schema displayed by "oid2name -d databasename" is noisy. Kenji Sugita
Kenji Sugita writes: > This small patch eliminates relations in information_schema from oid2name > listing. Why would one want to do that? -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Kenji Sugita writes:
>> This small patch eliminates relations in information_schema from oid2name
>> listing.
> Why would one want to do that?
AFAICS the point of oid2name is to provide a mapping between disk file
names and table names. As such, what it *ought* to be doing is
suppressing views, since those don't have disk files --- they are only
cluttering the listing with irrelevant data. If we put that in, there'd
be no need to special-case information_schema.
regards, tom lane
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Kenji Sugita wrote:
> This small patch eliminates relations in information_schema from oid2name
> listing.
> Index: oid2name.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/contrib/oid2name/oid2name.c,v
> retrieving revision 1.18
> diff -u -r1.18 oid2name.c
> --- oid2name.c 14 May 2003 03:25:56 -0000 1.18
> +++ oid2name.c 21 Jul 2003 03:49:57 -0000
> @@ -355,7 +355,10 @@
> if (systables == 1)
> snprintf(todo, 1024, "select relfilenode,relname from pg_class order by relname");
> else
> - snprintf(todo, 1024, "select relfilenode,relname from pg_class where relname not like 'pg_%%' order by
relname");
> + snprintf(todo, 1024, "select relfilenode,relname "
> + "from pg_class c, pg_namespace n "
> + "where c.relnamespace = n.oid and n.nspname != 'information_schema' and c.relname not like 'pg_%%'
"
> + "order by c.relname");
>
> sql_exec(conn, todo, 0);
> }
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Sorry, patch removed from patch queue. I will rework the patch to skip views completely, OK? --------------------------------------------------------------------------- Kenji Sugita wrote: > This small patch eliminates relations in information_schema from oid2name > listing. > Index: oid2name.c > =================================================================== > RCS file: /projects/cvsroot/pgsql-server/contrib/oid2name/oid2name.c,v > retrieving revision 1.18 > diff -u -r1.18 oid2name.c > --- oid2name.c 14 May 2003 03:25:56 -0000 1.18 > +++ oid2name.c 21 Jul 2003 03:49:57 -0000 > @@ -355,7 +355,10 @@ > if (systables == 1) > snprintf(todo, 1024, "select relfilenode,relname from pg_class order by relname"); > else > - snprintf(todo, 1024, "select relfilenode,relname from pg_class where relname not like 'pg_%%' order by relname"); > + snprintf(todo, 1024, "select relfilenode,relname " > + "from pg_class c, pg_namespace n " > + "where c.relnamespace = n.oid and n.nspname != 'information_schema' and c.relname not like 'pg_%%' " > + "order by c.relname"); > > sql_exec(conn, todo, 0); > } > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch attached and applied. --------------------------------------------------------------------------- Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Kenji Sugita writes: > >> This small patch eliminates relations in information_schema from oid2name > >> listing. > > > Why would one want to do that? > > AFAICS the point of oid2name is to provide a mapping between disk file > names and table names. As such, what it *ought* to be doing is > suppressing views, since those don't have disk files --- they are only > cluttering the listing with irrelevant data. If we put that in, there'd > be no need to special-case information_schema. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: contrib/oid2name/oid2name.c =================================================================== RCS file: /cvsroot/pgsql-server/contrib/oid2name/oid2name.c,v retrieving revision 1.18 diff -c -c -r1.18 oid2name.c *** contrib/oid2name/oid2name.c 14 May 2003 03:25:56 -0000 1.18 --- contrib/oid2name/oid2name.c 27 Jul 2003 04:42:11 -0000 *************** *** 355,361 **** if (systables == 1) snprintf(todo, 1024, "select relfilenode,relname from pg_class order by relname"); else ! snprintf(todo, 1024, "select relfilenode,relname from pg_class where relname not like 'pg_%%' order by relname"); sql_exec(conn, todo, 0); } --- 355,363 ---- if (systables == 1) snprintf(todo, 1024, "select relfilenode,relname from pg_class order by relname"); else ! snprintf(todo, 1024, "select relfilenode,relname from pg_class " ! "where reltype not in ('v','c') and " ! "relname not like 'pg_%%' order by relname"); sql_exec(conn, todo, 0); }
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> ! "where reltype not in ('v','c') and "
Surely you meant relkind. Also, there is no 'c' relkind; perhaps you
meant 's'? I think v,s,t are all relkinds to exclude here.
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > ! "where reltype not in ('v','c') and "
>
> Surely you meant relkind. Also, there is no 'c' relkind; perhaps you
> meant 's'? I think v,s,t are all relkinds to exclude here.
Yes, sorry, relkind. New attached patch applied with your suggested
relkind list.
I got my list of entries from pg_class.h:
#define RELKIND_INDEX 'i' /* secondary index */
#define RELKIND_RELATION 'r' /* ordinary cataloged heap */
#define RELKIND_SPECIAL 's' /* special (non-heap) */
#define RELKIND_SEQUENCE 'S' /* SEQUENCE relation */
#define RELKIND_UNCATALOGED 'u' /* temporary heap */
#define RELKIND_TOASTVALUE 't' /* moved off huge values */
#define RELKIND_VIEW 'v' /* view */
#define RELKIND_COMPOSITE_TYPE 'c' /* composite type */
Is 't' for toast tables? If so, we should allow 't', no? I wasn't sure
about 's'? Is there a disk file associated with it that oid2name should
diplay?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Index: contrib/oid2name/oid2name.c
===================================================================
RCS file: /cvsroot/pgsql-server/contrib/oid2name/oid2name.c,v
retrieving revision 1.19
diff -c -c -r1.19 oid2name.c
*** contrib/oid2name/oid2name.c 27 Jul 2003 04:51:45 -0000 1.19
--- contrib/oid2name/oid2name.c 27 Jul 2003 19:29:28 -0000
***************
*** 356,362 ****
snprintf(todo, 1024, "select relfilenode,relname from pg_class order by relname");
else
snprintf(todo, 1024, "select relfilenode,relname from pg_class "
! "where reltype not in ('v','c') and "
"relname not like 'pg_%%' order by relname");
sql_exec(conn, todo, 0);
--- 356,362 ----
snprintf(todo, 1024, "select relfilenode,relname from pg_class order by relname");
else
snprintf(todo, 1024, "select relfilenode,relname from pg_class "
! "where relkind not in ('v','s', 't') and "
"relname not like 'pg_%%' order by relname");
sql_exec(conn, todo, 0);
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> I think v,s,t are all relkinds to exclude here.
> Is 't' for toast tables? If so, we should allow 't', no? I wasn't sure
> about 's'?
Wups, you are right --- I was thinking 't' meant 'composite type'.
The only 's' in the system is pg_xactlock which does not have a disk
file.
The correct set to exclude seems to be 'v','s','c'.
regards, tom lane
OK, change made and applied. --------------------------------------------------------------------------- Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> I think v,s,t are all relkinds to exclude here. > > > Is 't' for toast tables? If so, we should allow 't', no? I wasn't sure > > about 's'? > > Wups, you are right --- I was thinking 't' meant 'composite type'. > > The only 's' in the system is pg_xactlock which does not have a disk > file. > > The correct set to exclude seems to be 'v','s','c'. > > regards, tom lane > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073