I have made most system indexes unique.

I have added indexes to match all system caches.

I have renamed some cache names to be clearer.

I have re-ordered the cache entries to be alphabetical.

I have renamed the inheritance *rel columns to be *relid.

I have added a large comment to syscache.c listing steps needed to
install a new system index.

I saw no speed improvement from my changes, but I can imagine cases
where this would be a speedup.

The only thing missing is that I can't seem to get pg_shadow to use an
index from the cache.  When I try it, initdb runs really slow, and the
resulting installation is unusable.  Any ideas anyone?  You can see my
commented-out code in indexing.c and syscache.c.  My guess is that the
strange way we issue pg_exec_query_dest() calls is the cause.  I have no
call to CatalogIndexInsert() for the pg_shadow because of this.  Anyone
want to rewrite user.c to use heap_insert() instead.

initdb required.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 22 Nov 1999 12:59:25 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
System cache index cleanup
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I have made most system indexes unique.

Bruce, I think you need to revert the following changes to pg_opclass.h:

*** src/include/catalog/pg_opclass.h	1999/09/29 21:13:30	1.20
--- src/include/catalog/pg_opclass.h	1999/11/22 17:36:15
***************
*** 68,76 ****
  DESCR("");
  DATA(insert OID =  423 (	float8_ops		701   ));
  DESCR("");
! DATA(insert OID =  424 (	int24_ops		  0   ));
  DESCR("");
! DATA(insert OID =  425 (	int42_ops		  0   ));
  DESCR("");
  DATA(insert OID =  426 (	int4_ops		 23   ));
  DESCR("");
--- 68,78 ----
  DESCR("");
  DATA(insert OID =  423 (	float8_ops		701   ));
  DESCR("");
! /* Technically, deftype is wrong, but it must be unique for index, bjm */
! DATA(insert OID =  424 (	int24_ops		424   ));
  DESCR("");
! /* Technically, deftype is wrong, but it must be unique for index, bjm */
! DATA(insert OID =  425 (	int42_ops		425   ));
  DESCR("");
  DATA(insert OID =  426 (	int4_ops		 23   ));
  DESCR("");
***************
*** 85,91 ****
  DESCR("");
  DATA(insert OID =  432 (	abstime_ops		702   ));
  DESCR("");
! DATA(insert OID =  433 (	bigbox_ops		603   ));
  DESCR("");
  DATA(insert OID =  434 (	poly_ops		604   ));
  DESCR("");
--- 87,94 ----
  DESCR("");
  DATA(insert OID =  432 (	abstime_ops		702   ));
  DESCR("");
! /* Technically, deftype is wrong, but it must be unique for index, bjm */
! DATA(insert OID =  433 (	bigbox_ops		433   ));
  DESCR("");
  DATA(insert OID =  434 (	poly_ops		604   ));
  DESCR("");

and make the corresponding index non-unique.

(a) It is not supposed to be a unique column --- we'd not need the
concept of index opclasses at all if there were only one possible
operator set for any given column type!

(b) The above changes are making the oidjoins and opr_sanity regress
tests fail, as indeed they should...

			regards, tom lane

Mon, 22 Nov 1999 23:35:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] System cache index cleanup 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I have made most system indexes unique.
> 
> Bruce, I think you need to revert the following changes to pg_opclass.h:
> 
> *** src/include/catalog/pg_opclass.h	1999/09/29 21:13:30	1.20
> --- src/include/catalog/pg_opclass.h	1999/11/22 17:36:15
> ***************
> *** 68,76 ****
>   DESCR("");
>   DATA(insert OID =  423 (	float8_ops		701   ));
>   DESCR("");
> ! DATA(insert OID =  424 (	int24_ops		  0   ));
>   DESCR("");
> ! DATA(insert OID =  425 (	int42_ops		  0   ));
>   DESCR("");
>   DATA(insert OID =  426 (	int4_ops		 23   ));
>   DESCR("");
> --- 68,78 ----
>   DESCR("");
>   DATA(insert OID =  423 (	float8_ops		701   ));
>   DESCR("");
> ! /* Technically, deftype is wrong, but it must be unique for index, bjm */
> ! DATA(insert OID =  424 (	int24_ops		424   ));
>   DESCR("");
> ! /* Technically, deftype is wrong, but it must be unique for index, bjm */
> ! DATA(insert OID =  425 (	int42_ops		425   ));
>   DESCR("");
>   DATA(insert OID =  426 (	int4_ops		 23   ));
>   DESCR("");
> ***************
> *** 85,91 ****
>   DESCR("");
>   DATA(insert OID =  432 (	abstime_ops		702   ));
>   DESCR("");
> ! DATA(insert OID =  433 (	bigbox_ops		603   ));
>   DESCR("");
>   DATA(insert OID =  434 (	poly_ops		604   ));
>   DESCR("");
> --- 87,94 ----
>   DESCR("");
>   DATA(insert OID =  432 (	abstime_ops		702   ));
>   DESCR("");
> ! /* Technically, deftype is wrong, but it must be unique for index, bjm */
> ! DATA(insert OID =  433 (	bigbox_ops		433   ));
>   DESCR("");
>   DATA(insert OID =  434 (	poly_ops		604   ));
>   DESCR("");
> 
> and make the corresponding index non-unique.
> 
> (a) It is not supposed to be a unique column --- we'd not need the
> concept of index opclasses at all if there were only one possible
> operator set for any given column type!
> 
> (b) The above changes are making the oidjoins and opr_sanity regress
> tests fail, as indeed they should...

Patch reverse applied, and index no longer unique.  I saw these errors
too but was unsure of the cause and whether it was significant.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 22 Nov 1999 23:48:16 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] System cache index cleanup