On Wed, Jun 24, 2026 at 1:48 AM Peter Eisentraut <[email protected]> wrote:
>
> I don't understand why this proposed check is being done in the
> executor. It seems to me that it should be done in the parser, in
> transformForPortionOfClause(), where you check other properties of the
> for-portion-of target column. It is not possible to turn a normal
> column into a generated column, so once we have checked that the column
> exists and has the right type and is not generated, I don't think there
> is then any risk that that check becomes invalidated between parsing and
> execution.
We were worried about BEGIN ATOMIC functions in particular, but you're
right that there is no way to change an ordinary column to a GENERATED
column later, without dropping it. And the BEGIN ATOMIC function
records the dependency, so Postgres won't let you do that. (Actually
you *can* change an integer column to GENERATED AS IDENTITY, but I
don't think you will ever be able to use an integer column in FOR
PORTION OF.)
Here is v4 moving the check into analysis. This lets us give a nicer
error message in a couple cases (captured in the tests).
The test about BEGIN ATOMIC functions now shows that the analysis-time
check prevents you from defining the function.
Yours,
--
Paul ~{:-)
[email protected]
From 4ea775e87bf485239518172a7445cc2b24795511 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email protected]>
Date: Thu, 14 May 2026 09:56:24 -0700
Subject: [PATCH v4] Forbid GENERATED columns in FOR PORTION OF
With VIRTUAL columns there is no column to assign to, and we shouldn't
assign directly to STORED columns either. (Once we have PERIODs, we will
allow a STORED column here, but we will assign to its start/end inputs.)
It is safe to do this check in parse analysis, because an ordinary column cannot
be converted to a GENERATED column later. Technically columns with integer types
can be converted to identity columns, but those can't be used in FOR PORTION OF.
Even BEGIN ATOMIC SQL functions, which parse the statement far in advance of
executing it, are safe.
Discussion: https://www.postgresql.org/message-id/agOOykf2HV26yVfU%40nathan
---
src/backend/parser/analyze.c | 14 ++++
src/test/regress/expected/for_portion_of.out | 67 ++++++++++++++++++++
src/test/regress/sql/for_portion_of.sql | 46 ++++++++++++++
3 files changed, 127 insertions(+)
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 93fa66ae57c..69f84c5f3d8 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1354,6 +1354,20 @@ transformForPortionOfClause(ParseState *pstate,
parser_errposition(pstate, forPortionOf->location)));
attr = TupleDescAttr(targetrel->rd_att, range_attno - 1);
+ /*
+ * Reject generated columns. We can't write to a virtual generated column,
+ * and a stored generated column should be written by its own expression.
+ *
+ * XXX: We plan to implement PERIODs as stored generated columns, so later
+ * we will loosen this restriction if the column belongs to a PERIOD.
+ */
+ if (attr->attgenerated)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use generated column \"%s\" in FOR PORTION OF",
+ forPortionOf->range_name),
+ parser_errposition(pstate, forPortionOf->location)));
+
attbasetype = getBaseType(attr->atttypid);
rangeVar = makeVar(rtindex,
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
index 43408972117..86ec7381e4b 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -2208,6 +2208,73 @@ SELECT * FROM fpo_rule ORDER BY f1;
(2 rows)
DROP TABLE fpo_rule;
+-- UPDATE/DELETE FOR PORTION OF on a GENERATED VIRTUAL range column:
+CREATE TABLE fpo_gen_virtual (
+ a int,
+ b int4range GENERATED ALWAYS AS (int4range(a, a + 1)) VIRTUAL
+);
+INSERT INTO fpo_gen_virtual VALUES (1);
+DELETE FROM fpo_gen_virtual FOR PORTION OF b FROM 1 TO 2; -- fails
+ERROR: cannot use generated column "b" in FOR PORTION OF
+LINE 1: DELETE FROM fpo_gen_virtual FOR PORTION OF b FROM 1 TO 2;
+ ^
+UPDATE fpo_gen_virtual FOR PORTION OF b FROM 1 TO 2 SET a = 5; -- fails
+ERROR: cannot use generated column "b" in FOR PORTION OF
+LINE 1: UPDATE fpo_gen_virtual FOR PORTION OF b FROM 1 TO 2 SET a = ...
+ ^
+DROP TABLE fpo_gen_virtual;
+-- UPDATE/DELETE FOR PORTION OF on a GENERATED STORED range column:
+CREATE TABLE fpo_gen_stored (
+ a int,
+ b int4range GENERATED ALWAYS AS (int4range(a, a + 1)) STORED
+);
+INSERT INTO fpo_gen_stored VALUES (1);
+DELETE FROM fpo_gen_stored FOR PORTION OF b FROM 1 TO 2; -- fails
+ERROR: cannot use generated column "b" in FOR PORTION OF
+LINE 1: DELETE FROM fpo_gen_stored FOR PORTION OF b FROM 1 TO 2;
+ ^
+UPDATE fpo_gen_stored FOR PORTION OF b FROM 1 TO 2 SET a = 5; -- fails
+ERROR: cannot use generated column "b" in FOR PORTION OF
+LINE 1: UPDATE fpo_gen_stored FOR PORTION OF b FROM 1 TO 2 SET a = 5...
+ ^
+DROP TABLE fpo_gen_stored;
+-- A new-style SQL function is parsed at CREATE FUNCTION time, but our
+-- generated-column check is in the executor, so it sees the column's
+-- current attgenerated when the function actually runs.
+CREATE TABLE fpo_func_test (
+ a int,
+ b int4range GENERATED ALWAYS AS (int4range(a, a + 1)) STORED
+);
+INSERT INTO fpo_func_test VALUES (1);
+-- Definition fails because b is a generated column.
+CREATE FUNCTION fpo_delete() RETURNS void
+ LANGUAGE SQL
+ BEGIN ATOMIC
+ DELETE FROM fpo_func_test FOR PORTION OF b FROM 1 TO 2;
+ END;
+ERROR: cannot use generated column "b" in FOR PORTION OF
+LINE 4: DELETE FROM fpo_func_test FOR PORTION OF b FROM 1 TO 2;
+ ^
+-- Drop the generation expression and the same function now succeeds.
+ALTER TABLE fpo_func_test ALTER COLUMN b DROP EXPRESSION;
+CREATE FUNCTION fpo_delete() RETURNS void
+ LANGUAGE SQL
+ BEGIN ATOMIC
+ DELETE FROM fpo_func_test FOR PORTION OF b FROM 1 TO 2;
+ END;
+SELECT fpo_delete();
+ fpo_delete
+------------
+
+(1 row)
+
+TABLE fpo_func_test ORDER BY a, b;
+ a | b
+---+---
+(0 rows)
+
+DROP FUNCTION fpo_delete();
+DROP TABLE fpo_func_test;
-- UPDATE/DELETE FOR PORTION OF with table inheritance
-- Leftover rows must stay in the child table, preserving child-specific columns.
CREATE TABLE fpo_inh_parent (
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index 7b08f8cf45e..676fa4814ce 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -1448,6 +1448,52 @@ SELECT * FROM fpo_rule ORDER BY f1;
DROP TABLE fpo_rule;
+-- UPDATE/DELETE FOR PORTION OF on a GENERATED VIRTUAL range column:
+CREATE TABLE fpo_gen_virtual (
+ a int,
+ b int4range GENERATED ALWAYS AS (int4range(a, a + 1)) VIRTUAL
+);
+INSERT INTO fpo_gen_virtual VALUES (1);
+DELETE FROM fpo_gen_virtual FOR PORTION OF b FROM 1 TO 2; -- fails
+UPDATE fpo_gen_virtual FOR PORTION OF b FROM 1 TO 2 SET a = 5; -- fails
+DROP TABLE fpo_gen_virtual;
+
+-- UPDATE/DELETE FOR PORTION OF on a GENERATED STORED range column:
+CREATE TABLE fpo_gen_stored (
+ a int,
+ b int4range GENERATED ALWAYS AS (int4range(a, a + 1)) STORED
+);
+INSERT INTO fpo_gen_stored VALUES (1);
+DELETE FROM fpo_gen_stored FOR PORTION OF b FROM 1 TO 2; -- fails
+UPDATE fpo_gen_stored FOR PORTION OF b FROM 1 TO 2 SET a = 5; -- fails
+DROP TABLE fpo_gen_stored;
+
+-- A new-style SQL function is parsed at CREATE FUNCTION time, but our
+-- generated-column check is in the executor, so it sees the column's
+-- current attgenerated when the function actually runs.
+CREATE TABLE fpo_func_test (
+ a int,
+ b int4range GENERATED ALWAYS AS (int4range(a, a + 1)) STORED
+);
+INSERT INTO fpo_func_test VALUES (1);
+-- Definition fails because b is a generated column.
+CREATE FUNCTION fpo_delete() RETURNS void
+ LANGUAGE SQL
+ BEGIN ATOMIC
+ DELETE FROM fpo_func_test FOR PORTION OF b FROM 1 TO 2;
+ END;
+-- Drop the generation expression and the same function now succeeds.
+ALTER TABLE fpo_func_test ALTER COLUMN b DROP EXPRESSION;
+CREATE FUNCTION fpo_delete() RETURNS void
+ LANGUAGE SQL
+ BEGIN ATOMIC
+ DELETE FROM fpo_func_test FOR PORTION OF b FROM 1 TO 2;
+ END;
+SELECT fpo_delete();
+TABLE fpo_func_test ORDER BY a, b;
+DROP FUNCTION fpo_delete();
+DROP TABLE fpo_func_test;
+
-- UPDATE/DELETE FOR PORTION OF with table inheritance
-- Leftover rows must stay in the child table, preserving child-specific columns.
CREATE TABLE fpo_inh_parent (
--
2.47.3
