Feedback-ID: ia2694551:Fastmail
Date: Thu, 20 Jun 2024 15:07:44 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Mujjamil k <mujjamil995@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>,
	pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18516: Foreign key data integrity is not validated when
 reenabled the trigger on tables
Message-ID: <202406201307.ojevjmezqu45@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAFvoc=n+zbX6=A-v_949wH2Yt_4E5K8hGkjR4ts6qbo4-z9XNA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202406201307.ojevjmezqu45%40alvherre.pgsql>
Precedence: bulk

On 2024-Jun-20, Mujjamil k wrote:

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

Well, we're not doing that.

This is documented; the page
https://www.postgresql.org/docs/devel/sql-altertable.html
contains, under "DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER", the
following very explicit text:

: 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.

There's a reason you were forced to become superuser in order to do
this.  Anytime you're forced to become superuser, you should take a step
back and think carefully what you're doing.


I think it's not a bad idea to suggest that we could have a new command
ALTER TABLE .. DISABLE CONSTRAINT, which can be run by the table owner
and paired with a later ALTER TABLE ENABLE CONSTRAINT, which verifies
the constraint.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Entristecido, Wutra                     (canción de Las Barreras)
echa a Freyr a rodar
y a nosotros al mar"