PostgreSQL - отключение ограничений

Согласно предыдущим комментариям, это должно быть проблемой. Тем не менее, есть команда, которая может быть тем, что вы ищете - она ​​установит ограничения как отложенные, поэтому они проверяются при COMMIT, а не при каждом удалении. Если вы выполняете только одно большое УДАЛЕНИЕ всех строк, это не будет иметь никакого значения, но если вы делаете это по частям, это будет.

SET CONSTRAINTS ALL DEFERRED

это то, что вы ищете в таком случае. Обратите внимание, что ограничения должны быть помечены как DEFERRABLE, прежде чем их можно будет отложить. Например:

ALTER TABLE table_name
  ADD CONSTRAINT constraint_uk UNIQUE(column_1, column_2)
  DEFERRABLE INITIALLY IMMEDIATE;

Затем ограничение можно отложить в транзакции или функции следующим образом:

CREATE OR REPLACE FUNCTION f() RETURNS void AS
$BODY$
BEGIN
  SET CONSTRAINTS ALL DEFERRED;

  -- Code that temporarily violates the constraint...
  -- UPDATE table_name ...
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Что сработало для меня, так это отключить одну за другой TRIGGERS из тех таблиц, которые будут задействованы в DELETE операции.

ALTER TABLE reference DISABLE TRIGGER ALL;
DELETE FROM reference WHERE refered_id > 1;
ALTER TABLE reference ENABLE TRIGGER ALL;

Решение работает в версии 9.3.16. В моем случае время выполнения DELETE операций увеличилось с 45 минут до 14 секунд.

Как указано в разделе комментариев @amphetamachine, для выполнения этой задачи вам потребуется admin привилегий для таблиц.

Если вы попробуете DISABLE TRIGGER ALL и получите сообщение об ошибке типа permission denied: "RI_ConstraintTrigger_a_16428" is a system trigger (я получил это на Amazon RDS), попробуйте следующее:

set session_replication_role to replica;

В случае успеха все триггеры, лежащие в основе ограничений таблицы, будут отключены. Теперь вам нужно убедиться, что ваши изменения оставляют БД в согласованном состоянии!

Затем, когда вы закончите, повторно активируйте триггеры и ограничения для вашего сеанса с помощью:

set session_replication_role to default;

(В этом ответе предполагается, что вы намерены удалить все строки этих таблиц, а не только их выбор.)

Мне тоже пришлось это сделать, но в рамках набора тестов. Я нашел ответ, предложил в другом месте на SO. Используйте TRUNCATE TABLE следующим образом:

TRUNCATE TABLE <list-of-table-names> [RESTART IDENTITY] [CASCADE];

Следующее быстро удаляет все строки из таблиц table1, table2 и table3, при условии, что нет ссылок на строки этих таблиц из таблиц, не перечисленных:

TRUNCATE TABLE table1, table2, table3;

Пока ссылки находятся между перечисленными таблицами, PostgreSQL удалит все строки, не заботясь о ссылочной целостности. Если таблица, отличная от перечисленных, ссылается на строку одной из этих таблиц, запрос завершится ошибкой.

Однако вы можете уточнить запрос, чтобы он также усекал все таблицы со ссылками на перечисленные таблицы (хотя я этого не пробовал):

TRUNCATE TABLE table1, table2, table3 CASCADE;

По умолчанию последовательности этих таблиц не перезапускают нумерацию. Новые строки продолжатся со следующим номером последовательности. Для перезапуска порядковой нумерации:

TRUNCATE TABLE table1, table2, table3 RESTART IDENTITY;

Мой PostgreSQL - 9.6.8.

set session_replication_role to replica;

работать на меня, но мне нужно разрешение.

Я вхожу в psql с суперпользователем.

sudo -u postgres psql

Затем подключитесь к моей базе данных

\c myDB

И запускаем:

set session_replication_role to replica;

Теперь я могу удалить из таблицы с ограничением.

Отключить все ограничения таблицы

ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName

- Включить все ограничения таблицы

ALTER TABLE TableName CHECK CONSTRAINT ConstraintName