Thread: Assigning fixed OIDs to system catalogs and indexes
While thinking about the use of hand-assigned OIDs for pg_proc and pg_operator, it occurred to me to wonder why we don't have hand-assigned OIDs for all system catalogs and indexes. Currently, most of the time that the C code wants to reference a specific catalog or index, it has to reference it by name. If we had fixed OIDs for all the catalogs and indexes known to the C code, we could get rid of heap_openr, index_openr, and the index-by-name maintained inside the relcache, because *all* such accesses would go by OID. I don't have hard numbers to prove it, but I think that the aggregate overhead of doing string instead of integer comparisons during those lookups has to be nontrivial. There are other annoyances such as having to use get_system_catalog_relid() in many places where a constant would be nice to have. The code wouldn't get any less readable -- we'd just be replacing macros that expand to strings with ones that expand to numbers. Thoughts? Anyone have an argument why we should not do this? regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes > While thinking about the use of hand-assigned OIDs for pg_proc and > pg_operator, it occurred to me to wonder why we don't have hand-assigned > OIDs for all system catalogs and indexes. Currently, most of the time > that the C code wants to reference a specific catalog or index, it has > to reference it by name. If we had fixed OIDs for all the catalogs and > indexes known to the C code, we could get rid of heap_openr, > index_openr, and the index-by-name maintained inside the relcache, > because *all* such accesses would go by OID. I don't have hard numbers > to prove it, but I think that the aggregate overhead of doing string > instead of integer comparisons during those lookups has to be > nontrivial. There are other annoyances such as having to use > get_system_catalog_relid() in many places where a constant would be nice > to have. So some changing-oid operations like vacuum full, reindex, etc will not affect these system catalogs? Regards, Qingqing
> So some changing-oid operations like vacuum full, reindex, etc will not > affect these system catalogs? Is it possible to cluster system tables?
On Wed, Apr 13, 2005 at 09:54:28AM +0800, Christopher Kings-Lynne wrote: > >So some changing-oid operations like vacuum full, reindex, etc will not > >affect these system catalogs? > > Is it possible to cluster system tables? No. And then, some catalogs are only allowed to be reindexed in place. (I think only shared catalogs.) VACUUM FULL does not change the Oid, does it? -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Siempre hay que alimentar a los dioses, aunque la tierra esté seca" (Orual)
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes > Is it possible to cluster system tables? > Ooops, I guess I mixed two concepts - oid and relfilenode. Those operations change relfilenode but not oids. I don't think we could cluster system tables. template1=# select oid, relfilenode from pg_class where relname = 'pg_attribute_ relid_attnam_index'; oid | relfilenode -------+-------------16688 | 17239 (1 row) template1=# reindex table pg_attribute; REINDEX template1=# select oid, relfilenode from pg_class where relname = 'pg_attribute_ relid_attnam_index'; oid | relfilenode -------+-------------16688 | 17241 (1 row) template1=# cluster pg_attribute_relid_attnam_index on pg_attribute; ERROR: "pg_attribute" is a system catalog Regards, Qingqing
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: >> So some changing-oid operations like vacuum full, reindex, etc will not >> affect these system catalogs? > Is it possible to cluster system tables? No, and yes. CLUSTER, REINDEX, and similar things change the relfilenode, not the relation OID. If you are looking for things that will fundamentally break the system, try renaming one of the indexes of a core system catalog ;-). We could avoid that dependency if we went by OIDs instead. regards, tom lane
"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes: > template1=# cluster pg_attribute_relid_attnam_index on pg_attribute; > ERROR: "pg_attribute" is a system catalog That error has nothing to do with any risk of reassigning OIDs. The issue is whether we can change the index's relfilenode or not --- the error is actually coming from here: /* * Disallow clustering system relations. This will definitely NOT * work for shared relations (we have no wayto update pg_class rows * in other databases), nor for nailed-in-cache relations (the * relfilenode values for thoseare hardwired, see relcache.c). It * might work for other system relations, but I ain't gonna risk it. */ if(IsSystemRelation(OldHeap)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("\"%s\" is a system catalog", RelationGetRelationName(OldHeap)))); AFAIK it would work for cases not explained in the comment, but it's not been tested. regards, tom lane
Tom Lane wrote: > Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > >> So some changing-oid operations like vacuum full, reindex, etc will not > >> affect these system catalogs? > > > Is it possible to cluster system tables? > > No, and yes. CLUSTER, REINDEX, and similar things change the > relfilenode, not the relation OID. > > If you are looking for things that will fundamentally break the system, > try renaming one of the indexes of a core system catalog ;-). We could > avoid that dependency if we went by OIDs instead. Right now you can use a temporary table in place of a system table using the temporary schema. Would that still work?test=> BEGIN;BEGINtest=> CREATE temporary TABLE pg_class(x INT);CREATETABLEtest=> INSERT INTO pg_class VALUES (1);INSERT 0 1test=> SELECT * FROM pg_class; x--- 1(1 row) -- 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, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes: > Tom Lane wrote: >> If you are looking for things that will fundamentally break the system, >> try renaming one of the indexes of a core system catalog ;-). We could >> avoid that dependency if we went by OIDs instead. > Right now you can use a temporary table in place of a system > table using the temporary schema. Would that still work? Sure. This doesn't change any externally visible behavior ... other than the response to unsupported changes to the system catalogs ;-) regards, tom lane