Thank you, Laurenz, for your valuable feedback. If the enabling system trigger doesn’t check the data integrity with the parent table, then this syntax (ALTER TABLE DISABLE/ENABLE TRIGGER ALL) should be removed from PostgreSQL. Otherwise, it will create confusion for the users.
On Thu, 2024-06-20 at 09:10 +0000, PG Bug reporting form wrote:
> I come up with a weird issue in the PostgreSQL 15.6 version. To update the
> data in the tables I generally disable all triggers on the table using the
> superuser privilege and enable the after the data copy to avoid the checking
> the parent and child tables sequence. During the enable process FK
> constraint should check the data integrity between the child and parent
> tables. but somehow this is skipped and trigger enabled without any issue
> and later during the data restoration this issue is identified.
You wish that enabling the triggers would check the foreign key constraint,
but it doesn't. All it does is prevent new foreign key violations from
happening.
If you disable the system triggers that implement the foreign key, all
bets are off, and you have to make sure that only correct data are
loaded. This potential for data corruption is why only superusers can
disable system triggers.
Leave the foreign key triggers enabled while you load data.
Yours,
Laurenz Albe
--
Thanks and Regards,
Mujjamil Khureshi