Postgres: DISABLE TRIGGER ALL and ON UPDATE CASCADE

Question

It seems like when I

ALTER TABLE foo DISABLE TRIGGER ALL;
DELETE FROM foo;

The deletions don't get cascaded to other tables with

FOREIGN KEY ("fooId") REFERENCES "foo"("id") ON DELETE CASCADE

constraints, but I can't find anything in the documentation about this. I also don't see any triggers I didn't create in

SELECT * FROM information_schema.triggers;

I'm guessing ON UPDATE/ON DELETE are implemented internally as triggers (which aren't visible in pgAdmin)? Does anyone know where this is documented?

Laurenz Albe · Accepted Answer · 2020-03-28 04:41:07Z

3

Try here:

DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
These forms configure the firing of trigger(s) belonging to the table. A disabled trigger is still known to the system, but is not executed when its triggering event occurs. For a deferred trigger, the enable status is checked when the event occurs, not when the trigger function is actually executed. 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.

edited Mar 28, 2020 at 4:41

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Mar 28, 2020 at 0:57

Renato

2,1871 gold badge13 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andy Over a year ago

ah, good catch, I must have tried to find that with ⌘-F DISABLE TRIGGER and failed...

Megadest Over a year ago

Also, pg_trigger should display internally generated triggers: postgresql.org/docs/9.3/catalog-pg-trigger.html

Collectives™ on Stack Overflow

Postgres: DISABLE TRIGGER ALL and ON UPDATE CASCADE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related