PostgreSQL Antipatterns: меняем данные в обход триггера

[yriiarutiunian]

А нельзя локально выполнить все изменения в структуре базы данных, предварительно отключив все триггеры, а потом залить новую версию базы на ресурс, предварительно запретив доступ на него на время пока база заливается?

[Kilor]

Если у вас сервис должен работать 24x7, а размер базы исчисляется сотнями GB, то нет.

[Nord001]

Я бы создал отдельного пользователя no_triggers и в каждом тригере сделал бы проверку имени от которого выполняется и при совпадении ничего не делал.

[Iremel]

del

[the_motherofdragons]

Можно внутри транзакции сделать реплейс функции триггера на пустышку, сделать апдейт, вернуть назад функцию. Или так не сработает ?

[Kilor]

Вроде должно сработать, но это наложит блокировку на запись триггера на период этой транзакции, насколько я понимаю. То есть любая DML-операция, подпадающая под его условия, впадет в ожидание.

[akhkmed]

session_replication_role опасен. В документации написано, что это простой способ случайно сломать ссылочную целостность.

Так как внешние ключи реализованы посредством триггеров, присвоение этому параметру значения replica влечёт отключение всех проверок внешних ключей, что может привести к нарушению согласованности данных при некорректном использовании.

Ещё 1 безопасный способ без блокировок: в начале своей транзакции подменить триггерную функцию на пустышку, а в конце транзакции — вернуть как было.

[Kilor]

Ещё 1 безопасный способ без блокировок: в начале своей транзакции подменить триггерную функцию на пустышку, а в конце транзакции — вернуть как было.

А не случится ли тогда вот это?

[akhkmed]

ALTER TABLE..DISABLE TRIGGER заблокирует саму таблицу, ничего в соседних транзакциях с таблицей не сделаешь.

Пересоздание триггерной функции не блокирует таблицу, это легко проверить. Вероятно, блокирует только строку в pg_proc, то есть в соседней транзакции ту же функцию будет какое-то время не пересоздать.

[Kilor]

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

[Kilor]

Был неправ. Действительно, до окончания транзакции, меняющей триггерную функцию, блокировки не возникает — просто выполняется старая версия.

CREATE TABLE tst(a integer);

CREATE OR REPLACE FUNCTION trg() RETURNS trigger AS $$
BEGIN
	RAISE NOTICE 'trg';
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg AFTER INSERT ON tst EXECUTE PROCEDURE trg();

INSERT INTO tst(a)VALUES(1);
-- меняем в другом подключении
BEGIN;
CREATE OR REPLACE FUNCTION trg() RETURNS trigger AS $$
BEGIN
	RAISE NOTICE 'trgX';
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;
COMMIT;

[edo1h]

получается, это лучший вариант? хотя, конечно, сильно полагающийся на специфику postgresql (хотя бы то, что нет read uncommited)

[Kilor]

У этого способа есть негативный эффект — очень долгая и «жирная» транзакция, включающая все UPDATE, что может приводить к проблемам. Вариант с интеграцией условия в триггер будет работать и без этого.

[edo1h]

не понял.
если нам нужна транзакция — нам от неё никуда не деться.
если нам не нужна транзакция — кто нам мешает каждую операцию обернуть в транзакцию с временной подменой триггера?

[Kilor]

Тогда если нам надо выполнить 100k UPDATE — это значит, что pg_proc перенесет 200k перезаписей тела триггерной функции. А проблемы table bloat никто неотменял.

[edo1h]

ну если мне нужно будет сделать 100k UPDATE за раз, то разумеется, я не буду так поступать.
а если 100k в течение года — не вижу проблем.

[Kilor]

Если мы подменяем триггер однократно, врезая туда условие от переменной, то вроде как эффективнее получается. Если, конечно, микросекунды на этой проверке некритичны.

[Iremel]

Сделать параллельную таблицу для внесения изменений. Триггер ON UPDATE основной таблицы пусть смотрит также эту таблицу и переносит к себе из нее обновление, а после этого удаляет запись в параллельной.
При SELECT-е основной таблицы нефиксированные накаты добавляются из параллельной, например через правило. Это чтоб все планируемые изменения работали через основную таблицу уже с самого начала.
Постепенно при естественных UPDATE все обновления из параллельной таблицы перенесутся в основную, и когда та опустеет — можно будет отключить правило, вернуть ON UPDATE как было и удалить параллельную таблицу.