Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...

From: Anna Akenteva <a(dot)akenteva(at)postgrespro(dot)ru>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
Date: 2020-07-05 14:02:23
Message-ID: e16cbe912b71bf13acbfe22b22a4475f@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello, hackers!

I'd like to propose a feature for changing a constraint's index. The
provided patch allows to do it for EXCLUDE, UNIQUE, PRIMARY KEY and
FOREIGN KEY constraints.

Feature description:
ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
Replace a constraint's index with another sufficiently similar index.

Use cases:
- Removing index bloat [1] (now also achieved by REINDEX CONCURRENTLY)
- Swapping a normal index for an index with INCLUDED columns, or vice
versa

Example of use:
CREATE TABLE target_tbl (
id integer PRIMARY KEY,
info text
);
CREATE TABLE referencing_tbl (
id_ref integer REFERENCES target_tbl (id)
);
-- Swapping primary key's index for an equivalent index,
-- but with INCLUDE-d attributes.
CREATE UNIQUE INDEX new_idx ON target_tbl (id) INCLUDE (info);
ALTER TABLE target_tbl ALTER CONSTRAINT target_tbl_pkey USING INDEX
new_idx;
ALTER TABLE referencing_tbl ALTER CONSTRAINT referencing_tbl_id_ref_fkey
USING INDEX new_idx;
DROP INDEX target_tbl_pkey;

I'd like to hear your feedback on this feature.
Also, some questions:
1) If the index supporting a UNIQUE or PRIMARY KEY constraint is
changed, should foreign keys also automatically switch to the new index?
Or should the user switch it manually, by using ALTER CONSTRAINT USING
INDEX on the foreign key?
2) Whose name should change to fit the other - constraint's or index's?

[1]
https://www.postgresql.org/message-id/flat/CABwTF4UxTg%2BkERo1Nd4dt%2BH2miJoLPcASMFecS1-XHijABOpPg%40mail.gmail.com

Attachment Content-Type Size
alter_con_idx_v1.patch text/x-diff 286.8 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Anna Akenteva 2020-07-05 14:12:07 Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
Previous Message Konstantin Knizhnik 2020-07-05 13:46:09 Re: Built-in connection pooler