On Wed, May 6, 2026 at 4:39 AM Peter Eisentraut <[email protected]> wrote:
>
> On 05.05.26 23:50, Paul A Jungwirth wrote:
> > On Wed, Apr 22, 2026 at 11:03 AM Paul A Jungwirth
> > <[email protected]> wrote:
> >>
> >> Good catch! I removed that line in v7 (attached). I also included your
> >> test change to compute the range len by hand. Also a rebase was
> >> necessary after d3bba04154.
> >
> > This needed a rebase. v8 attached.
>
> This patch fails the injection_points/isolation test for me. It looks
> like it causes a server crash. Check please.
Sorry, I didn't have injection_points enabled, but now I see it too.
The attached v9 fixes it.
Yours,
--
Paul ~{:-)
[email protected]
From 0325ac58ad1e2f2e8fb1a2007ddadfcdaffdaced Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Fri, 10 Apr 2026 17:01:12 +0800
Subject: [PATCH v9] Fix some problems with UPDATE FOR PORTION OF
- Fixed inserting leftovers with traditional table inheritance. Since there is
no tuple routing, we must add them directly to the child table. Also this
preserves extra columns in that table.
- Added ExecInitForPortionOf. This sets up executor state for child partitions.
Previously we did this in ExecForPortionOfLeftovers, but doing it earlier lets
us use the child->parent attr mapping in the fixes below.
- Made sure GENERATED STORED columns that depend on the application-time column
get updated. We exclude that column from the updatedCols bitmapset, because it
does not require permissions. But then we must remember to add it later. This
also fixes a similar problem with UPDATE OF triggers.
- Clarified a comment about the rangetype stored in ForPortionOfState.
Discussion: https://postgr.es/m/CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=h...@mail.gmail.com
Discussion: https://postgr.es/m/CAHg+QDcsXsUVaZ+JwM02yDRQEi=cl_rth_roldygox004sq...@mail.gmail.com
---
src/backend/executor/execUtils.c | 36 ++-
src/backend/executor/nodeModifyTable.c | 145 ++++++++----
src/include/nodes/execnodes.h | 3 +-
src/test/regress/expected/for_portion_of.out | 221 +++++++++++++++----
src/test/regress/sql/for_portion_of.sql | 94 +++++++-
5 files changed, 400 insertions(+), 99 deletions(-)
diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c
index 1eb6b9f1f40..ae23c248081 100644
--- a/src/backend/executor/execUtils.c
+++ b/src/backend/executor/execUtils.c
@@ -1408,20 +1408,52 @@ Bitmapset *
ExecGetUpdatedCols(ResultRelInfo *relinfo, EState *estate)
{
RTEPermissionInfo *perminfo = GetResultRTEPermissionInfo(relinfo, estate);
+ Bitmapset *updatedCols;
if (perminfo == NULL)
return NULL;
+ updatedCols = perminfo->updatedCols;
+
/* Map the columns to child's attribute numbers if needed. */
if (relinfo->ri_RootResultRelInfo)
{
TupleConversionMap *map = ExecGetRootToChildMap(relinfo, estate);
if (map)
- return execute_attr_map_cols(map->attrMap, perminfo->updatedCols);
+ updatedCols = execute_attr_map_cols(map->attrMap, updatedCols);
+ }
+
+ /*
+ * For UPDATE ... FOR PORTION OF, the range column is being modified
+ * (narrowed via intersection), but it is not included in updatedCols
+ * because the user does not need UPDATE permission on it. Now manualy
+ * add it to updatedCols. Since ri_forPortionOf->fp_rangeAttno is already
+ * mapped for the child partition, we have to add it after the mapping just
+ * above. Also that makes it unsafe to mutate perminfo. XXX: Always add the
+ * unmapped attno instead (before mapping), and mutate perminfo, to avoid
+ * repeated allocations?
+ */
+ if (relinfo->ri_forPortionOf)
+ {
+ AttrNumber rangeAttno = relinfo->ri_forPortionOf->fp_rangeAttno;
+
+ if (!bms_is_member(rangeAttno - FirstLowInvalidHeapAttributeNumber,
+ updatedCols))
+ {
+ MemoryContext oldContext;
+
+ oldContext = MemoryContextSwitchTo(estate->es_query_cxt);
+
+ updatedCols =
+ bms_add_member(updatedCols,
+ rangeAttno - FirstLowInvalidHeapAttributeNumber);
+
+ MemoryContextSwitchTo(oldContext);
+ }
}
- return perminfo->updatedCols;
+ return updatedCols;
}
/* Return a bitmap representing generated columns being updated */
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 4cb057ca4f9..81f5afc9fb7 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -198,6 +198,8 @@ static TupleTableSlot *ExecMergeNotMatched(ModifyTableContext *context,
static void ExecSetupTransitionCaptureState(ModifyTableState *mtstate, EState *estate);
static void fireBSTriggers(ModifyTableState *node);
static void fireASTriggers(ModifyTableState *node);
+static void ExecInitForPortionOf(ModifyTableState *mtstate, EState *estate,
+ ResultRelInfo *resultRelInfo);
/*
@@ -1409,7 +1411,6 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
ModifyTableState *mtstate = context->mtstate;
ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node->forPortionOf;
- AttrNumber rangeAttno;
Datum oldRange;
TypeCacheEntry *typcache;
ForPortionOfState *fpoState;
@@ -1424,37 +1425,10 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
ReturnSetInfo rsi;
bool didInit = false;
bool shouldFree = false;
+ ResultRelInfo *rootRelInfo = mtstate->rootResultRelInfo;
LOCAL_FCINFO(fcinfo, 2);
- if (!resultRelInfo->ri_forPortionOf)
- {
- /*
- * If we don't have a ForPortionOfState yet, we must be a partition
- * child being hit for the first time. Make a copy from the root, with
- * our own TupleTableSlot. We do this lazily so that we don't pay the
- * price of unused partitions.
- */
- ForPortionOfState *leafState = makeNode(ForPortionOfState);
-
- if (!mtstate->rootResultRelInfo)
- elog(ERROR, "no root relation but ri_forPortionOf is uninitialized");
-
- fpoState = mtstate->rootResultRelInfo->ri_forPortionOf;
- Assert(fpoState);
-
- leafState->fp_rangeName = fpoState->fp_rangeName;
- leafState->fp_rangeType = fpoState->fp_rangeType;
- leafState->fp_rangeAttno = fpoState->fp_rangeAttno;
- leafState->fp_targetRange = fpoState->fp_targetRange;
- leafState->fp_Leftover = fpoState->fp_Leftover;
- /* Each partition needs a slot matching its tuple descriptor */
- leafState->fp_Existing =
- table_slot_create(resultRelInfo->ri_RelationDesc,
- &mtstate->ps.state->es_tupleTable);
-
- resultRelInfo->ri_forPortionOf = leafState;
- }
fpoState = resultRelInfo->ri_forPortionOf;
oldtupleSlot = fpoState->fp_Existing;
leftoverSlot = fpoState->fp_Leftover;
@@ -1475,21 +1449,13 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc, tupleid, SnapshotAny, oldtupleSlot))
elog(ERROR, "failed to fetch tuple for FOR PORTION OF");
- /*
- * Get the old range of the record being updated/deleted. Must read with
- * the attno of the leaf partition being updated.
- */
-
- rangeAttno = forPortionOf->rangeVar->varattno;
- if (resultRelInfo->ri_RootResultRelInfo)
- map = ExecGetChildToRootMap(resultRelInfo);
- if (map != NULL)
- rangeAttno = map->attrMap->attnums[rangeAttno - 1];
slot_getallattrs(oldtupleSlot);
- if (oldtupleSlot->tts_isnull[rangeAttno - 1])
+ /* Get the old range of the record being updated/deleted. */
+
+ if (oldtupleSlot->tts_isnull[fpoState->fp_rangeAttno - 1])
elog(ERROR, "found a NULL range in a temporal table");
- oldRange = oldtupleSlot->tts_values[rangeAttno - 1];
+ oldRange = oldtupleSlot->tts_values[fpoState->fp_rangeAttno - 1];
/*
* Get the range's type cache entry. This is worth caching for the whole
@@ -1527,12 +1493,20 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
fcinfo->args[1].isnull = false;
/*
- * If there are partitions, we must insert into the root table, so we get
- * tuple routing. We already set up leftoverSlot with the root tuple
- * descriptor.
+ * For partitioned tables, we must read leftovers with the tuple descriptor
+ * of the child table, but insert into the root table to enable tuple
+ * routing. So leftoverSlot is configured with the root's tuple
+ * descriptor. However, for traditional table inheritance, we don't need
+ * tuple routing and just insert directly into the child table to preserve
+ * child-specific columns. In that case, leftoverSlot uses the child's
+ * (resultRelInfo) tuple descriptor.
*/
- if (resultRelInfo->ri_RootResultRelInfo)
+ if (rootRelInfo &&
+ rootRelInfo->ri_RelationDesc->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ map = ExecGetChildToRootMap(resultRelInfo);
resultRelInfo = resultRelInfo->ri_RootResultRelInfo;
+ }
/*
* Insert a leftover for each value returned by the without_portion helper
@@ -1601,8 +1575,9 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
didInit = true;
}
- leftoverSlot->tts_values[forPortionOf->rangeVar->varattno - 1] = leftover;
- leftoverSlot->tts_isnull[forPortionOf->rangeVar->varattno - 1] = false;
+ leftoverSlot->tts_values[resultRelInfo->ri_forPortionOf->fp_rangeAttno - 1] = leftover;
+ leftoverSlot->tts_isnull[resultRelInfo->ri_forPortionOf->fp_rangeAttno - 1] = false;
+
ExecMaterializeSlot(leftoverSlot);
/*
@@ -4777,6 +4752,18 @@ ExecModifyTable(PlanState *pstate)
false, true);
}
+ /*
+ * If we don't have a ForPortionOfState yet, we must be a partition
+ * child being hit for the first time. Make a copy from the root, with
+ * our own TupleTableSlot. We do this lazily so that we don't pay the
+ * price of unused partitions.
+ */
+ if ((((ModifyTable *) context.mtstate->ps.plan)->forPortionOf) &&
+ !resultRelInfo->ri_forPortionOf)
+ {
+ ExecInitForPortionOf(context.mtstate, estate, resultRelInfo);
+ }
+
/*
* If resultRelInfo->ri_usesFdwDirectModify is true, all we need to do
* here is compute the RETURNING expressions.
@@ -5860,3 +5847,67 @@ ExecReScanModifyTable(ModifyTableState *node)
*/
elog(ERROR, "ExecReScanModifyTable is not implemented");
}
+
+/* ----------------------------------------------------------------
+ * ExecInitForPortionOf
+ *
+ * Initializes resultRelInfo->ri_forPortionOf for child tables.
+ * ----------------------------------------------------------------
+ */
+static void
+ExecInitForPortionOf(ModifyTableState *mtstate, EState *estate, ResultRelInfo *resultRelInfo)
+{
+ MemoryContext oldcxt;
+ ForPortionOfState *leafState;
+ ResultRelInfo *rootRelInfo = mtstate->rootResultRelInfo;
+ ForPortionOfState *fpoState;
+
+ if (!rootRelInfo)
+ elog(ERROR, "no root relation but ri_forPortionOf is uninitialized");
+
+ fpoState = mtstate->rootResultRelInfo->ri_forPortionOf;
+
+ /* Things built here have to last for the query duration. */
+ oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
+
+ leafState = makeNode(ForPortionOfState);
+
+ leafState->fp_rangeName = fpoState->fp_rangeName;
+ leafState->fp_rangeType = fpoState->fp_rangeType;
+ leafState->fp_targetRange = fpoState->fp_targetRange;
+
+ /*
+ * For partitioned tables we must read the leftovers using the child table's
+ * tuple descriptor, but then insert them into the root table (using its
+ * tuple descriptor) so we get tuple routing.
+ *
+ * For traditional table inheritance, we read and insert directly into this
+ * resultRelInfo; no tuple routing to the parent is required.
+ */
+ if (rootRelInfo->ri_RelationDesc->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ TupleConversionMap *map = ExecGetChildToRootMap(resultRelInfo);
+ if (map)
+ leafState->fp_rangeAttno = map->attrMap->attnums[fpoState->fp_rangeAttno - 1];
+ else
+ leafState->fp_rangeAttno = fpoState->fp_rangeAttno;
+ leafState->fp_Leftover = fpoState->fp_Leftover;
+ }
+ else
+ {
+ leafState->fp_rangeAttno = fpoState->fp_rangeAttno;
+ leafState->fp_Leftover =
+ ExecInitExtraTupleSlot(mtstate->ps.state,
+ RelationGetDescr(resultRelInfo->ri_RelationDesc),
+ &TTSOpsVirtual);
+ }
+
+ /* Each partition needs a slot matching its tuple descriptor */
+ leafState->fp_Existing =
+ table_slot_create(resultRelInfo->ri_RelationDesc,
+ &mtstate->ps.state->es_tupleTable);
+
+ resultRelInfo->ri_forPortionOf = leafState;
+
+ MemoryContextSwitchTo(oldcxt);
+}
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 13359180d25..53c138310db 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -477,7 +477,8 @@ typedef struct ForPortionOfState
NodeTag type;
char *fp_rangeName; /* the column named in FOR PORTION OF */
- Oid fp_rangeType; /* the type of the FOR PORTION OF expression */
+ Oid fp_rangeType; /* the base type (not domain) of the FOR
+ * PORTION OF expression */
int fp_rangeAttno; /* the attno of the range column */
Datum fp_targetRange; /* the range/multirange from FOR PORTION OF */
TypeCacheEntry *fp_leftoverstypcache; /* type cache entry of the range */
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
index 0c0a205c44b..91241463991 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,115 @@ 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)
+
+CREATE TRIGGER fpo_before_row1
+ BEFORE UPDATE OF valid_at ON fpo_generated
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_before_row2
+ BEFORE UPDATE OF valid_at ON fpo_generated
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+-- 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.
+-- Triggers fpo_before_row1 and fpo_before_row2 should also be fired.
+UPDATE fpo_generated
+ 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)
+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;
+-- UPDATE FOR PORTION OF with table inheritance
+-- Leftover rows must stay in the child table, preserving child-specific columns.
+CREATE TABLE fpo_inh_parent (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+CREATE TABLE fpo_inh_child (
+ description text
+) INHERITS (fpo_inh_parent);
+INSERT INTO fpo_inh_child (id, valid_at, name, description) VALUES
+ ('[1,2)', '[2018-01-01,2019-01-01)', 'one', 'initial');
+-- Update targets the parent; the matching row lives in the child.
+UPDATE fpo_inh_parent FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-10-01'
+ SET name = 'one^1';
+-- All three rows should be in the child, with description preserved.
+SELECT tableoid::regclass, * FROM fpo_inh_parent ORDER BY valid_at;
+ tableoid | id | valid_at | name
+---------------+-------+-------------------------+-------
+ fpo_inh_child | [1,2) | [2018-01-01,2018-04-01) | one
+ fpo_inh_child | [1,2) | [2018-04-01,2018-10-01) | one^1
+ fpo_inh_child | [1,2) | [2018-10-01,2019-01-01) | one
+(3 rows)
+
+SELECT * FROM fpo_inh_child ORDER BY valid_at;
+ id | valid_at | name | description
+-------+-------------------------+-------+-------------
+ [1,2) | [2018-01-01,2018-04-01) | one | initial
+ [1,2) | [2018-04-01,2018-10-01) | one^1 | initial
+ [1,2) | [2018-10-01,2019-01-01) | one | initial
+(3 rows)
+
+-- No rows should have leaked into the parent.
+SELECT * FROM ONLY fpo_inh_parent ORDER BY valid_at;
+ id | valid_at | name
+----+----------+------
+(0 rows)
+
+DROP TABLE fpo_inh_parent CASCADE;
+NOTICE: drop cascades to table fpo_inh_child
RESET datestyle;
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index fd79a9b78e7..04e0dba6375 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,80 @@ 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;
+
+CREATE TRIGGER fpo_before_row1
+ BEFORE UPDATE OF valid_at ON fpo_generated
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+
+CREATE TRIGGER fpo_before_row2
+ BEFORE UPDATE OF valid_at ON fpo_generated
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+
+-- 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.
+-- Triggers fpo_before_row1 and fpo_before_row2 should also be fired.
+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;
+
+
+-- UPDATE FOR PORTION OF with table inheritance
+-- Leftover rows must stay in the child table, preserving child-specific columns.
+CREATE TABLE fpo_inh_parent (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+CREATE TABLE fpo_inh_child (
+ description text
+) INHERITS (fpo_inh_parent);
+INSERT INTO fpo_inh_child (id, valid_at, name, description) VALUES
+ ('[1,2)', '[2018-01-01,2019-01-01)', 'one', 'initial');
+
+-- Update targets the parent; the matching row lives in the child.
+UPDATE fpo_inh_parent FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-10-01'
+ SET name = 'one^1';
+
+-- All three rows should be in the child, with description preserved.
+SELECT tableoid::regclass, * FROM fpo_inh_parent ORDER BY valid_at;
+SELECT * FROM fpo_inh_child ORDER BY valid_at;
+-- No rows should have leaked into the parent.
+SELECT * FROM ONLY fpo_inh_parent ORDER BY valid_at;
+
+DROP TABLE fpo_inh_parent CASCADE;
+
RESET datestyle;
--
2.47.3
