Assigning fixed OIDs to system catalogs and indexes

"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?

"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

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:

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 way to update pg_class rows
* in other databases), nor for nailed-in-cache relations (the
* relfilenode values for those are 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;
BEGIN
test=> CREATE temporary TABLE pg_class(x INT);
CREATE TABLE
test=> INSERT INTO pg_class VALUES (1);
INSERT 0 1
test=> 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, Pennsylvania 19073

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