Re: How to handle ALTER TABLE DROP CONSTRAINT, ADD CONSTRAINT USING INDEX with foreign keys

On 05/29/2012 06:08 AM, Thomas Reiss wrote:
> Hello,
>
> PostgreSQL 9.1 introduced an ALTER TABLE DROP CONSTRAINT, ADD CONSTRAINT
> USING INDEX command to help index maintenance. I works for some cases,
> but I can't get it work with a primary key index which is referenced by
> a foreign key.
>
> Here's an example of the problem I encounter :
> db=# CREATE TABLE master (i serial primary key, value integer);
> NOTICE: CREATE TABLE will create implicit sequence "master_i_seq" for
> serial column "master.i"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "master_pkey" for table "master"
> CREATE TABLE
> db=# CREATE TABLE detail (id serial primary key, master_id integer
> REFERENCES master (i));
> NOTICE: CREATE TABLE will create implicit sequence "detail_id_seq" for
> serial column "detail.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "detail_pkey" for table "detail"
> CREATE TABLE
> db=# CREATE UNIQUE INDEX CONCURRENTLY tmp_index ON master (i);
> CREATE INDEX
>
> And now, we try to drop the old PK constraint and create a new one with
> index tmp_index, which fails because of the FK :
> db=# ALTER TABLE master DROP CONSTRAINT master_pkey, ADD CONSTRAINT
> master_pkey PRIMARY KEY USING INDEX tmp_index;
> ERROR: cannot drop constraint master_pkey on table master because other
> objects depend on it
> DETAIL: constraint detail_master_id_fkey on table detail depends on
> index master_pkey
> HINT: Use DROP ... CASCADE to drop the dependent objects too.
>
> Actually, the only way to "solve" this issue is to swap the relfilenode
> columns between the old and the new index. I don't like this option very
> much by the way but I'm very interested by your opinion on updating the
> pg_class catalog this way.

Why not?:
BEGIN;
ALTER TABLE master DROP CONSTRAINT master_pkey CASCADE, ADD CONSTRAINT
master_pkey PRIMARY KEY USING INDEX tmp_index;
ALTER TABLE detail ADD CONSTRAINT detail_master_id_fkey FOREIGN
KEY(master_id) REFERENCES master(id);
COMMIT;

Though I am not sure what the above gets you as there is already an
index on master.id.

>
> Kind regards,
> Thomas Reiss
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com