Как сломать ссылочную целостность в PostgreSQL
17 июня 2024
Любая система имеет свои ограничения, и PostgreSQL не является исключением. Например, недавно мы разбирали проблему XID wraparound, которая может привести к деградации производительности, а в пределе — перевести СУБД в режим read-only. Сегодня мы рассмотрим еще одно ограничение, связанное с взаимодействием внешних ключей и триггеров.
Допустим, есть такая база данных:
uid SERIAL PRIMARY KEY,
name TEXT);
CREATE TABLE phones(
uid INTEGER REFERENCES users ON DELETE CASCADE,
phone TEXT);
Есть пользователи, у пользователей есть номера телефонов. Нельзя создать номер телефона, который никому не принадлежит:
ERROR: insert or update on table "phones" violates foreign key ⏎
constraint "phones_uid_fkey"
DETAIL: Key (uid)=(1) is not present in table "users".
Заполним таблицы какими-то данными:
-- у одного человека может быть много номеров телефона
INSERT INTO phones VALUES(1, '79161234567');
INSERT INTO phones VALUES(1, '79161112233');
Если теперь удалить пользователя:
… то также удалятся и принадлежащие ему номера:
uid | phone
-----+-------
(0 rows)
=# SELECT * FROM users;
uid | name
-----+------
(0 rows)
Пока что все хорошо. Теперь мы по каким-то причинам решаем запретить удаление телефонов при помощи триггеров:
BEGIN
RAISE NOTICE 'DELETEs are not allowed on phones';
RETURN NULL;
END
$$ LANGUAGE 'plpgsql';
-- здесь нужно явно указать FOR EACH ROW, так как по умолчанию
-- создается триггер на выражения, FOR EACH STATEMENT
CREATE TRIGGER phones_on_delete BEFORE DELETE
ON phones FOR EACH ROW EXECUTE FUNCTION phones_on_delete();
Проверяем:
INSERT INTO phones VALUES(1, '79161234567');
DELETE FROM phones WHERE uid = 1;
… и видим:
DELETE 0
Телефоны не удаляются. Пока что все работает именно так, как и задумывалось. Но что произойдет, если попытаться удалить пользователя?
NOTICE: DELETEs are not allowed on phones
DELETE 1
=# SELECT * FROM users;
uid | name
-----+------
(0 rows)
=# SELECT * FROM phones;
uid | phone
-----+-------------
1 | 79161234567
(1 row)
Остался телефон, ссылающийся на несуществующего пользователя. Мы только что сломали ссылочную целость. Это не баг, а документированное поведение.
Вот цитата из документации на CREATE TRIGGER:
[...] There is also nonstandard behavior if BEFORE triggers modify rows or prevent updates during an update that is caused by a referential action. This can lead to constraint violations or stored data that does not honor the referential constraint.
Также в главе Overview of Trigger Behavior для PostgreSQL ≥ 17 был добавлен следующий параграф:
If a foreign key constraint specifies referential actions (that is, cascading updates or deletes), those actions are performed via ordinary SQL update or delete commands on the referencing table. In particular, any triggers that exist on the referencing table will be fired for those changes. If such a trigger modifies or blocks the effect of one of these commands, the end result could be to break referential integrity. It is the trigger programmer’s responsibility to avoid that.
Мораль такая. Изучайте ограничения использованных вами инструментов — СУБД, ОС, языков программирования, библиотек, и так далее. Это не только интересно, но и избавит вас от неприятных сюрпризов на проде.
Метки: PostgreSQL, СУБД.
Вы можете прислать свой комментарий мне на почту, или воспользоваться комментариями в Telegram-группе.