Allow replacement of bloated primary key indexes without foreign key rebuilds - Mailing list pgsql-hackers

<div dir="ltr">Today I learnt [1,2,3] that the feature ALTER TABLE .. ADD CONSTRAINT ... USING INDEX we added back in
theday is not so useful in the field. Having to drop foreign key constraints before this command, and recreate them
afterwardsmakes this command useless to most database setups. I feel sorry that no one brought this up when we were
implementingthe feature; maybe we could've done something about it right then.<br /><br />I wish to correct it now, and
didsome research. Long story short, I realized that the foreign key constraint depends on the index relation of the
primarykey, and does not depend on the constraint object as I had expected (Please see rows 5 and 11 of the result set
shownbelow). This behaviour is also seen when the FKey references a unique constraint. As much as that perplexes me, I
thinkit makes our job a bit easier.<br /><br />All we need to do is allow swapping of pg_class.relfilenode of two
indexes.This will let the dependency entries stand as they are and allow us to drop the bloated primary key index
structurewithout having to rebuild the foreign key constraints.<br /><br />As for the syntactical sugar, this can be
addedto either ALTER TABLE or to ALTER INDEX. Although under no normal circumstances one would need to use ALTER INDEX
toswap two indexes' relfilenode (because one can easily create a duplicate index and drop/rename-in-place the old one),
Ithink it would make more sense here since it is just an operation on two indexes and has nothing to do with the
constraints,apart from the fact that we want to use this feature to meddle with the constraints.<br /><br />Syntax
options:<br/><br />ALTER TABLE tbl REPLACE [CONSTRAINT constr] {PRIMARY KEY | UNIQUE} USING INDEX new_index;<br /><br
/>ALTERINDEX ind REPLACE WITH new_index;<br /><br />Note that in both the syntaxes, it is assumed that all remnants of
new_indexwill be gone after the command completes successfully; that is, the commands will behave as if they deleted
theindex structure of the index being replaced and placed the new structure in its place, while dropping the index that
wasused for the replacement.<br /><br />I don't think we need to ensure that the new_index is completely flushed to
diskbefore the operation, but we do need to issue relevant cache invalidation messages after the operation is done.<br
/><br/>For replacement to be successful, new_index should not be associated with any constraints, and, new_index should
beidentical to the index being replaced, except for the index names.<br /><br />The ALTER TABLE syntax closely emulates
theexisting syntax of replacing a constraint using an existing index, but looking at the grammar construction I feel
thatit may be more complex to implement than the ALTER INDEX syntax.<br /><br /> ALTER INDEX feels easier to do, since
wewon't have to jump through hoops like in ALTER TABLE's multi-command support (ATExec*() functions), and dropping the
new_indexmight be easier to do.<br /><br />Thoughts?<br /><br /><span style="font-family:courier
new,monospace">postgres=#\d+ test</span><br style="font-family:courier new,monospace" /><span
style="font-family:couriernew,monospace">                         Table "public.test"</span><br
style="font-family:couriernew,monospace" /><span style="font-family:courier new,monospace"> Column |  Type   |
Modifiers| Storage | Stats target | Description </span><br style="font-family:courier new,monospace" /><span
style="font-family:courier
new,monospace">--------+---------+-----------+---------+--------------+-------------</span><br
style="font-family:couriernew,monospace" /><span style="font-family:courier new,monospace"> a      | integer | not
null | plain   |              | </span><br style="font-family:courier new,monospace" /><span style="font-family:courier
new,monospace"> b     | integer |           | plain   |              | </span><br style="font-family:courier
new,monospace"/><span style="font-family:courier new,monospace">Indexes:</span><br style="font-family:courier
new,monospace"/><span style="font-family:courier new,monospace">    "test_pkey" PRIMARY KEY, btree (a)</span><br
style="font-family:couriernew,monospace" /><span style="font-family:courier new,monospace">    "temp_idx" UNIQUE, btree
(a)</span><brstyle="font-family:courier new,monospace" /><span style="font-family:courier new,monospace"></span><span
style="font-family:couriernew,monospace">Referenced by:</span><br style="font-family:courier new,monospace" /><span
style="font-family:couriernew,monospace">    TABLE "test2" CONSTRAINT "test2_b_fkey" FOREIGN KEY (b) REFERENCES
test(a)</span><brstyle="font-family:courier new,monospace" /><span style="font-family:courier new,monospace">Has OIDs:
no</span><brstyle="font-family:courier new,monospace" /><br style="font-family:courier new,monospace" /><span
style="font-family:couriernew,monospace">postgres=# \d+ test2</span><br style="font-family:courier new,monospace"
/><spanstyle="font-family:courier new,monospace">                        Table "public.test2"</span><br
style="font-family:couriernew,monospace" /><span style="font-family:courier new,monospace"> Column |  Type   |
Modifiers| Storage | Stats target | Description </span><br style="font-family:courier new,monospace" /><span
style="font-family:courier
new,monospace">--------+---------+-----------+---------+--------------+-------------</span><br
style="font-family:couriernew,monospace" /><span style="font-family:courier new,monospace"> a      | integer
|          | plain   |              | </span><br style="font-family:courier new,monospace" /><span
style="font-family:couriernew,monospace"> b      | integer |           | plain   |              | </span><br
style="font-family:couriernew,monospace" /><span style="font-family:courier new,monospace">Foreign-key
constraints:</span><brstyle="font-family:courier new,monospace" /><span style="font-family:courier new,monospace">   
"test2_b_fkey"FOREIGN KEY (b) REFERENCES test(a)</span><br style="font-family:courier new,monospace" /><span
style="font-family:couriernew,monospace">Has OIDs: no</span><br style="font-family:courier new,monospace" /><br
/>Relevantoutput of query [4] on pg_depend:<br /><br style="font-family:courier new,monospace" /><span
style="font-family:couriernew,monospace">    classid    | objid |            objid             |  refclassid   |
refobjid|   refobjid   | deptype </span><br style="font-family:courier new,monospace" /><span
style="font-family:courier
new,monospace">---------------+-------+------------------------------+---------------+----------+--------------+---------<br
/> pg_class     | 16413 | test                         | pg_namespace  |     2200 | public       | n</span><br
style="font-family:couriernew,monospace" /><span style="font-family:courier new,monospace"> pg_type       | 16415 |
test                        | pg_class      |    16413 | test         | i</span><br style="font-family:courier
new,monospace"/><span style="font-family:courier new,monospace"> pg_type       | 16414 | test[]                       |
pg_type      |    16415 | test         | i</span><br style="font-family:courier new,monospace" /><span
style="font-family:couriernew,monospace"> pg_constraint | 16417 | test_pkey                    | pg_class      |   
16413| test         | a</span><br style="font-family:courier new,monospace" /><span style="font-family:courier
new,monospace"> pg_class     | 16416 | test_pkey                    | pg_constraint |    16417 | test_pkey    |
i</span><brstyle="font-family:courier new,monospace" /><span style="font-family:courier new,monospace"> pg_class      |
16418| test2                        | pg_namespace  |     2200 | public       | n</span><br style="font-family:courier
new,monospace"/><span style="font-family:courier new,monospace"> pg_type       | 16420 | test2                        |
pg_class     |    16418 | test2        | i</span><br style="font-family:courier new,monospace" /><span
style="font-family:couriernew,monospace"> pg_type       | 16419 | test2[]                      | pg_type       |   
16420| test2        | i</span><br style="font-family:courier new,monospace" /><span style="font-family:courier
new,monospace"> pg_constraint| 16421 | test2_b_fkey                 | pg_class      |    16413 | test         |
n</span><brstyle="font-family:courier new,monospace" /><span style="font-family:courier new,monospace"> pg_constraint |
16421| test2_b_fkey                 | pg_class      |    16418 | test2        | a</span><br style="font-family:courier
new,monospace"/><span style="font-family:courier new,monospace"> pg_constraint | 16421 | test2_b_fkey                 |
pg_class     |    16416 | test_pkey    | n</span><br style="font-family:courier new,monospace" /><span
style="font-family:couriernew,monospace"> pg_trigger    | 16422 | RI_ConstraintTrigger_a_16422 | pg_constraint |   
16421| test2_b_fkey | i</span><br style="font-family:courier new,monospace" /><span style="font-family:courier
new,monospace"> pg_trigger   | 16423 | RI_ConstraintTrigger_a_16423 | pg_constraint |    16421 | test2_b_fkey |
i</span><brstyle="font-family:courier new,monospace" /><span style="font-family:courier new,monospace"> pg_trigger    |
16424| RI_ConstraintTrigger_c_16424 | pg_constraint |    16421 | test2_b_fkey | i</span><br style="font-family:courier
new,monospace"/><span style="font-family:courier new,monospace"> pg_trigger    | 16425 | RI_ConstraintTrigger_c_16425 |
pg_constraint|    16421 | test2_b_fkey | i</span><br style="font-family:courier new,monospace" /><span
style="font-family:couriernew,monospace"></span><br />[1] <a
href="http://archives.postgresql.org/pgsql-general/2012-07/msg00104.php">http://archives.postgresql.org/pgsql-general/2012-07/msg00104.php</a><br
/>[2]<a
href="http://archives.postgresql.org/pgsql-general/2012-07/msg00105.php">http://archives.postgresql.org/pgsql-general/2012-07/msg00105.php</a><br
/>[3] <a
href="http://archives.postgresql.org/pgsql-general/2012-07/msg00110.php">http://archives.postgresql.org/pgsql-general/2012-07/msg00110.php</a><br
clear="all"/>[4] select classid::regclass, objid, case classid::regclass::text when 'pg_class' then
objid::regclass::textwhen 'pg_type' then objid::regtype::text when 'pg_constraint' then (select conname from
pg_constraintwhere oid = objid) when 'pg_namespace' then (select nspname from pg_namespace where oid = objid) when
'pg_trigger'then (select tgname from pg_trigger where oid = objid) else objid::text end, refclassid::regclass,
refobjid,case refclassid::regclass::text when 'pg_class' then refobjid::regclass::text when 'pg_type' then
refobjid::regtype::textwhen 'pg_constraint' then (select conname from pg_constraint where oid = refobjid) when
'pg_namespace'then (select nspname from pg_namespace where oid = refobjid) when 'pg_trigger' then (select tgname from
pg_triggerwhere oid = refobjid) else refobjid::text end, deptype from pg_depend;<br /><br />-- <br /><div
dir="ltr">GurjeetSingh<br />EnterpriseDB Corporation<br />The Enterprise PostgreSQL Company<br /></div><br /></div>