5

I am copying data from one PostgreSQL v10 table to another. The destination table has several foreign key constraints. I was surprised that I did not get any errors, even though none of the tables referred to by the foreign key constraints had any data.

Before I did the copy, I used DISABLE TRIGGER ALL to ensure that the trigger defined on the destination table did not fire. I was surprised that the copy succeeded. After ENABLE TRIGGER ALL, I tried to add one more row, a copy of an existing row. That failed with a foreign key constraint violation. I then did DISABLE TRIGGER ALL, tried to add the new row, and it succeeded.

I conclude that in PostgreSQL 10, DISABLE TRIGGER ALL will disable foreign key constraint checks. Is that expected behavior?

Details can be found here.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
ROBERT RICHARDSON
  • 2,077
  • 4
  • 24
  • 57
  • Not just in Postgres 10, but in all versions. After all, `FOREIGN KEY` constraints are implemented with constraint triggers: https://dba.stackexchange.com/a/102498/3684 – Erwin Brandstetter Aug 10 '18 at 00:14

1 Answers1

4

Yes, that's expected.

From "ALTER TABLE":

DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER

These forms configure the firing of trigger(s) belonging to the table. (...) One can disable or enable a single trigger specified by name, or all triggers on the table, or only user triggers (this option excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints). Disabling or enabling internally generated constraint triggers requires superuser privileges; it should be done with caution since of course the integrity of the constraint cannot be guaranteed if the triggers are not executed. (...)

sticky bit
  • 36,626
  • 12
  • 31
  • 42