Re: BUG #18516: Foreign key data integrity is not validated when reenabled the trigger on tables

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, Jun 20, 2024 at 5:40 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> 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*