Postgres Pro
Downloads
Home   >   mailing lists

Thread: System cache index cleanup

System cache index cleanup

From
Bruce Momjian
Date:
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,
Pennsylvania19026

Re: [HACKERS] System cache index cleanup

From
Tom Lane
Date:
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

Re: [HACKERS] System cache index cleanup

From
Bruce Momjian
Date:
> 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,
Pennsylvania19026