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.klaver@gmail.com