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

Поиск
Список
Период
Сортировка
От Thomas Reiss
Тема How to handle ALTER TABLE DROP CONSTRAINT, ADD CONSTRAINT USING INDEX with foreign keys
Дата
Msg-id 4FC4CA4E.4040101@interieur.gouv.fr
обсуждение исходный текст
Ответы Re: How to handle ALTER TABLE DROP CONSTRAINT, ADD CONSTRAINT USING INDEX with foreign keys  (Adrian Klaver <adrian.klaver@gmail.com>)
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Export and import from one postgres server to another
Следующее
От: François Beausoleil
Дата:
Сообщение: Re: Disable Streaming Replication without restarting either master or slave