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

From: Thomas Reiss <thomas(dot)reiss(at)interieur(dot)gouv(dot)fr>
To: pgsql-general(at)postgresql(dot)org
Subject: How to handle ALTER TABLE DROP CONSTRAINT, ADD CONSTRAINT USING INDEX with foreign keys
Date: 2012-05-29 13:08:30
Message-ID: 4FC4CA4E.4040101@interieur.gouv.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message François Beausoleil 2012-05-29 13:17:29 Re: Disable Streaming Replication without restarting either master or slave
Previous Message Adrian Klaver 2012-05-29 12:58:50 Re: Export and import from one postgres server to another