Eliminate information_schema from oid2name listing

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

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

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