From: Laurenz Albe <laurenz.albe@cybertec.at>
To: mujjamil995@gmail.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18516: Foreign key data integrity is not validated when reenabled the trigger on tables
Date: Thu, 20 Jun 2024 14:10:37 +0200
Message-ID: <412ed2e042c5cb552acfa45b01201083dcaae446.camel@cybertec.at> (raw)
In-Reply-To: <18516-a7e2ae00e4ccf5ca@postgresql.org>
References: <18516-a7e2ae00e4ccf5ca@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, mujjamil995@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18516: Foreign key data integrity is not validated when reenabled the trigger on tables
In-Reply-To: <412ed2e042c5cb552acfa45b01201083dcaae446.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox