Обсуждение: 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

On Mon, Dec 1, 2025 at 2:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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.
in ExecInitGenerated, we have:

    /*
     * In an UPDATE, we can skip computing any generated columns that do not
     * depend on any UPDATE target column.  But if there is a BEFORE ROW
     * UPDATE trigger, we cannot skip because the trigger might change more
     * columns.
     */
    if (cmdtype == CMD_UPDATE &&
        !(rel->trigdesc && rel->trigdesc->trig_update_before_row))
        updatedCols = ExecGetUpdatedCols(resultRelInfo, estate);
    else
        updatedCols = NULL;

So I applied the equivalent approach. This should works fine, because if we are
able to skip computing certain generated columns, then we sure sure be able to
skip evaluating some check constraints.


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