Horton
Menu

Thread

  1. UPDATE run check constraints for affected columns only

    jian he <jian.universality@gmail.com> — 2025-12-01T06:20:32Z

    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

  2. Re: UPDATE run check constraints for affected columns only

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-12-01T06:33:19Z

    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

  3. 回复: UPDATE run check constraints for affected columns only

    li carol <carol.li2025@outlook.com> — 2025-12-01T09:24:11Z

    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

  4. Re: UPDATE run check constraints for affected columns only

    jian he <jian.universality@gmail.com> — 2025-12-07T15:50:37Z

    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

  5. Re: UPDATE run check constraints for affected columns only

    jian he <jian.universality@gmail.com> — 2026-01-23T02:09:27Z

    hi.

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


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

  6. Re: UPDATE run check constraints for affected columns only

    jian he <jian.universality@gmail.com> — 2026-03-04T01:04:49Z

    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/

  7. Re: UPDATE run check constraints for affected columns only

    jian he <jian.universality@gmail.com> — 2026-03-05T03:12:04Z

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



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

  8. Re: UPDATE run check constraints for affected columns only

    jian he <jian.universality@gmail.com> — 2026-03-06T02:17:35Z

    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/

  9. Re: UPDATE run check constraints for affected columns only

    Florin Irion <irionr@gmail.com> — 2026-03-06T16:10:33Z

    Hi Jian,

Haritabh and I have been reviewing this patch. The core optimization
is sound — the BEFORE ROW UPDATE trigger guard, the generated column
handling via expand_generated_columns_in_expr + ExecGetAllUpdatedCols,
and the whole-row reference check are all correct.

We independently found the same MERGE issue you fixed in v6, where
ri_CheckConstraintExprs is shared between INSERT and UPDATE actions on
the same ResultRelInfo. Nice catch on the fix.

That said, I think v6's approach of disabling the optimization entirely
for MERGE is more conservative than necessary. A MERGE with only WHEN
MATCHED THEN UPDATE (no INSERT action) would still benefit from skipping
unaffected constraints, but v6 disables it for all MERGE operations.

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 */
```

Then in ExecRelCheck, select the appropriate array based on cmdtype:

```
ExprState **checkExprs;
checkExprs = (cmdtype == CMD_UPDATE)
    ? resultRelInfo->ri_CheckConstraintExprsU
    : resultRelInfo->ri_CheckConstraintExprsI;

if (checkExprs == NULL)
{
    Bitmapset  *updatedCols = NULL;

    if (cmdtype == CMD_UPDATE &&
        !(rel->trigdesc && rel->trigdesc->trig_update_before_row))
        updatedCols = ExecGetAllUpdatedCols(resultRelInfo, estate);

    /* ... alloc and populate checkExprs ... */

    if (cmdtype == CMD_UPDATE)
        resultRelInfo->ri_CheckConstraintExprsU = checkExprs;
    else
        resultRelInfo->ri_CheckConstraintExprsI = checkExprs;
}
```

This way INSERT always compiles and checks all constraints, UPDATE gets
the skip optimization even during MERGE, and both can safely coexist on
the same ResultRelInfo. The lazy-init cost for the second array only
applies when both code paths are actually taken, which matches the
generated-columns precedent. It also makes INSERT ON CONFLICT DO UPDATE
structurally safe rather than relying on INSERT always running first.

A few other items on v6:

1. The MERGE test case only tests MERGE with a single UPDATE action and
verifies the optimization is disabled. It doesn't test the actual
dangerous scenario — MERGE with both INSERT and UPDATE actions where
the INSERT row violates a constraint. Without that, a future refactor
could reintroduce the original bug without any test failing. Something
like:

```
CREATE TABLE merge_cc (id int PRIMARY KEY, a int, b int,
                     CONSTRAINT cc CHECK (a > 0));
INSERT INTO merge_cc VALUES (1, 10, 10);

MERGE INTO merge_cc t
  USING (VALUES (1, 99, 20), (2, -5, 30)) AS s(id, a, b)
    ON t.id = s.id
  WHEN MATCHED THEN UPDATE SET b = s.b
  WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.a, s.b);
  -- must ERROR on cc (a = -5), not silently succeed
```

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.

Cheers,
Florin

-- 
    * Florin Irion  *
*    https://www.enterprisedb.com <https://www.enterprisedb.com/>*

  10. Re: UPDATE run check constraints for affected columns only

    jian he <jian.universality@gmail.com> — 2026-03-07T09:46:23Z

    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/