public inbox for [email protected]
help / color / mirror / Atom feedFrom: Paul A Jungwirth <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: jian he <[email protected]>
Cc: Chao Li <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: SATYANARAYANA NARLAPURAM <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: FOR PORTION OF does not recompute GENERATED STORED columns that depend on the range column
Date: Tue, 12 May 2026 17:05:03 -0700
Message-ID: <CA+renyWqeWxSUoohRQ4htfSLCcDVsZ=XwVR7F8-e9GXeH_O13w@mail.gmail.com> (raw)
In-Reply-To: <agOOykf2HV26yVfU@nathan>
References: <[email protected]>
<CA+renyU6rNkiNGreMyQ7pU_F6-5RND5jchHbECH4NoRO7W0Q-Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CA+renyUBLAynaj0BKhajB6F=sLuitQkjT+_sOt5HBSRn82iQsw@mail.gmail.com>
<[email protected]>
<CA+renyUTzwAMar173cbbxJypChp7s=txxgB+LYJQ5oRZ3a5hYQ@mail.gmail.com>
<CACJufxFHe9iq50RfgyU3T1_CrB+NfdrjdBUp6NFNtb=Dy5Lf-g@mail.gmail.com>
<CA+renyX4UaO7T=sC2UcpKUwS2yd7zt3XxOm1MgXv5W82ucbk0w@mail.gmail.com>
<agOOykf2HV26yVfU@nathan>
On Tue, May 12, 2026 at 1:34 PM Nathan Bossart <[email protected]> wrote:
>
> FOR PORTION OF doesn't seem to work well with virtual generated columns,
> either. The following example seg-faults on my machine:
>
> create table t (a int, b int4range generated always as (int4range(a, a + 1)) virtual);
> insert into t values (1);
> delete from t for portion of b from 1 to 2;
Thanks for catching this!
Here is a patch forbidding both STORED and VIRTUAL columns here. There
is a follow-up patch (not for v19) to add SQL:2011 PERIODs, which will
be based on STORED columns, so we will eventually allow those (if they
belong to a PERIOD), but it seems right to forbid them for now.
I put the check in the analysis phase to match what we have already,
but based on [1] that is apparently premature. I think I'd like to
move all those things together in a single commit though.
I did experiment with putting just this check in ExecInitModifyTable.
But (1) the planner will already reject the UPDATE case with a
different error message, and (2) it doesn't really improve anything,
since rangeVar gets looked up during analysis anyway (until we address
the rest of [1]).
[1] https://www.postgresql.org/message-id/626986.1776785090%40sss.pgh.pa.us
Yours,
--
Paul ~{:-)
[email protected]
Attachments:
[text/x-patch] v1-0001-Forbid-GENERATED-columns-in-FOR-PORTION-OF.patch (4.6K, 2-v1-0001-Forbid-GENERATED-columns-in-FOR-PORTION-OF.patch)
download | inline diff:
From f2bbcafc8fe7d1dbdf3c6fd49a34975fca9d804d Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email protected]>
Date: Tue, 12 May 2026 16:33:42 -0700
Subject: [PATCH v1] 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.)
Discussion: https://postgr.es/m/agOOykf2HV26yVfU%40nathan
---
src/backend/parser/analyze.c | 13 +++++++++
src/test/regress/expected/for_portion_of.out | 30 ++++++++++++++++++++
src/test/regress/sql/for_portion_of.sql | 21 ++++++++++++++
3 files changed, 64 insertions(+)
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index ffcf25a6be7..12b64b01e18 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1354,6 +1354,19 @@ 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 0c0a205c44b..9979d816972 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -2152,4 +2152,34 @@ 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;
RESET datestyle;
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index fd79a9b78e7..81bdeb8d13a 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -1398,4 +1398,25 @@ 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;
+
+
RESET datestyle;
--
2.47.3
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: FOR PORTION OF does not recompute GENERATED STORED columns that depend on the range column
In-Reply-To: <CA+renyWqeWxSUoohRQ4htfSLCcDVsZ=XwVR7F8-e9GXeH_O13w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox