pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19907: INSERT through partitioned table fails with domain-typed generated column
2+ messages / 2 participants
[nested] [flat]

* BUG #19907: INSERT through partitioned table fails with domain-typed generated column
@ 2026-11-16 12:24  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2026-11-16 12:24 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: hello@bjornpagen.com

The following bug has been logged on the website:

Bug reference:      19907
Logged by:          Bjorn Pagen
Email address:      hello@bjornpagen.com
PostgreSQL version: Unsupported/Unknown
Operating system:   macOS 27 (arm64)
Description:        

On master (7fc2f04233), an INSERT through a partitioned table fails if
a CHECK constraint reads a stored generated column of a domain type:

    CREATE DOMAIN d AS int;
    CREATE TABLE p (k int, g d GENERATED ALWAYS AS (k * 2) STORED CHECK (g >
0))
        PARTITION BY RANGE (k);
    CREATE TABLE p1 PARTITION OF p FOR VALUES FROM (1) TO (10);
    INSERT INTO p (k) VALUES (1);
    ERROR:  attribute 2 of type record has wrong type
    DETAIL:  Table has type integer, but query expects d.

The same happens when an index expression or predicate reads g.  A
plain table, or an INSERT directly into p1, works.

This comes from 7c87284940: expand_insert_targetlist() now fills g
with a NULL of the domain's base type, and since the tuple is routed
without being copied into a slot of the table's rowtype, the CHECK
sees an integer where it expects d.  With that commit reverted, the
INSERT works.  I'll follow up with a patch.







^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #19907: INSERT through partitioned table fails with domain-typed generated column
@ 2026-11-16 12:38  Bjorn Pagen <hello@bjornpagen.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Bjorn Pagen @ 2026-11-16 12:38 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org

Here's a patch. It keeps the bare NULL Const but labels it with the
column's own type, so the domain's constraints are still not applied
to it.

--
Bjorn Pagen

On Mon, Nov 16, 2026 06:25 AM, PG Bug reporting form <noreply@postgresql.org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference:      19907
> Logged by:          Bjorn Pagen
> Email address:      hello@bjornpagen.com
> PostgreSQL version: Unsupported/Unknown
> Operating system:   macOS 27 (arm64)
> Description:
>
> On master (7fc2f04233), an INSERT through a partitioned table fails if
> a CHECK constraint reads a stored generated column of a domain type:
>
>     CREATE DOMAIN d AS int;
>     CREATE TABLE p (k int, g d GENERATED ALWAYS AS (k * 2) STORED CHECK (g
> >
> 0))
>         PARTITION BY RANGE (k);
>     CREATE TABLE p1 PARTITION OF p FOR VALUES FROM (1) TO (10);
>     INSERT INTO p (k) VALUES (1);
>     ERROR:  attribute 2 of type record has wrong type
>     DETAIL:  Table has type integer, but query expects d.
>
> The same happens when an index expression or predicate reads g.  A
> plain table, or an INSERT directly into p1, works.
>
> This comes from 7c87284940: expand_insert_targetlist() now fills g
> with a NULL of the domain's base type, and since the tuple is routed
> without being copied into a slot of the table's rowtype, the CHECK
> sees an integer where it expects d.  With that commit reverted, the
> INSERT works.  I'll follow up with a patch.
>
>
>

Attachments:

v1-0001-Give-a-generated-column-s-placeholder-NULL-the-co.patch (text/x-patch, 7.8K) download From 4a16f9e7502fdb743cfb435375c46042da602b9f Mon Sep 17 00:00:00 2001 From: Bjorn Pagen <hello@bjornpagen.com> Date: Mon, 16 Nov 2026 00:57:43 -0600 Subject: [PATCH v1] Give a generated column's placeholder NULL the column's own type. Since 7c87284940, expand_insert_targetlist() fills a generated column omitted from an INSERT with a NULL of the domain's base type, so that a NOT NULL domain is not checked against a value that will be overwritten anyway. But when the INSERT goes through a partitioned table, the plan's tuple is passed to the partition without being copied into a slot of the table's rowtype, so a CHECK constraint or index expression that reads the column failed with "attribute N of type record has wrong type". Label the NULL with the column's own type instead. It still has no CoerceToDomain above it, so the domain's constraints are still not applied. --- src/backend/executor/nodeModifyTable.c | 5 ++--- src/backend/optimizer/prep/preptlist.c | 18 +++++++----------- src/test/regress/expected/generated_stored.out | 4 ++++ .../regress/expected/generated_virtual.out | 5 +++++ src/test/regress/sql/generated_stored.sql | 4 ++++ src/test/regress/sql/generated_virtual.sql | 4 ++++ 6 files changed, 26 insertions(+), 14 deletions(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 966094b6a3..3c68b35614 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -253,9 +253,8 @@ ExecCheckPlanOutput(Relation resultRel, List *targetList) { /* * For a generated column, the planner will have inserted a null - * of the column's base type (to avoid possibly failing on domain - * not-null constraints). It doesn't seem worth insisting on that - * exact type though, since a null value is type-independent. As + * of the column's type, without applying domain constraints (to + * avoid possibly failing on domain not-null constraints). As * above, just insist on *some* NULL constant. */ if (!IsA(tle->expr, Const) || diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c index 1647066a13..ce28011b85 100644 --- a/src/backend/optimizer/prep/preptlist.c +++ b/src/backend/optimizer/prep/preptlist.c @@ -46,7 +46,6 @@ #include "optimizer/tlist.h" #include "parser/parse_coerce.h" #include "parser/parsetree.h" -#include "utils/lsyscache.h" #include "utils/rel.h" static List *expand_insert_targetlist(PlannerInfo *root, List *tlist, @@ -437,9 +436,10 @@ expand_insert_targetlist(PlannerInfo *root, List *tlist, Relation rel) * Another exception is that if the column is generated, the value * we produce here will be ignored, and we don't want to risk * throwing an error. So in that case we *don't* want to apply - * domain constraints, so we must produce a NULL of the base type. - * Again, code comparing the finished plan to the target relation - * must account for this. + * domain constraints. We produce a bare NULL constant, but still + * label it with the column's type, since the executor may + * evaluate the relation's constraints on the plan's output tuple + * as is. */ Node *new_expr; @@ -456,13 +456,9 @@ expand_insert_targetlist(PlannerInfo *root, List *tlist, Relation rel) } else if (att_tup->attgenerated) { - /* Generated column, insert a NULL of the base type */ - Oid baseTypeId = att_tup->atttypid; - int32 baseTypeMod = att_tup->atttypmod; - - baseTypeId = getBaseTypeAndTypmod(baseTypeId, &baseTypeMod); - new_expr = (Node *) makeConst(baseTypeId, - baseTypeMod, + /* Generated column, insert a NULL without domain checks */ + new_expr = (Node *) makeConst(att_tup->atttypid, + att_tup->atttypmod, att_tup->attcollation, att_tup->attlen, (Datum) 0, diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out index d7a18e478f..23bbc6e860 100644 --- a/src/test/regress/expected/generated_stored.out +++ b/src/test/regress/expected/generated_stored.out @@ -952,6 +952,10 @@ CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) STORE INSERT INTO gtest24nn (a) VALUES (4); -- ok INSERT INTO gtest24nn (a) VALUES (NULL); -- error ERROR: value for domain gtestdomainnn violates check constraint "gtestdomainnn_check" +-- a constraint reading the column, through a partitioned table +CREATE TABLE gtest24p (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) STORED CHECK (b < 100)) PARTITION BY RANGE (a); +CREATE TABLE gtest24p_1 PARTITION OF gtest24p FOR VALUES FROM (1) TO (10); +INSERT INTO gtest24p (a) VALUES (4); -- ok -- typed tables (currently not supported) CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint); CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) STORED); diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index b14e10fb5a..cca5e0bfa6 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -892,6 +892,11 @@ CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) VIRTU ERROR: virtual generated column "b" cannot have a domain type --INSERT INTO gtest24nn (a) VALUES (4); -- ok --INSERT INTO gtest24nn (a) VALUES (NULL); -- error +-- a constraint reading the column, through a partitioned table +CREATE TABLE gtest24p (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) VIRTUAL CHECK (b < 100)) PARTITION BY RANGE (a); +ERROR: virtual generated column "b" cannot have a domain type +--CREATE TABLE gtest24p_1 PARTITION OF gtest24p FOR VALUES FROM (1) TO (10); +--INSERT INTO gtest24p (a) VALUES (4); -- ok -- typed tables (currently not supported) CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint); CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) VIRTUAL); diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql index ef639ec6a1..92698bfc08 100644 --- a/src/test/regress/sql/generated_stored.sql +++ b/src/test/regress/sql/generated_stored.sql @@ -502,6 +502,10 @@ CREATE DOMAIN gtestdomainnn AS int CHECK (VALUE IS NOT NULL); CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) STORED); INSERT INTO gtest24nn (a) VALUES (4); -- ok INSERT INTO gtest24nn (a) VALUES (NULL); -- error +-- a constraint reading the column, through a partitioned table +CREATE TABLE gtest24p (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) STORED CHECK (b < 100)) PARTITION BY RANGE (a); +CREATE TABLE gtest24p_1 PARTITION OF gtest24p FOR VALUES FROM (1) TO (10); +INSERT INTO gtest24p (a) VALUES (4); -- ok -- typed tables (currently not supported) CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint); diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql index e62880f941..bd32c1b612 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -510,6 +510,10 @@ CREATE DOMAIN gtestdomainnn AS int CHECK (VALUE IS NOT NULL); CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) VIRTUAL); --INSERT INTO gtest24nn (a) VALUES (4); -- ok --INSERT INTO gtest24nn (a) VALUES (NULL); -- error +-- a constraint reading the column, through a partitioned table +CREATE TABLE gtest24p (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) VIRTUAL CHECK (b < 100)) PARTITION BY RANGE (a); +--CREATE TABLE gtest24p_1 PARTITION OF gtest24p FOR VALUES FROM (1) TO (10); +--INSERT INTO gtest24p (a) VALUES (4); -- ok -- typed tables (currently not supported) CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint); -- 2.54.0 (Apple Git-157)
^ permalink raw reply [nested|flat] 2+ messages in thread


end of thread, other threads:[~2026-11-16 12:38 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-16 12:24 BUG #19907: INSERT through partitioned table fails with domain-typed generated column PG Bug reporting form <noreply@postgresql.org>
2026-11-16 12:38 ` Bjorn Pagen <hello@bjornpagen.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox