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

On Fri, 2020-09-04 at 13:31 -0400, Alvaro Herrera wrote:
> On 2020-Sep-04, Laurenz Albe wrote:
> > On Fri, 2020-09-04 at 10:41 -0400, Alvaro Herrera wrote:
> > > > The value I see in this is:
> > > > - replacing a primary key index
> > > > - replacing the index behind a constraint targeted by a foreign key
> > > But why is this better than using REINDEX CONCURRENTLY?
> > It is not better, but it can be used to replace a constraint index
> > with an index with a different INCLUDE clause, which is something
> > that cannot easily be done otherwise.
>
>
> I can see that there is value in having an index that serves both a
> uniqueness constraint and coverage purposes. But this seems a pretty
> roundabout way to get that -- I think you should have to do "CREATE
> UNIQUE INDEX ... INCLUDING ..." instead. That way, the fact that this
> is a Postgres extension remains clear.
>
> 55432 14devel 24138=# create table foo (a int not null, b int not null, c int);
> CREATE TABLE
> Duración: 1,775 ms
> 55432 14devel 24138=# create unique index on foo (a, b) include (c);
> CREATE INDEX
> Duración: 1,481 ms
> 55432 14devel 24138=# create table bar (a int not null, b int not null, foreign key (a, b) references foo (a, b));
> CREATE TABLE
> Duración: 2,559 ms
>
> Now you have a normal index that you can reindex in the normal way, if you need
> it.

Yes, that is true.

But what if you have done

CREATE TABLE a (id bigint CONSTRAINT a_pkey PRIMARY KEY, val integer);
CREATE TABLE b (id bigint CONSTRAINT b_fkey REFERENCES a);

and later you figure out later that it would actually be better to have
an index ON mytab (id) INCLUDE (val), and you don't want to maintain
two indexes.

Yes, you could do

CREATE UNIQUE INDEX CONCURRENTLY ind ON a (id) INCLUDE (val);
ALTER TABLE a ADD UNIQUE USING INDEX ind;
ALTER TABLE a DROP CONSTRAINT a_pkey CASCADE;
ALTER TABLE b ADD FOREIGN KEY (id) REFERENCES a(id);

but then you don't have a primary key, and you have to live without
the foreign key for a while.

Adding a primary key to a large table is very painful, because it
locks the table exclusively for a long time.

This patch would provide a more convenient way to do that.

Again, I am not sure if that justifies the effort.

Yours,
Laurenz Albe