public inbox for [email protected]
help / color / mirror / Atom feedFrom: Paul A Jungwirth <[email protected]>
To: Chao Li <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: jian he <[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: Mon, 25 May 2026 15:23:20 -0700
Message-ID: <CA+renyXyLfvtvVv--hGWGTgzFP=-+dPLy4RWvEmioAPyJMM+uw@mail.gmail.com> (raw)
In-Reply-To: <CA+renyUTzwAMar173cbbxJypChp7s=txxgB+LYJQ5oRZ3a5hYQ@mail.gmail.com>
References: <CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=hM0g@mail.gmail.com>
<CACJufxGreOtA-S-qeHyS5iSSsj5zZX0W3Rf8FxbyL+SVXFjLYw@mail.gmail.com>
<CAHg+QDeGLfz8YSCChjqrxaVSrz9AnMA0NrmsNogLqeGgCt7-wg@mail.gmail.com>
<CA+renyWD+XXifwswE74vhjooqbiVKu4qVhLvpMcUQBzrjVjT7A@mail.gmail.com>
<CACJufxHYntqy2fo9CFWDDrqKjcMK8DGRM3kse4YnXYnPYq2Hiw@mail.gmail.com>
<CA+renyVp4rgj8x0ERXRkZp223eyBZ_XZr2RVCXvjzKBhTtS6Yw@mail.gmail.com>
<CACJufxEkomKYmWgqXJmQr_qS+z=BZ3w801eh7Z7ekh-3oHXxHQ@mail.gmail.com>
<CA+renyWk7kVsZJPZKzN95mYkO7S=hDUx=+fUPtbg9qFqeepCpg@mail.gmail.com>
<CACJufxHai+HB1gkNqVEHe4oKyUmXfAagWBYAWXYKy8hyMV3RxA@mail.gmail.com>
<CA+renyUBQdhnYxfPay+dxFs6BU1-fnEQskT0r-3dQ2v-ZnmZzg@mail.gmail.com>
<CA+renyVZLYSghHb_85w0pUBG0KNGKTwciFTKBK5--rpHUM+VdA@mail.gmail.com>
<[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>
On Fri, May 8, 2026 at 8:25 AM Paul A Jungwirth
<[email protected]> wrote:
>
> On Fri, May 8, 2026 at 12:10 AM Chao Li <[email protected]> wrote:
> > > <v11-0001-Fix-FOR-PORTION-OF-column-dependency-tracking.patch><v11-0002-Fix-FOR-PORTION-OF-with-partitions-and-inheritan.patch>
> >
> > Thanks for updating the patch and making the separation. After reading v11, I still have a few comments for 0001.
> . . .
>
> v12 attached.
After discussing this as PGConf.dev, Peter and I agreed that we
*should* be checking for UPDATE permission on the application-time
column. So we need to add the attno to updatedCols on the
RTEPermissionInfo. That is great, because it fixes GENERATED column
dependency tracking and also UPDATE OF triggers, without the
complexity of changing ExecGetUpdatedCols.
Note we still do not require INSERT permission for the temporal
leftovers, which is what the SQL Standard says and also makes sense
semantically, since those leftovers represent already-existing
history.
No RLS changes are needed because RLS policies aren't checked based on
*column* changes.
We still need a fix for non-partitioning inheritance, but the patch
becomes a lot simpler.
Here is a patch adding the application-time column to updatedCols,
along with the tests we've written for GENERATED columns and UPDATE OF
triggers.
I'll submit a patch for inheritance on that thread.
--
Paul ~{:-)
[email protected]
Attachments:
[text/x-patch] v13-0001-Require-UPDATE-permission-on-FOR-PORTION-OF-colu.patch (24.1K, 2-v13-0001-Require-UPDATE-permission-on-FOR-PORTION-OF-colu.patch)
download | inline diff:
From 28d10700825a5ab84542d22573a55acd7a94399f Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email protected]>
Date: Mon, 25 May 2026 08:58:33 -0700
Subject: [PATCH v13] Require UPDATE permission on FOR PORTION OF column
It seems like the SQL standard does require this after all, and it makes sense
because these columns get changed. (This is not to be confused with *not*
requiring INSERT permission to add the temporal leftovers.)
Adding the column to RTEPermissionInfo->updatedCols also fixes a couple
outstanding bugs from other (non-permission) features using that bitmapset to
detect changes: GENERATED columns and UPDATE OF triggers. I've included test
cases to exercise those scenarios, including on partitioned tables.
---
src/backend/parser/analyze.c | 12 +-
src/test/regress/expected/for_portion_of.out | 190 ++++++++++++++-----
src/test/regress/expected/privileges.out | 12 +-
src/test/regress/sql/for_portion_of.sql | 76 +++++++-
src/test/regress/sql/privileges.sql | 10 +-
5 files changed, 241 insertions(+), 59 deletions(-)
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index ffcf25a6be7..03753b1c5a7 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1549,6 +1549,7 @@ transformForPortionOfClause(ParseState *pstate,
List *funcArgs;
Node *rangeTLEExpr;
TargetEntry *tle;
+ RTEPermissionInfo *target_perminfo = pstate->p_target_nsitem->p_perminfo;
/*
* Whatever operator is used for intersect by temporal foreign keys,
@@ -1598,14 +1599,9 @@ transformForPortionOfClause(ParseState *pstate,
forPortionOf->range_name, false);
result->rangeTargetList = lappend(result->rangeTargetList, tle);
- /*
- * The range column will change, but you don't need UPDATE permission
- * on it, so we don't add to updatedCols here. XXX: If
- * https://www.postgresql.org/message-id/CACJufxEtY1hdLcx%3DFhnqp-ERcV1PhbvELG5COy_CZjoEW76ZPQ%40mail.gmail.com
- * is merged (only validate CHECK constraints if they depend on one of
- * the columns being UPDATEd), we need to make sure that code knows
- * that we are updating the application-time column.
- */
+ /* Mark the range column as requiring update permissions */
+ target_perminfo->updatedCols = bms_add_member(target_perminfo->updatedCols,
+ range_attno - FirstLowInvalidHeapAttributeNumber);
}
else
result->rangeTargetList = NIL;
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
index 0c0a205c44b..fe08186bc5b 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -1365,6 +1365,9 @@ $$;
CREATE TRIGGER fpo_before_stmt
BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_before_stmt1
+ BEFORE UPDATE OF valid_at ON for_portion_of_test
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
CREATE TRIGGER fpo_after_insert_stmt
AFTER INSERT ON for_portion_of_test
FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
@@ -1378,6 +1381,9 @@ CREATE TRIGGER fpo_after_delete_stmt
CREATE TRIGGER fpo_before_row
BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_before_row1
+ BEFORE UPDATE OF valid_at ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
CREATE TRIGGER fpo_after_insert_row
AFTER INSERT ON for_portion_of_test
FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
@@ -1394,9 +1400,15 @@ UPDATE for_portion_of_test
NOTICE: fpo_before_stmt: BEFORE UPDATE STATEMENT:
NOTICE: old: <NULL>
NOTICE: new: <NULL>
+NOTICE: fpo_before_stmt1: BEFORE UPDATE STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
NOTICE: fpo_before_row: BEFORE UPDATE ROW:
NOTICE: old: [2019-01-01,2030-01-01)
NOTICE: new: [2021-01-01,2022-01-01)
+NOTICE: fpo_before_row1: BEFORE UPDATE ROW:
+NOTICE: old: [2019-01-01,2030-01-01)
+NOTICE: new: [2021-01-01,2022-01-01)
NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT:
NOTICE: old: <NULL>
NOTICE: new: <NULL>
@@ -1986,6 +1998,7 @@ SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at;
DROP TABLE for_portion_of_test2;
DROP TYPE mydaterange;
-- Test FOR PORTION OF against a partitioned table.
+-- Include a GENERATED STORED column to test updatedCols column mapping.
-- temporal_partitioned_1 has the same attnums as the root
-- temporal_partitioned_3 has the different attnums from the root
-- temporal_partitioned_5 has the different attnums too, but reversed
@@ -1993,29 +2006,34 @@ CREATE TABLE temporal_partitioned (
id int4range,
valid_at daterange,
name text,
+ range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED,
CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
) PARTITION BY LIST (id);
CREATE TABLE temporal_partitioned_1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)');
CREATE TABLE temporal_partitioned_3 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)');
CREATE TABLE temporal_partitioned_5 PARTITION OF temporal_partitioned FOR VALUES IN ('[5,6)', '[6,7)');
ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_3;
-ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at;
+ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at CASCADE;
+NOTICE: drop cascades to column range_len of table temporal_partitioned_3
ALTER TABLE temporal_partitioned_3 ADD COLUMN id int4range NOT NULL, ADD COLUMN valid_at daterange NOT NULL;
+ALTER TABLE temporal_partitioned_3 ADD COLUMN range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED;
ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_3 FOR VALUES IN ('[3,4)', '[4,5)');
ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_5;
-ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at;
+ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at CASCADE;
+NOTICE: drop cascades to column range_len of table temporal_partitioned_5
ALTER TABLE temporal_partitioned_5 ADD COLUMN valid_at daterange NOT NULL, ADD COLUMN id int4range NOT NULL;
+ALTER TABLE temporal_partitioned_5 ADD COLUMN range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED;
ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_5 FOR VALUES IN ('[5,6)', '[6,7)');
INSERT INTO temporal_partitioned (id, valid_at, name) VALUES
('[1,2)', daterange('2000-01-01', '2010-01-01'), 'one'),
('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'),
('[5,6)', daterange('2000-01-01', '2010-01-01'), 'five');
SELECT * FROM temporal_partitioned;
- id | valid_at | name
--------+-------------------------+-------
- [1,2) | [2000-01-01,2010-01-01) | one
- [3,4) | [2000-01-01,2010-01-01) | three
- [5,6) | [2000-01-01,2010-01-01) | five
+ id | valid_at | name | range_len
+-------+-------------------------+-------+-----------
+ [1,2) | [2000-01-01,2010-01-01) | one | 3653
+ [3,4) | [2000-01-01,2010-01-01) | three | 3653
+ [5,6) | [2000-01-01,2010-01-01) | five | 3653
(3 rows)
-- Update without moving within partition 1
@@ -2046,54 +2064,54 @@ UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-0
id = '[3,4)'
WHERE id = '[5,6)';
-- Update all partitions at once (each with leftovers)
-SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
- id | valid_at | name
--------+-------------------------+---------
- [1,2) | [2000-01-01,2000-03-01) | one
- [1,2) | [2000-03-01,2000-04-01) | one^1
- [1,2) | [2000-04-01,2000-06-01) | one
- [1,2) | [2000-07-01,2010-01-01) | one
- [2,3) | [2000-06-01,2000-07-01) | three^2
- [3,4) | [2000-01-01,2000-03-01) | three
- [3,4) | [2000-03-01,2000-04-01) | three^1
- [3,4) | [2000-04-01,2000-06-01) | three
- [3,4) | [2000-06-01,2000-07-01) | five^2
- [3,4) | [2000-07-01,2010-01-01) | three
- [4,5) | [2000-06-01,2000-07-01) | one^2
- [5,6) | [2000-01-01,2000-03-01) | five
- [5,6) | [2000-03-01,2000-04-01) | five^1
- [5,6) | [2000-04-01,2000-06-01) | five
- [5,6) | [2000-07-01,2010-01-01) | five
+SELECT *, upper(valid_at) - lower(valid_at) FROM temporal_partitioned ORDER BY id, valid_at;
+ id | valid_at | name | range_len | ?column?
+-------+-------------------------+---------+-----------+----------
+ [1,2) | [2000-01-01,2000-03-01) | one | 60 | 60
+ [1,2) | [2000-03-01,2000-04-01) | one^1 | 31 | 31
+ [1,2) | [2000-04-01,2000-06-01) | one | 61 | 61
+ [1,2) | [2000-07-01,2010-01-01) | one | 3471 | 3471
+ [2,3) | [2000-06-01,2000-07-01) | three^2 | 30 | 30
+ [3,4) | [2000-01-01,2000-03-01) | three | 60 | 60
+ [3,4) | [2000-03-01,2000-04-01) | three^1 | 31 | 31
+ [3,4) | [2000-04-01,2000-06-01) | three | 61 | 61
+ [3,4) | [2000-06-01,2000-07-01) | five^2 | 30 | 30
+ [3,4) | [2000-07-01,2010-01-01) | three | 3471 | 3471
+ [4,5) | [2000-06-01,2000-07-01) | one^2 | 30 | 30
+ [5,6) | [2000-01-01,2000-03-01) | five | 60 | 60
+ [5,6) | [2000-03-01,2000-04-01) | five^1 | 31 | 31
+ [5,6) | [2000-04-01,2000-06-01) | five | 61 | 61
+ [5,6) | [2000-07-01,2010-01-01) | five | 3471 | 3471
(15 rows)
SELECT * FROM temporal_partitioned_1 ORDER BY id, valid_at;
- id | valid_at | name
--------+-------------------------+---------
- [1,2) | [2000-01-01,2000-03-01) | one
- [1,2) | [2000-03-01,2000-04-01) | one^1
- [1,2) | [2000-04-01,2000-06-01) | one
- [1,2) | [2000-07-01,2010-01-01) | one
- [2,3) | [2000-06-01,2000-07-01) | three^2
+ id | valid_at | name | range_len
+-------+-------------------------+---------+-----------
+ [1,2) | [2000-01-01,2000-03-01) | one | 60
+ [1,2) | [2000-03-01,2000-04-01) | one^1 | 31
+ [1,2) | [2000-04-01,2000-06-01) | one | 61
+ [1,2) | [2000-07-01,2010-01-01) | one | 3471
+ [2,3) | [2000-06-01,2000-07-01) | three^2 | 30
(5 rows)
SELECT * FROM temporal_partitioned_3 ORDER BY id, valid_at;
- name | id | valid_at
----------+-------+-------------------------
- three | [3,4) | [2000-01-01,2000-03-01)
- three^1 | [3,4) | [2000-03-01,2000-04-01)
- three | [3,4) | [2000-04-01,2000-06-01)
- five^2 | [3,4) | [2000-06-01,2000-07-01)
- three | [3,4) | [2000-07-01,2010-01-01)
- one^2 | [4,5) | [2000-06-01,2000-07-01)
+ name | id | valid_at | range_len
+---------+-------+-------------------------+-----------
+ three | [3,4) | [2000-01-01,2000-03-01) | 60
+ three^1 | [3,4) | [2000-03-01,2000-04-01) | 31
+ three | [3,4) | [2000-04-01,2000-06-01) | 61
+ five^2 | [3,4) | [2000-06-01,2000-07-01) | 30
+ three | [3,4) | [2000-07-01,2010-01-01) | 3471
+ one^2 | [4,5) | [2000-06-01,2000-07-01) | 30
(6 rows)
SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
- name | valid_at | id
---------+-------------------------+-------
- five | [2000-01-01,2000-03-01) | [5,6)
- five^1 | [2000-03-01,2000-04-01) | [5,6)
- five | [2000-04-01,2000-06-01) | [5,6)
- five | [2000-07-01,2010-01-01) | [5,6)
+ name | valid_at | id | range_len
+--------+-------------------------+-------+-----------
+ five | [2000-01-01,2000-03-01) | [5,6) | 60
+ five^1 | [2000-03-01,2000-04-01) | [5,6) | 31
+ five | [2000-04-01,2000-06-01) | [5,6) | 61
+ five | [2000-07-01,2010-01-01) | [5,6) | 3471
(4 rows)
DROP TABLE temporal_partitioned;
@@ -2152,4 +2170,84 @@ SELECT * FROM fpo_rule ORDER BY f1;
(2 rows)
DROP TABLE fpo_rule;
+-- UPDATE FOR PORTION OF with generated stored columns
+-- The generated column depends on the range column, so it must be
+-- recomputed when FOR PORTION OF narrows the range.
+CREATE TABLE fpo_generated (
+ id int,
+ valid_at int4range,
+ range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED,
+ range_lenv int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at))
+);
+INSERT INTO fpo_generated (id, valid_at) VALUES (1, '[10,100)');
+SELECT * FROM fpo_generated ORDER BY valid_at;
+ id | valid_at | range_len | range_lenv
+----+----------+-----------+------------
+ 1 | [10,100) | 90 | 90
+(1 row)
+
+-- After the FOR PORTION OF (FPO) update, all three resulting rows
+-- (leftover-before, updated, and leftover-after) must contain the correct
+-- values for range_len and range_lenv.
+UPDATE fpo_generated
+ FOR PORTION OF valid_at FROM 30 TO 70
+ SET id = 2;
+SELECT * FROM fpo_generated ORDER BY valid_at;
+ id | valid_at | range_len | range_lenv
+----+----------+-----------+------------
+ 1 | [10,30) | 20 | 20
+ 2 | [30,70) | 40 | 40
+ 1 | [70,100) | 30 | 30
+(3 rows)
+
+-- Also test with a generated column that references both a SET column
+-- and the range column.
+DROP TABLE fpo_generated;
+CREATE TABLE fpo_generated (
+ id int,
+ valid_at int4range,
+ id_plus_len int GENERATED ALWAYS AS (id + upper(valid_at) - lower(valid_at)) STORED,
+ id_plus_lenv int GENERATED ALWAYS AS (id + upper(valid_at) - lower(valid_at))
+);
+INSERT INTO fpo_generated (id, valid_at) VALUES (1, '[10,100)');
+SELECT * FROM fpo_generated ORDER BY valid_at;
+ id | valid_at | id_plus_len | id_plus_lenv
+----+----------+-------------+--------------
+ 1 | [10,100) | 91 | 91
+(1 row)
+
+UPDATE fpo_generated
+ FOR PORTION OF valid_at FROM 30 TO 70
+ SET id = 2;
+SELECT * FROM fpo_generated ORDER BY valid_at;
+ id | valid_at | id_plus_len | id_plus_lenv
+----+----------+-------------+--------------
+ 1 | [10,30) | 21 | 21
+ 2 | [30,70) | 42 | 42
+ 1 | [70,100) | 31 | 31
+(3 rows)
+
+DROP TABLE fpo_generated;
+-- Test that UPDATE OF colname triggers fire if colname is valid_at:
+CREATE TABLE fpo_update_of_trigger (
+ id int,
+ valid_at int4range
+);
+INSERT INTO fpo_update_of_trigger (id, valid_at) VALUES (1, '[10,100)');
+CREATE TRIGGER fpo_before_row1
+ BEFORE UPDATE OF valid_at ON fpo_update_of_trigger
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_before_row2
+ BEFORE UPDATE OF valid_at ON fpo_update_of_trigger
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+UPDATE fpo_update_of_trigger
+ FOR PORTION OF valid_at FROM 30 TO 70
+ SET id = 2;
+NOTICE: fpo_before_row2: BEFORE UPDATE STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row1: BEFORE UPDATE ROW:
+NOTICE: old: [10,100)
+NOTICE: new: [30,70)
+DROP TABLE fpo_update_of_trigger;
RESET datestyle;
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 0de13612818..f6cc1a1029c 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -1152,16 +1152,26 @@ CREATE TABLE t1 (
valid_at tsrange,
CONSTRAINT t1pk PRIMARY KEY (c1, valid_at WITHOUT OVERLAPS)
);
--- UPDATE requires select permission on the valid_at column (but not update):
+-- UPDATE requires select and update permission on the valid_at column:
GRANT SELECT (c1) ON t1 TO regress_priv_user2;
GRANT UPDATE (c1) ON t1 TO regress_priv_user2;
GRANT SELECT (c1, valid_at) ON t1 TO regress_priv_user3;
GRANT UPDATE (c1) ON t1 TO regress_priv_user3;
+GRANT SELECT (c1) ON t1 TO regress_priv_user4;
+GRANT UPDATE (c1, valid_at) ON t1 TO regress_priv_user4;
+GRANT SELECT (c1, valid_at) ON t1 TO regress_priv_user5;
+GRANT UPDATE (c1, valid_at) ON t1 TO regress_priv_user5;
SET SESSION AUTHORIZATION regress_priv_user2;
UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
ERROR: permission denied for table t1
SET SESSION AUTHORIZATION regress_priv_user3;
UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
+ERROR: permission denied for table t1
+SET SESSION AUTHORIZATION regress_priv_user4;
+UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
+ERROR: permission denied for table t1
+SET SESSION AUTHORIZATION regress_priv_user5;
+UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
SET SESSION AUTHORIZATION regress_priv_user1;
-- DELETE requires select permission on the valid_at column:
GRANT DELETE ON t1 TO regress_priv_user2;
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index fd79a9b78e7..856e3f91291 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -913,6 +913,10 @@ CREATE TRIGGER fpo_before_stmt
BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_before_stmt1
+ BEFORE UPDATE OF valid_at ON for_portion_of_test
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+
CREATE TRIGGER fpo_after_insert_stmt
AFTER INSERT ON for_portion_of_test
FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
@@ -931,6 +935,10 @@ CREATE TRIGGER fpo_before_row
BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_before_row1
+ BEFORE UPDATE OF valid_at ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+
CREATE TRIGGER fpo_after_insert_row
AFTER INSERT ON for_portion_of_test
FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
@@ -1292,6 +1300,7 @@ DROP TABLE for_portion_of_test2;
DROP TYPE mydaterange;
-- Test FOR PORTION OF against a partitioned table.
+-- Include a GENERATED STORED column to test updatedCols column mapping.
-- temporal_partitioned_1 has the same attnums as the root
-- temporal_partitioned_3 has the different attnums from the root
-- temporal_partitioned_5 has the different attnums too, but reversed
@@ -1300,6 +1309,7 @@ CREATE TABLE temporal_partitioned (
id int4range,
valid_at daterange,
name text,
+ range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED,
CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
) PARTITION BY LIST (id);
CREATE TABLE temporal_partitioned_1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)');
@@ -1307,13 +1317,15 @@ CREATE TABLE temporal_partitioned_3 PARTITION OF temporal_partitioned FOR VALUES
CREATE TABLE temporal_partitioned_5 PARTITION OF temporal_partitioned FOR VALUES IN ('[5,6)', '[6,7)');
ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_3;
-ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at;
+ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at CASCADE;
ALTER TABLE temporal_partitioned_3 ADD COLUMN id int4range NOT NULL, ADD COLUMN valid_at daterange NOT NULL;
+ALTER TABLE temporal_partitioned_3 ADD COLUMN range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED;
ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_3 FOR VALUES IN ('[3,4)', '[4,5)');
ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_5;
-ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at;
+ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at CASCADE;
ALTER TABLE temporal_partitioned_5 ADD COLUMN valid_at daterange NOT NULL, ADD COLUMN id int4range NOT NULL;
+ALTER TABLE temporal_partitioned_5 ADD COLUMN range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED;
ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_5 FOR VALUES IN ('[5,6)', '[6,7)');
INSERT INTO temporal_partitioned (id, valid_at, name) VALUES
@@ -1358,7 +1370,7 @@ UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-0
-- Update all partitions at once (each with leftovers)
-SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
+SELECT *, upper(valid_at) - lower(valid_at) FROM temporal_partitioned ORDER BY id, valid_at;
SELECT * FROM temporal_partitioned_1 ORDER BY id, valid_at;
SELECT * FROM temporal_partitioned_3 ORDER BY id, valid_at;
SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
@@ -1398,4 +1410,62 @@ SELECT * FROM fpo_rule ORDER BY f1;
DROP TABLE fpo_rule;
+-- UPDATE FOR PORTION OF with generated stored columns
+-- The generated column depends on the range column, so it must be
+-- recomputed when FOR PORTION OF narrows the range.
+CREATE TABLE fpo_generated (
+ id int,
+ valid_at int4range,
+ range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at)) STORED,
+ range_lenv int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at))
+);
+INSERT INTO fpo_generated (id, valid_at) VALUES (1, '[10,100)');
+
+SELECT * FROM fpo_generated ORDER BY valid_at;
+
+-- After the FOR PORTION OF (FPO) update, all three resulting rows
+-- (leftover-before, updated, and leftover-after) must contain the correct
+-- values for range_len and range_lenv.
+UPDATE fpo_generated
+ FOR PORTION OF valid_at FROM 30 TO 70
+ SET id = 2;
+
+SELECT * FROM fpo_generated ORDER BY valid_at;
+
+-- Also test with a generated column that references both a SET column
+-- and the range column.
+DROP TABLE fpo_generated;
+CREATE TABLE fpo_generated (
+ id int,
+ valid_at int4range,
+ id_plus_len int GENERATED ALWAYS AS (id + upper(valid_at) - lower(valid_at)) STORED,
+ id_plus_lenv int GENERATED ALWAYS AS (id + upper(valid_at) - lower(valid_at))
+);
+
+INSERT INTO fpo_generated (id, valid_at) VALUES (1, '[10,100)');
+SELECT * FROM fpo_generated ORDER BY valid_at;
+
+UPDATE fpo_generated
+ FOR PORTION OF valid_at FROM 30 TO 70
+ SET id = 2;
+SELECT * FROM fpo_generated ORDER BY valid_at;
+DROP TABLE fpo_generated;
+
+-- Test that UPDATE OF colname triggers fire if colname is valid_at:
+CREATE TABLE fpo_update_of_trigger (
+ id int,
+ valid_at int4range
+);
+INSERT INTO fpo_update_of_trigger (id, valid_at) VALUES (1, '[10,100)');
+CREATE TRIGGER fpo_before_row1
+ BEFORE UPDATE OF valid_at ON fpo_update_of_trigger
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_before_row2
+ BEFORE UPDATE OF valid_at ON fpo_update_of_trigger
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+UPDATE fpo_update_of_trigger
+ FOR PORTION OF valid_at FROM 30 TO 70
+ SET id = 2;
+DROP TABLE fpo_update_of_trigger;
+
RESET datestyle;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 95a46854b37..6cd9bb840ff 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -790,15 +790,23 @@ CREATE TABLE t1 (
valid_at tsrange,
CONSTRAINT t1pk PRIMARY KEY (c1, valid_at WITHOUT OVERLAPS)
);
--- UPDATE requires select permission on the valid_at column (but not update):
+-- UPDATE requires select and update permission on the valid_at column:
GRANT SELECT (c1) ON t1 TO regress_priv_user2;
GRANT UPDATE (c1) ON t1 TO regress_priv_user2;
GRANT SELECT (c1, valid_at) ON t1 TO regress_priv_user3;
GRANT UPDATE (c1) ON t1 TO regress_priv_user3;
+GRANT SELECT (c1) ON t1 TO regress_priv_user4;
+GRANT UPDATE (c1, valid_at) ON t1 TO regress_priv_user4;
+GRANT SELECT (c1, valid_at) ON t1 TO regress_priv_user5;
+GRANT UPDATE (c1, valid_at) ON t1 TO regress_priv_user5;
SET SESSION AUTHORIZATION regress_priv_user2;
UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
SET SESSION AUTHORIZATION regress_priv_user3;
UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
+SET SESSION AUTHORIZATION regress_priv_user4;
+UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
+SET SESSION AUTHORIZATION regress_priv_user5;
+UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
SET SESSION AUTHORIZATION regress_priv_user1;
-- DELETE requires select permission on the valid_at column:
GRANT DELETE ON t1 TO regress_priv_user2;
--
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]
Subject: Re: FOR PORTION OF does not recompute GENERATED STORED columns that depend on the range column
In-Reply-To: <CA+renyXyLfvtvVv--hGWGTgzFP=-+dPLy4RWvEmioAPyJMM+uw@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