From 1e836fa15ff30c22de9b555196da3e8fbb5a2ad7 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 9 Sep 2023 14:40:15 -0400 Subject: [PATCH v1 2/3] Preprocess column GENERATED expressions before checking mutability. The previous coding skipped the preprocessing step and thus had (at least) two failure modes: it could fail to notice the use of a volatile function default-argument expression, or it could reject a polymorphic function that is actually immutable on the datatype of interest. Per bug #18097 from Jim Keener. Back-patch to v12 where we added generated columns. Discussion: https://postgr.es/m/18097-ebb179674f22932f@postgresql.org --- src/backend/catalog/heap.c | 4 +++- src/test/regress/expected/generated.out | 3 +++ src/test/regress/sql/generated.sql | 3 +++ 3 files changed, 9 insertions(+), 1 deletion(-) diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index b42711f574..9b7544e9b7 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -3087,9 +3087,11 @@ cookDefault(ParseState *pstate, if (attgenerated) { + /* Disallow refs to other generated columns */ check_nested_generated(pstate, expr); - if (contain_mutable_functions(expr)) + /* Disallow mutable functions */ + if (ContainMutableFunctions((Expr *) expr)) ereport(ERROR, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), errmsg("generation expression is not immutable"))); diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out index dc97ed3fe0..a2f38d0f50 100644 --- a/src/test/regress/expected/generated.out +++ b/src/test/regress/expected/generated.out @@ -61,6 +61,9 @@ LINE 1: ..._3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) STO... -- generation expression must be immutable CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) STORED); ERROR: generation expression is not immutable +-- ... but be sure that the immutability test is accurate +CREATE TABLE gtest2 (a int, b text GENERATED ALWAYS AS (a || ' sec') STORED); +DROP TABLE gtest2; -- cannot have default/identity and generated CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2) STORED); ERROR: both default and generation expression specified for column "b" of table "gtest_err_5a" diff --git a/src/test/regress/sql/generated.sql b/src/test/regress/sql/generated.sql index 8ddecf0cc3..298f6b3aa8 100644 --- a/src/test/regress/sql/generated.sql +++ b/src/test/regress/sql/generated.sql @@ -26,6 +26,9 @@ CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) S -- generation expression must be immutable CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) STORED); +-- ... but be sure that the immutability test is accurate +CREATE TABLE gtest2 (a int, b text GENERATED ALWAYS AS (a || ' sec') STORED); +DROP TABLE gtest2; -- cannot have default/identity and generated CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2) STORED); -- 2.39.3