From 7c872849407730fa01e2c13b2d47483bc3ff6e7e Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 15 Apr 2025 12:08:34 -0400 Subject: Fix failure for generated column with a not-null domain constraint. If a GENERATED column is declared to have a domain data type where the domain's constraints disallow null values, INSERT commands failed because we built a targetlist that included coercing a null constant to the domain's type. The failure occurred even when the generated value would have been perfectly OK. This is adjacent to the issues fixed in 0da39aa76, but we didn't notice for lack of testing a domain with such a constraint. We aren't going to use the result of the targetlist entry for the generated column --- ExecComputeStoredGenerated will overwrite it. So it's not really necessary that it have the exact datatype of the generated column. This patch fixes the problem by changing the targetlist entry to be a null Const of the domain's base type, which should be sufficiently legal. (We do have to tweak ExecCheckPlanOutput to accept the situation, though.) This has been broken since we implemented generated columns. However, this patch only applies easily as far back as v14, partly because I (tgl) only carried 0da39aa76 back that far, but mostly because v14 significantly refactored the handling of INSERT/UPDATE targetlists. Given the lack of field complaints and the short remaining support lifetime of v13, I judge the cost-benefit ratio not good for devising a version that would work in v13. Reported-by: jian he Author: jian he Reviewed-by: Tom Lane Discussion: https://postgr.es/m/CACJufxG59tip2+9h=rEv-ykOFjt0cbsPVchhi0RTij8bABBA0Q@mail.gmail.com Backpatch-through: 14 --- src/test/regress/expected/generated_stored.out | 5 +++++ src/test/regress/expected/generated_virtual.out | 5 +++++ src/test/regress/sql/generated_stored.sql | 5 +++++ src/test/regress/sql/generated_virtual.sql | 5 +++++ 4 files changed, 20 insertions(+) (limited to 'src/test') diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out index 8cccd1d7fe9..16de30ab191 100644 --- a/src/test/regress/expected/generated_stored.out +++ b/src/test/regress/expected/generated_stored.out @@ -847,6 +847,11 @@ CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS A INSERT INTO gtest24r (a) VALUES (4); -- ok INSERT INTO gtest24r (a) VALUES (6); -- error ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check" +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 +ERROR: value for domain gtestdomainnn violates check constraint "gtestdomainnn_check" -- 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 26bbe1e9c31..6300e7c1d96 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -800,6 +800,11 @@ CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS A ERROR: virtual generated column "b" cannot have a domain type --INSERT INTO gtest24r (a) VALUES (4); -- ok --INSERT INTO gtest24r (a) VALUES (6); -- error +CREATE DOMAIN gtestdomainnn AS int CHECK (VALUE IS NOT NULL); +CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) VIRTUAL); +ERROR: virtual generated column "b" cannot have a domain type +--INSERT INTO gtest24nn (a) VALUES (4); -- ok +--INSERT INTO gtest24nn (a) VALUES (NULL); -- error -- 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 50e94e5c673..4ec155f2da9 100644 --- a/src/test/regress/sql/generated_stored.sql +++ b/src/test/regress/sql/generated_stored.sql @@ -419,6 +419,11 @@ CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS A INSERT INTO gtest24r (a) VALUES (4); -- ok INSERT INTO gtest24r (a) VALUES (6); -- error +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 + -- 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/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql index 13cfbd76859..b4eedeee2fb 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -453,6 +453,11 @@ CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS A --INSERT INTO gtest24r (a) VALUES (4); -- ok --INSERT INTO gtest24r (a) VALUES (6); -- error +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 + -- 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); -- cgit v1.2.3