Ошибка удаления PostgreSQL с правилом ON DELETE для унаследованной таблицы

В моей базе данных PostgreSQL 9.1 я определил ПРАВИЛА, которые удаляют строки из дочерних таблиц всякий раз, когда удаляется строка родительской таблицы. Все это работало нормально, пока я не ввел наследование. Если родительская (ссылающаяся) таблица INHERITS из другой таблицы и я удаляю из базовой таблицы, то DELETE выполняется успешно, но ПРАВИЛО вообще не срабатывает - строка, на которую указывает ссылка, не удаляется. Если я попытаюсь удалить из производной таблицы, я получаю сообщение об ошибке:

update or delete on table "referenced" violates foreign key constraint "fk_derived_referenced" on table "derived"

В родительской таблице нет другой строки, которая нарушала бы внешний ключ: на нее ссылается удаляемая строка! Как я могу это исправить?

Следующий скрипт воспроизводит проблему:

-- Schema

CREATE TABLE base
(
  id serial NOT NULL,
  name character varying(100),
  CONSTRAINT pk_base PRIMARY KEY (id)
);

CREATE TABLE referenced
(
  id serial NOT NULL,
  value character varying(100),
  CONSTRAINT pk_referenced PRIMARY KEY (id)
);

CREATE TABLE derived
(
  referenced_id integer,
  CONSTRAINT pk_derived PRIMARY KEY (id),
  CONSTRAINT fk_derived_referenced FOREIGN KEY (referenced_id) REFERENCES referenced (id)
)
INHERITS (base);

-- The rule

CREATE OR REPLACE RULE rl_derived_delete_referenced
AS ON DELETE TO derived DO ALSO
DELETE FROM referenced r WHERE r.id = old.referenced_id;

-- Some test data

INSERT INTO referenced (id, value)
VALUES (1, 'referenced 1');

INSERT INTO derived (id, name, referenced_id)
VALUES (2, 'derived 2', 1);

-- Delete from base - deletes the "base" and "derived" rows, but not "referenced"
--DELETE FROM base
--WHERE id = 2;

-- Delete from derived - fails with:
-- update or delete on table "referenced" violates foreign key constraint "fk_derived_referenced" on table "derived"
DELETE FROM derived
WHERE id = 2

postgresql inheritance sql rules
person EMP    schedule 28.09.2011    source источник
comment
Простым способом сделать это было бы использовать ON DELETE CASCADE в ссылке внешнего ключа и удалить из таблицы, на которую ссылаются, а не из производной. (И это тоже работает.) Есть ли веская причина, по которой вы решили этого не делать?   -  person Mike Sherrill 'Cat Recall'    schedule 28.09.2011

Ответы (2)


arrow_upward
1
arrow_downward

Как я уже сказал в своем комментарии, это кажется необычным способом делать что-то. Но вы можете заставить его работать с отложенным ограничением.

CREATE TABLE derived
(
  referenced_id integer,
  CONSTRAINT pk_derived PRIMARY KEY (id),
  CONSTRAINT fk_derived_referenced FOREIGN KEY (referenced_id) 
    REFERENCES referenced (id) DEFERRABLE INITIALLY DEFERRED
)
INHERITS (base);

Документы PostgreSQL, Правила и триггеры, говорят

Многие вещи, которые можно сделать с помощью триггеров, также можно реализовать с помощью системы правил PostgreSQL. Одной из вещей, которые нельзя реализовать с помощью правил, являются некоторые виды ограничений, особенно внешние ключи.

Но мне не ясно, с чем вы сталкиваетесь, с этим конкретным ограничением.

person Mike Sherrill 'Cat Recall'    schedule 28.09.2011
comment
Виртуальный +1 для @Catcall. Я не могу голосовать, потому что я не зарегистрирован. - person wildplasser; 28.09.2011
comment
Спасибо, DEFERRED решает эту проблему. В конце концов я использовал TRIGGER вместо RULE, у которого такой проблемы нет. - person EMP; 29.09.2011
comment
Кстати, поздравляю с достижением отметки в 10 тысяч! - person EMP; 29.09.2011
comment
@EMP: Спасибо. 10 км за 8 месяцев — такими темпами я рассчитываю догнать Джона Скита в 2038 году. Конечно, к тому времени он продвинется вперед лет на 40 или около того. - person Mike Sherrill 'Cat Recall'; 29.09.2011

arrow_upward
0
arrow_downward

Кроме того, вам необходимо проверить, ссылаются ли другие записи на строки, подлежащие удалению. Я добавил тестовую производную запись № 3, которая указывает на ту же ссылочную запись № 1.

-- The rule    
CREATE OR REPLACE RULE rl_derived_delete_referenced
AS ON DELETE TO tmp.derived DO ALSO (
    DELETE FROM tmp.referenced re_del
    WHERE re_del.id = OLD.referenced_id
    AND NOT EXISTS ( SELECT * FROM tmp.derived other
        WHERE other.referenced_id = re_del.id
        AND other.id <> OLD.id )
        ;
    );

-- Some test data

INSERT INTO tmp.referenced (id, value)
VALUES (1, 'referenced 1');

-- EXPLAIN ANALYZE
INSERT INTO tmp.derived (id, name, referenced_id)
VALUES (2, 'derived 2', 1); 

INSERT INTO tmp.derived (id, name, referenced_id)
VALUES (3, 'derived 3', 1);

-- Delete from base - deletes the "base" and "derived" rows, but not "referenced"
--DELETE FROM base
--WHERE id = 2;

-- Delete from derived - fails with:
-- update or delete on table "referenced" violates foreign key constraint "fk_derived_referenced" on table "derived"

EXPLAIN ANALYZE
DELETE FROM tmp.derived
WHERE id = 2
    ;

SELECT * FROM tmp.base;
SELECT * FROM tmp.derived;
SELECT * FROM tmp.referenced;
person wildplasser    schedule 28.09.2011