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

On 2020-07-07 01:08, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
>> On 2020-Jul-05, Anna Akenteva wrote:
>>> -- 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;
>
>> How is this state represented by pg_dump?
>
> Even if it's possible to represent, I think we should flat out reject
> this "feature". Primary keys that aren't primary keys don't seem like
> a good idea. For one thing, it won't be possible to describe the
> constraint accurately in the information_schema.

Do you think it could still be a good idea if we only swap the
relfilenodes of indexes, as it was suggested in [1]? The original use
case was getting rid of index bloat, which is now solved by REINDEX
CONCURRENTLY, but this feature still has its own use case of adding
INCLUDE-d columns to constraint indexes.

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

--
Anna Akenteva
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company