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
P.S. I apologize for resending the email, the previous one was sent as a
response to another thread by mistake.
--
Anna Akenteva
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company