Thread: How to handle ALTER TABLE DROP CONSTRAINT, ADD CONSTRAINT USING INDEX with foreign keys
How to handle ALTER TABLE DROP CONSTRAINT, ADD CONSTRAINT USING INDEX with foreign keys
From
Thomas Reiss
Date:
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. Kind regards, Thomas Reiss
Re: How to handle ALTER TABLE DROP CONSTRAINT, ADD CONSTRAINT USING INDEX with foreign keys
From
Adrian Klaver
Date:
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