Re: disable and enable trigger all when a foreign keys

Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
> On 7/12/19 7:04 AM, Tom Lane wrote:
>> Well, yeah, DISABLE TRIGGER ALL defeats enforcement of FK constraints.
>> That's why you have to be superuser to use it[1].

> The OP is probably trying to understand why the below happens:

> alter table a add constraint a_id_b_fkey foreign key (id_b) references
> b(id);
> ERROR: insert or update on table "a" violates foreign key constraint
> "a_id_b_fkey"
> DETAIL: Key (id_b)=(3) is not present in table "b".

> Versus the above not happening when you re-enable a trigger.

Well, because ALTER ... ENABLE TRIGGER does what it says on the tin: it
re-enables firing of the trigger. That means future inserts and updates
will be checked; it doesn't extend to somehow checking events for which
trigger firings didn't happen in the past.

The OP already found the best method to get out of this, which is to
drop and re-add the FK constraint. Adding the constraint will force
a full verification scan, which is what you need here since there's
no information available about which checks were missed.

regards, tom lane