Блог

(Для фона я использую Postgres 12.4)

Мне непонятно, почему удаления работают, когда между двумя таблицами есть циклические FK, и оба FK настроены на КАСКАД УДАЛЕНИЯ.

 CREATE TABLE a (id bigint PRIMARY KEY);
CREATE TABLE b (id bigint PRIMARY KEY, aid bigint references a(id) on delete cascade);
ALTER TABLE  a ADD COLUMN bid int REFERENCES b(id) ON DELETE CASCADE ;

insert into a(id) values (5);
insert into b(id, aid) values (10,5);
update a set bid = 10 where id=5;

DELETE from a where id=5;

 

Как я думаю об этом, когда вы удаляете строку в таблице » a » с идентификатором PK = 5, postgres просматривает таблицы, в которых есть ссылочное ограничение, ссылающееся на a(идентификатор), находит b, пытается удалить строку в таблице b с идентификатором = 10, но затем ему приходится просматривать таблицы, ссылающиеся на b(идентификатор), поэтому он возвращается к a, а затем он должен просто оказаться в бесконечном цикле.

Но, похоже, это не так. Удаление завершается без ошибок. Это также не тот случай, как говорят некоторые источники в Интернете, когда вы не можете создать циклическое ограничение. Ограничения созданы успешно, и ни одно из них не может быть отменено.

Поэтому мой вопрос заключается в следующем: почему postgres завершает этот циклический каскад, даже если ни одно из ограничений не установлено на отсрочку, и если оно может это сделать, то какой смысл даже иметь возможность ОТСРОЧКИ?

Ограничения внешнего ключа реализуются как системные триггеры.

Для ON DELETE CASCADE этого триггер выполнит запрос типа:

 /* ----------
 * The query string built is
 *  DELETE FROM [ONLY] <fktable> WHERE $1 = fkatt1 [AND ...]
 * The type id's for the $ parameters are those of the
 * corresponding PK attributes.
 * ----------
 */
 

Выполняемый запрос представляет собой новый снимок базы данных, поэтому он не может видеть строки, удаленные предыдущими триггерами RI:

 /*
 * In READ COMMITTED mode, we just need to use an up-to-date regular
 * snapshot, and we will see all rows that could be interesting. But in
 * transaction-snapshot mode, we can't change the transaction snapshot. If
 * the caller passes detectNewRows == false then it's okay to do the query
 * with the transaction snapshot; otherwise we use a current snapshot, and
 * tell the executor to error out if it finds any rows under the current
 * snapshot that wouldn't be visible per the transaction snapshot.  Note
 * that SPI_execute_snapshot will register the snapshots, so we don't need
 * to bother here.
 */
 

Это гарантирует, что ни один триггер RI не попытается удалить ту же строку во второй раз, и, таким образом, цикличность будет нарушена.

(Все цитаты взяты из src/backend/utils/adt/ri_triggers.c .)