pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #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