bug in stored generated column over domain with constraints.
jian he <jian.universality@gmail.com>
From: jian he <jian.universality@gmail.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2025-02-25T17:09:29Z
Lists: pgsql-hackers
Attachments
- v1-0001-fix-default-insertion-for-stored-generated-column.patch (text/x-patch) patch v1-0001
hi.
create domain d1 as int not null;
create domain d2 as int check (value > 1);
create domain d3 as int check (value is not null);
create table t0(b int, a d3 GENERATED ALWAYS as (b + 11) stored);
insert into t0 values (1, default);
ERROR: value for domain d3 violates check constraint "d3_check"
in the above example, a domain with check constraint not working as intended,
similarly, a domain with not-null constraint will also not work.
now table t0 can not insert any data, because of check constraint violation.
so i think this is a bug, (hope i didn't miss anything).
in ExecBuildProjectionInfo, we compile "values (1, default)" as
targetlist expression (CONST, COERCETODOMAIN)
Then in ExecResult, ExecProject, ExecInterpExpr we evaluate the
compiled expression;
we failed at ExecEvalConstraintCheck. we are acting like: ``null::d3``.
explain(costs off, verbose) insert into t0 values (1, default);
QUERY PLAN
----------------------------------------
Insert on public.t0
-> Result
Output: 1, NULL::integer
the plan is ``NULL::integer``, which will not fail, but ``NULL::d3`` will fail.
that means, the output plan is right. it's the execution wrong?
the main fix should be in rewriteTargetListIU.
UPDATE don't have this issue, since there is no Result node,
ExecComputeStoredGenerated will do the domain constraint check.
related:
https://git.postgresql.org/cgit/postgresql.git/commit/?id=0da39aa7667b06e16189d318f7850d559d446d52
Commits
-
Fix failure for generated column with a not-null domain constraint.
- f04e0faa3702 16.9 landed
- 97d671672393 15.13 landed
- 7c8728494077 18.0 landed
- 4604928edd72 14.18 landed
- 3c39c000c859 17.5 landed
-
Handle default NULL insertion a little better.
- 0da39aa7667b 18.0 cited