Обсуждение: UPDATE run check constraints for affected columns only

hi.

while casually looking at https://wiki.postgresql.org/wiki/Todo
then I found out this thread:
https://postgr.es/m/1326055327.15293.13.camel%40vanquo.pezone.net

Seems easier to do nowadays.
The attached patch implements the $subject.

regress tests seems not enough to test it.
Following the approach in 001_constraint_validation.pl, we use
ereport(DEBUG1, errmsg_internal), then grep the logs to check whether the
enforced constraint verification was skipped or not.
we can not add check constraint to VIEW,
tests covered partitioned table scarenio.

DEMO:
CREATE TABLE upd_check_skip (a int, b int, c int, d int generated
always as (b+c) STORED);
ALTER TABLE upd_check_skip ADD CONSTRAINT cc2 CHECK(a+c < 100);
ALTER TABLE upd_check_skip ADD CONSTRAINT cc3 CHECK(b < 1);
ALTER TABLE upd_check_skip ADD CONSTRAINT cc4 CHECK(d < 2);
INSERT INTO upd_check_skip DEFAULT VALUES;
SET client_min_messages to DEBUG1;

--constraint verification will be skipped for cc3, cc4
UPDATE upd_check_skip SET a = 1;

--constraint verification will be skipped for cc2
UPDATE upd_check_skip SET b = -1;

--constraint verification will be skipped for cc3
UPDATE upd_check_skip SET c = -1;


--
jian
https://www.enterprisedb.com

jian he <jian.universality@gmail.com> writes:
> The attached patch implements the $subject.

Does this cover the case where a BEFORE UPDATE trigger has modified
columns that were not mentioned in UPDATE...SET?

            regards, tom lane

Hi,

+1 on Tom's point about BEFORE UPDATE triggers.
I also noticed that in execReplication.c, ExecSimpleRelationUpdate() passes CMD_INSERT to ExecConstraints():
ExecConstraints(CMD_INSERT, resultRelInfo, slot, estate);
I think this should be CMD_UPDATE?

Regards,
Yuan Li(carol)

-----邮件原件-----
发件人: Tom Lane <tgl@sss.pgh.pa.us> 
发送时间: 2025年12月1日 14:33
收件人: jian he <jian.universality@gmail.com>
抄送: PostgreSQL-development <pgsql-hackers@postgresql.org>
主题: Re: UPDATE run check constraints for affected columns only

jian he <jian.universality@gmail.com> writes:
> The attached patch implements the $subject.

Does this cover the case where a BEFORE UPDATE trigger has modified columns that were not mentioned in UPDATE...SET?

            regards, tom lane