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

hi.

code has been further simplified and is now more neat.
The test is kind of verbose now.


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

On Fri, Jan 23, 2026 at 10:09 AM jian he <jian.universality@gmail.com> wrote:
> code has been further simplified and is now more neat.
> The test is kind of verbose now.
>

if (check_attrs &&
    !bms_is_member(-FirstLowInvalidHeapAttributeNumber, check_attrs) &&
    !bms_overlap(check_attrs, updatedCols))
{
        ereport(DEBUG1,
                errmsg_internal("skipping verification for constraint
\"%s\" on table \"%s\"",
                                check[i].ccname,
                                RelationGetRelationName(rel)));
}

We need tests to reach the above ereport(DEBUG1, branch.

We can use
+SET log_statement to NONE;
+SET client_min_messages TO 'debug1';

to make sure the regress output has the DEBUG1 message.



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

Thank to Jacob Champion for the off-list review.
Now ExecRelCheck looks much neater.



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

hi.

We cache ri_CheckConstraintExprs in ExecConstraints and must initialize them all
if different actions like INSERT and UPDATE, happen together in a query.
Both INSERT and UPDATE need to use these ri_CheckConstraintExprs.
Invoke INSERT AND UPDATE together can happen within MERGE command. We confirm
it's a MERGE operation by checking that resultRelInfo->ri_MergeActions is not
NIL. See ExecMergeNotMatched and ExecMergeMatched.

For cross-partition updates (ExecCrossPartitionUpdate), the operation splits
into an INSERT and DELETE, we can treat it as a single action.



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

On Sat, Mar 7, 2026 at 12:10 AM Florin Irion <irionr@gmail.com> wrote:
>
> Hi Jian,
>
> An alternative that preserves the optimization for MERGE UPDATE actions
> would be to follow the existing ri_GeneratedExprsI/ri_GeneratedExprsU
> pattern — split into two separate cached arrays:
>
> ```
> /* array of expr states for checking check constraints */
> ExprState **ri_CheckConstraintExprsI; /* for INSERT */
> ExprState **ri_CheckConstraintExprsU; /* for UPDATE */
> ```

I have given it a try, please see attached v7.
I don’t have a strong preference for either v6 or v7.

> 2. The cross-partition update test comment says "cannot be skipped", but
> cross-partition UPDATE goes through ExecCrossPartitionUpdate which
> does DELETE + INSERT. The constraint check happens via ExecInsert with
> CMD_INSERT on the destination partition, so the optimization was never
> applicable. The test doesn't exercise anything specific to this patch.
>

OK. I kept these tests. I think covering this scenario is useful.  Perhaps it
has already been tested elsewhere, but including it here makes the tests more
complete



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