v14-0003-Add-UPDATE-DELETE-FOR-PORTION-OF.patch
text/x-patch
Filename: v14-0003-Add-UPDATE-DELETE-FOR-PORTION-OF.patch
Type: text/x-patch
Part: 2
Message:
Re: SQL:2011 application time
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v14-0003
Subject: Add UPDATE/DELETE FOR PORTION OF
| File | + | − |
|---|---|---|
| doc/src/sgml/ref/delete.sgml | 46 | 0 |
| doc/src/sgml/ref/update.sgml | 47 | 0 |
| doc/src/sgml/trigger.sgml | 49 | 11 |
| src/backend/commands/tablecmds.c | 1 | 0 |
| src/backend/commands/trigger.c | 49 | 0 |
| src/backend/executor/execMain.c | 1 | 0 |
| src/backend/executor/nodeModifyTable.c | 221 | 1 |
| src/backend/nodes/nodeFuncs.c | 24 | 0 |
| src/backend/optimizer/plan/createplan.c | 6 | 2 |
| src/backend/optimizer/plan/planner.c | 1 | 0 |
| src/backend/optimizer/util/pathnode.c | 3 | 1 |
| src/backend/parser/analyze.c | 209 | 5 |
| src/backend/parser/gram.y | 39 | 8 |
| src/backend/parser/parse_agg.c | 10 | 0 |
| src/backend/parser/parse_collate.c | 1 | 0 |
| src/backend/parser/parse_expr.c | 8 | 0 |
| src/backend/parser/parse_func.c | 3 | 0 |
| src/backend/parser/parse_merge.c | 1 | 1 |
| src/backend/rewrite/rewriteHandler.c | 40 | 0 |
| src/backend/tcop/utility.c | 1 | 1 |
| src/backend/utils/adt/Makefile | 1 | 0 |
| src/backend/utils/adt/period.c | 56 | 0 |
| src/backend/utils/adt/rangetypes.c | 42 | 0 |
| src/backend/utils/adt/ri_triggers.c | 74 | 0 |
| src/backend/utils/cache/lsyscache.c | 52 | 0 |
| src/include/commands/trigger.h | 1 | 0 |
| src/include/nodes/execnodes.h | 27 | 0 |
| src/include/nodes/parsenodes.h | 31 | 13 |
| src/include/nodes/pathnodes.h | 1 | 0 |
| src/include/nodes/plannodes.h | 1 | 0 |
| src/include/nodes/primnodes.h | 25 | 0 |
| src/include/optimizer/pathnode.h | 2 | 1 |
| src/include/parser/analyze.h | 2 | 1 |
| src/include/parser/kwlist.h | 1 | 0 |
| src/include/parser/parse_node.h | 1 | 0 |
| src/include/utils/lsyscache.h | 2 | 0 |
| src/include/utils/period.h | 19 | 0 |
| src/include/utils/rangetypes.h | 3 | 0 |
| src/test/regress/expected/for_portion_of.out | 431 | 0 |
| src/test/regress/expected/privileges.out | 18 | 0 |
| src/test/regress/expected/updatable_views.out | 32 | 0 |
| src/test/regress/expected/without_overlaps.out | 127 | 2 |
| src/test/regress/parallel_schedule | 1 | 1 |
| src/test/regress/sql/for_portion_of.sql | 340 | 0 |
| src/test/regress/sql/privileges.sql | 18 | 0 |
| src/test/regress/sql/updatable_views.sql | 14 | 0 |
| src/test/regress/sql/without_overlaps.sql | 75 | 2 |
From d2b7da7fecab992a0fb20337d01ea4f15f16aceb Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Fri, 25 Jun 2021 18:54:35 -0700
Subject: [PATCH v14 3/5] Add UPDATE/DELETE FOR PORTION OF
- Added bison support for FOR PORTION OF syntax. The bounds must be
constant, so we forbid column references, subqueries, etc. But we
permit the magic UNBOUNDED keyword as the FROM or the TO (or both)
to perform an unbounded update/delete. We also accept functions like
NOW().
- Added logic to executor to insert new rows for the "leftover" part of
a record touched by a FOR PORTION OF query.
- Added tg_temporal descriptor to the TriggerData struct that we pass to
trigger functions. Our triggers use this to learn what bounds were
given in the FOR PORTION OF clause.
- Documented FOR PORTION OF.
- Documented tg_temporal struct.
- Added tests.
---
doc/src/sgml/ref/delete.sgml | 46 ++
doc/src/sgml/ref/update.sgml | 47 ++
doc/src/sgml/trigger.sgml | 60 ++-
src/backend/commands/tablecmds.c | 1 +
src/backend/commands/trigger.c | 49 ++
src/backend/executor/execMain.c | 1 +
src/backend/executor/nodeModifyTable.c | 222 ++++++++-
src/backend/nodes/nodeFuncs.c | 24 +
src/backend/optimizer/plan/createplan.c | 8 +-
src/backend/optimizer/plan/planner.c | 1 +
src/backend/optimizer/util/pathnode.c | 4 +-
src/backend/parser/analyze.c | 214 ++++++++-
src/backend/parser/gram.y | 47 +-
src/backend/parser/parse_agg.c | 10 +
src/backend/parser/parse_collate.c | 1 +
src/backend/parser/parse_expr.c | 8 +
src/backend/parser/parse_func.c | 3 +
src/backend/parser/parse_merge.c | 2 +-
src/backend/rewrite/rewriteHandler.c | 40 ++
src/backend/tcop/utility.c | 2 +-
src/backend/utils/adt/Makefile | 1 +
src/backend/utils/adt/period.c | 56 +++
src/backend/utils/adt/rangetypes.c | 42 ++
src/backend/utils/adt/ri_triggers.c | 74 +++
src/backend/utils/cache/lsyscache.c | 52 +++
src/include/commands/trigger.h | 1 +
src/include/nodes/execnodes.h | 27 ++
src/include/nodes/parsenodes.h | 44 +-
src/include/nodes/pathnodes.h | 1 +
src/include/nodes/plannodes.h | 1 +
src/include/nodes/primnodes.h | 25 +
src/include/optimizer/pathnode.h | 3 +-
src/include/parser/analyze.h | 3 +-
src/include/parser/kwlist.h | 1 +
src/include/parser/parse_node.h | 1 +
src/include/utils/lsyscache.h | 2 +
src/include/utils/period.h | 19 +
src/include/utils/rangetypes.h | 3 +
src/test/regress/expected/for_portion_of.out | 431 ++++++++++++++++++
src/test/regress/expected/privileges.out | 18 +
src/test/regress/expected/updatable_views.out | 32 ++
.../regress/expected/without_overlaps.out | 129 +++++-
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/for_portion_of.sql | 340 ++++++++++++++
src/test/regress/sql/privileges.sql | 18 +
src/test/regress/sql/updatable_views.sql | 14 +
src/test/regress/sql/without_overlaps.sql | 77 +++-
47 files changed, 2157 insertions(+), 50 deletions(-)
create mode 100644 src/backend/utils/adt/period.c
create mode 100644 src/include/utils/period.h
create mode 100644 src/test/regress/expected/for_portion_of.out
create mode 100644 src/test/regress/sql/for_portion_of.sql
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 1b81b4e7d7..c0903ec361 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -23,6 +23,7 @@ PostgreSQL documentation
<synopsis>
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
DELETE FROM [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
+ [ FOR PORTION OF <replaceable class="parameter">range_or_period_name</replaceable> FROM <replaceable class="parameter">start_time</replaceable> TO <replaceable class="parameter">end_time</replaceable> ]
[ USING <replaceable class="parameter">from_item</replaceable> [, ...] ]
[ WHERE <replaceable class="parameter">condition</replaceable> | WHERE CURRENT OF <replaceable class="parameter">cursor_name</replaceable> ]
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
@@ -54,6 +55,15 @@ DELETE FROM [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ *
circumstances.
</para>
+ <para>
+ If the table has a <link linkend="ddl-periods-application-periods">range column
+ or <literal>PERIOD</literal></link>, you may supply a
+ <literal>FOR PORTION OF</literal> clause, and your delete will only affect rows
+ that overlap the given interval. Furthermore, if a row's span extends outside
+ the <literal>FOR PORTION OF</literal> bounds, then new rows spanning the "cut
+ off" duration will be inserted to preserve the old values.
+ </para>
+
<para>
The optional <literal>RETURNING</literal> clause causes <command>DELETE</command>
to compute and return value(s) based on each row actually deleted.
@@ -116,6 +126,42 @@ DELETE FROM [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ *
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">range_or_period_name</replaceable></term>
+ <listitem>
+ <para>
+ The range column or period to use when performing a temporal delete. This
+ must match the range or period used in the table's temporal primary key.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">start_time</replaceable></term>
+ <listitem>
+ <para>
+ The earliest time (inclusive) to change in a temporal delete.
+ This must be a value matching the base type of the range or period from
+ <replaceable class="parameter">range_or_period_name</replaceable>. It may also
+ be the special value <literal>MINVALUE</literal> to indicate a delete whose
+ beginning is unbounded.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">end_time</replaceable></term>
+ <listitem>
+ <para>
+ The latest time (exclusive) to change in a temporal delete.
+ This must be a value matching the base type of the range or period from
+ <replaceable class="parameter">range_or_period_name</replaceable>. It may also
+ be the special value <literal>MAXVALUE</literal> to indicate a delete whose
+ end is unbounded.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">from_item</replaceable></term>
<listitem>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 2ab24b0523..069cb3ca5c 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -23,6 +23,7 @@ PostgreSQL documentation
<synopsis>
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
+ [ FOR PORTION OF <replaceable class="parameter">range_or_period_name</replaceable> FROM <replaceable class="parameter">start_time</replaceable> TO <replaceable class="parameter">end_time</replaceable> ]
SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } |
( <replaceable class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) |
( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable class="parameter">sub-SELECT</replaceable> )
@@ -51,6 +52,16 @@ UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [
circumstances.
</para>
+ <para>
+ If the table has a <link linkend="ddl-periods-application-periods">range column
+ or <literal>PERIOD</literal></link>, you may supply a
+ <literal>FOR PORTION OF</literal> clause, and your update will only affect rows
+ that overlap the given interval. Furthermore, if a row's span extends outside
+ the <literal>FOR PORTION OF</literal> bounds, then it will be truncated to fit
+ within the bounds, and new rows spanning the "cut off" duration will be
+ inserted to preserve the old values.
+ </para>
+
<para>
The optional <literal>RETURNING</literal> clause causes <command>UPDATE</command>
to compute and return value(s) based on each row actually updated.
@@ -114,6 +125,42 @@ UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">range_or_period_name</replaceable></term>
+ <listitem>
+ <para>
+ The range column or period to use when performing a temporal update. This
+ must match the range or period used in the table's temporal primary key.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">start_time</replaceable></term>
+ <listitem>
+ <para>
+ The earliest time (inclusive) to change in a temporal update.
+ This must be a value matching the base type of the range or period from
+ <replaceable class="parameter">range_or_period_name</replaceable>. It may also
+ be the special value <literal>MINVALUE</literal> to indicate an update whose
+ beginning is unbounded.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">end_time</replaceable></term>
+ <listitem>
+ <para>
+ The latest time (exclusive) to change in a temporal update.
+ This must be a value matching the base type of the range or period from
+ <replaceable class="parameter">range_or_period_name</replaceable>. It may also
+ be the special value <literal>MAXVALUE</literal> to indicate an update whose
+ end is unbounded.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">column_name</replaceable></term>
<listitem>
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index 6e1f370b21..b58e6348ad 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -537,17 +537,18 @@ CALLED_AS_TRIGGER(fcinfo)
<programlisting>
typedef struct TriggerData
{
- NodeTag type;
- TriggerEvent tg_event;
- Relation tg_relation;
- HeapTuple tg_trigtuple;
- HeapTuple tg_newtuple;
- Trigger *tg_trigger;
- TupleTableSlot *tg_trigslot;
- TupleTableSlot *tg_newslot;
- Tuplestorestate *tg_oldtable;
- Tuplestorestate *tg_newtable;
- const Bitmapset *tg_updatedcols;
+ NodeTag type;
+ TriggerEvent tg_event;
+ Relation tg_relation;
+ HeapTuple tg_trigtuple;
+ HeapTuple tg_newtuple;
+ Trigger *tg_trigger;
+ TupleTableSlot *tg_trigslot;
+ TupleTableSlot *tg_newslot;
+ Tuplestorestate *tg_oldtable;
+ Tuplestorestate *tg_newtable;
+ const Bitmapset *tg_updatedcols;
+ ForPortionOfState *tg_temporal;
} TriggerData;
</programlisting>
@@ -815,6 +816,43 @@ typedef struct Trigger
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><structfield>tg_temporal</structfield></term>
+ <listitem>
+ <para>
+ Set for <literal>UPDATE</literal> and <literal>DELETE</literal> queries
+ that use <literal>FOR PORTION OF</literal>, otherwise <symbol>NULL</symbol>.
+ Contains a pointer to a structure of type
+ <structname>ForPortionOfState</structname>, defined in
+ <filename>nodes/execnodes.h</filename>:
+
+<programlisting>
+typedef struct ForPortionOfState
+{
+ NodeTag type;
+
+ char *fp_rangeName; /* the column/PERIOD named in FOR PORTION OF */
+ Oid fp_rangeType; /* the type of the FOR PORTION OF expression */
+ bool fp_hasPeriod; /* true iff this is a PERIOD not a range */
+ char *fp_periodStartName; /* the PERIOD's start column */
+ char *fp_periodEndName; /* the PERIOD's end column */
+ Datum fp_targetRange; /* the range from FOR PORTION OF */
+} ForPortionOfState;
+</programlisting>
+
+ where <structfield>fp_rangeName</structfield> is the period or range
+ column named in the <literal>FOR PORTION OF</literal> clause,
+ <structfield>fp_rangeType</structfield> is its range type,
+ <structfield>fp_hasPeriod</structfield> indicates whether a period was used
+ or a range column, <structfield>fp_periodStartName</structfield> and
+ <structfield>fp_periodEndName</structfield> are the names of the period's
+ start and end columns (or <symbol>NULL</symbol> if a range column was used),
+ and <structfield>fp_targetRange</structfield> is a rangetype value created
+ by evaluating the <literal>FOR PORTION OF</literal> bounds.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 5af72ea627..5a4d7ff273 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -12168,6 +12168,7 @@ validateForeignKeyConstraint(char *conname,
trigdata.tg_trigtuple = ExecFetchSlotHeapTuple(slot, false, NULL);
trigdata.tg_trigslot = slot;
trigdata.tg_trigger = &trig;
+ trigdata.tg_temporal = NULL;
fcinfo->context = (Node *) &trigdata;
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 0577b60415..2f05557b34 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -54,6 +54,7 @@
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/bytea.h"
+#include "utils/datum.h"
#include "utils/fmgroids.h"
#include "utils/guc_hooks.h"
#include "utils/inval.h"
@@ -2638,6 +2639,7 @@ ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo)
LocTriggerData.tg_event = TRIGGER_EVENT_DELETE |
TRIGGER_EVENT_BEFORE;
LocTriggerData.tg_relation = relinfo->ri_RelationDesc;
+ LocTriggerData.tg_temporal = relinfo->ri_forPortionOf;
for (i = 0; i < trigdesc->numtriggers; i++)
{
Trigger *trigger = &trigdesc->triggers[i];
@@ -2737,6 +2739,7 @@ ExecBRDeleteTriggers(EState *estate, EPQState *epqstate,
TRIGGER_EVENT_ROW |
TRIGGER_EVENT_BEFORE;
LocTriggerData.tg_relation = relinfo->ri_RelationDesc;
+ LocTriggerData.tg_temporal = relinfo->ri_forPortionOf;
for (i = 0; i < trigdesc->numtriggers; i++)
{
HeapTuple newtuple;
@@ -2828,6 +2831,7 @@ ExecIRDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
TRIGGER_EVENT_ROW |
TRIGGER_EVENT_INSTEAD;
LocTriggerData.tg_relation = relinfo->ri_RelationDesc;
+ LocTriggerData.tg_temporal = relinfo->ri_forPortionOf;
ExecForceStoreHeapTuple(trigtuple, slot, false);
@@ -2891,6 +2895,7 @@ ExecBSUpdateTriggers(EState *estate, ResultRelInfo *relinfo)
TRIGGER_EVENT_BEFORE;
LocTriggerData.tg_relation = relinfo->ri_RelationDesc;
LocTriggerData.tg_updatedcols = updatedCols;
+ LocTriggerData.tg_temporal = relinfo->ri_forPortionOf;
for (i = 0; i < trigdesc->numtriggers; i++)
{
Trigger *trigger = &trigdesc->triggers[i];
@@ -3008,6 +3013,7 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
TRIGGER_EVENT_ROW |
TRIGGER_EVENT_BEFORE;
LocTriggerData.tg_relation = relinfo->ri_RelationDesc;
+ LocTriggerData.tg_temporal = relinfo->ri_forPortionOf;
updatedCols = ExecGetAllUpdatedCols(relinfo, estate);
LocTriggerData.tg_updatedcols = updatedCols;
for (i = 0; i < trigdesc->numtriggers; i++)
@@ -3157,6 +3163,7 @@ ExecIRUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
TRIGGER_EVENT_ROW |
TRIGGER_EVENT_INSTEAD;
LocTriggerData.tg_relation = relinfo->ri_RelationDesc;
+ LocTriggerData.tg_temporal = relinfo->ri_forPortionOf;
ExecForceStoreHeapTuple(trigtuple, oldslot, false);
@@ -3623,6 +3630,7 @@ typedef struct AfterTriggerSharedData
Oid ats_tgoid; /* the trigger's ID */
Oid ats_relid; /* the relation it's on */
CommandId ats_firing_id; /* ID for firing cycle */
+ ForPortionOfState *for_portion_of; /* the FOR PORTION OF clause */
struct AfterTriggersTableData *ats_table; /* transition table access */
Bitmapset *ats_modifiedcols; /* modified columns */
} AfterTriggerSharedData;
@@ -3896,6 +3904,7 @@ static SetConstraintState SetConstraintStateCreate(int numalloc);
static SetConstraintState SetConstraintStateCopy(SetConstraintState origstate);
static SetConstraintState SetConstraintStateAddItem(SetConstraintState state,
Oid tgoid, bool tgisdeferred);
+static ForPortionOfState *CopyForPortionOfState(ForPortionOfState *src);
static void cancel_prior_stmt_triggers(Oid relid, CmdType cmdType, int tgevent);
@@ -4105,6 +4114,7 @@ afterTriggerAddEvent(AfterTriggerEventList *events,
newshared->ats_relid == evtshared->ats_relid &&
newshared->ats_event == evtshared->ats_event &&
newshared->ats_table == evtshared->ats_table &&
+ newshared->for_portion_of == evtshared->for_portion_of &&
newshared->ats_firing_id == 0)
break;
}
@@ -4469,6 +4479,7 @@ AfterTriggerExecute(EState *estate,
LocTriggerData.tg_relation = rel;
if (TRIGGER_FOR_UPDATE(LocTriggerData.tg_trigger->tgtype))
LocTriggerData.tg_updatedcols = evtshared->ats_modifiedcols;
+ LocTriggerData.tg_temporal = evtshared->for_portion_of;
MemoryContextReset(per_tuple_context);
@@ -6005,6 +6016,43 @@ AfterTriggerPendingOnRel(Oid relid)
return false;
}
+/* ----------
+ * ForPortionOfState()
+ *
+ * Copies a ForPortionOfState into the current memory context.
+ */
+static ForPortionOfState *
+CopyForPortionOfState(ForPortionOfState *src)
+{
+ ForPortionOfState *dst = NULL;
+ if (src) {
+ MemoryContext oldctx;
+ RangeType *r;
+ TypeCacheEntry *typcache;
+
+ /*
+ * Need to lift the FOR PORTION OF details into a higher memory context
+ * because cascading foreign key update/deletes can cause triggers to fire
+ * triggers, and the AfterTriggerEvents will outlive the FPO
+ * details of the original query.
+ */
+ oldctx = MemoryContextSwitchTo(TopTransactionContext);
+ dst = makeNode(ForPortionOfState);
+ dst->fp_rangeName = pstrdup(src->fp_rangeName);
+ dst->fp_rangeType = src->fp_rangeType;
+ dst->fp_hasPeriod = src->fp_hasPeriod;
+ dst->fp_rangeAttno = src->fp_rangeAttno;
+ dst->fp_periodStartAttno = src->fp_periodStartAttno;
+ dst->fp_periodEndAttno = src->fp_periodEndAttno;
+
+ r = DatumGetRangeTypeP(src->fp_targetRange);
+ typcache = lookup_type_cache(RangeTypeGetOid(r), TYPECACHE_RANGE_INFO);
+ dst->fp_targetRange = datumCopy(src->fp_targetRange, typcache->typbyval, typcache->typlen);
+ MemoryContextSwitchTo(oldctx);
+ }
+ return dst;
+}
+
/* ----------
* AfterTriggerSaveEvent()
*
@@ -6420,6 +6468,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
else
new_shared.ats_table = NULL;
new_shared.ats_modifiedcols = afterTriggerCopyBitmap(modifiedCols);
+ new_shared.for_portion_of = CopyForPortionOfState(relinfo->ri_forPortionOf);
afterTriggerAddEvent(&afterTriggers.query_stack[afterTriggers.query_depth].events,
&new_event, &new_shared);
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 4c5a7bbf62..69096cbefb 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1274,6 +1274,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
resultRelInfo->ri_projectReturning = NULL;
resultRelInfo->ri_onConflictArbiterIndexes = NIL;
resultRelInfo->ri_onConflict = NULL;
+ resultRelInfo->ri_forPortionOf = NULL;
resultRelInfo->ri_ReturningSlot = NULL;
resultRelInfo->ri_TrigOldSlot = NULL;
resultRelInfo->ri_TrigNewSlot = NULL;
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 5005d8c0d1..92d6611f51 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -61,6 +61,9 @@
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/memutils.h"
+#include "utils/period.h"
+#include "utils/rangetypes.h"
+#include "utils/lsyscache.h"
#include "utils/rel.h"
@@ -141,6 +144,10 @@ static bool ExecOnConflictUpdate(ModifyTableContext *context,
TupleTableSlot *excludedSlot,
bool canSetTag,
TupleTableSlot **returning);
+static void ExecForPortionOfLeftovers(ModifyTableContext *context,
+ EState *estate,
+ ResultRelInfo *resultRelInfo,
+ ItemPointer tupleid);
static TupleTableSlot *ExecPrepareTupleRouting(ModifyTableState *mtstate,
EState *estate,
PartitionTupleRouting *proute,
@@ -1205,6 +1212,138 @@ ExecInsert(ModifyTableContext *context,
return result;
}
+/* ----------------------------------------------------------------
+ * set_leftover_tuple_bounds
+ *
+ * Saves the new bounds into the range attribute
+ * ----------------------------------------------------------------
+ */
+static void set_leftover_tuple_bounds(TupleTableSlot *leftoverTuple,
+ ForPortionOfExpr *forPortionOf,
+ TypeCacheEntry *typcache,
+ RangeType *leftoverRangeType)
+{
+ /* Store the range directly */
+
+ leftoverTuple->tts_values[forPortionOf->rangeVar->varattno - 1] = RangeTypePGetDatum(leftoverRangeType);
+ leftoverTuple->tts_isnull[forPortionOf->rangeVar->varattno - 1] = false;
+}
+
+/* ----------------------------------------------------------------
+ * ExecForPortionOfLeftovers
+ *
+ * Insert tuples for the untouched timestamp of a row in a FOR
+ * PORTION OF UPDATE/DELETE
+ * ----------------------------------------------------------------
+ */
+static void
+ExecForPortionOfLeftovers(ModifyTableContext *context,
+ EState *estate,
+ ResultRelInfo *resultRelInfo,
+ ItemPointer tupleid)
+{
+ ModifyTableState *mtstate = context->mtstate;
+ ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
+ ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node->forPortionOf;
+ Datum oldRange;
+ RangeType *oldRangeType;
+ RangeType *targetRangeType;
+ RangeType *leftoverRangeType1;
+ RangeType *leftoverRangeType2;
+ Oid rangeTypeOid = forPortionOf->rangeType;
+ bool isNull = false;
+ TypeCacheEntry *typcache;
+ ForPortionOfState *fpoState = resultRelInfo->ri_forPortionOf;
+ TupleTableSlot *oldtupleSlot = fpoState->fp_Existing;
+ TupleTableSlot *leftoverTuple1 = fpoState->fp_Leftover1;
+ TupleTableSlot *leftoverTuple2 = fpoState->fp_Leftover2;
+
+ /*
+ * Get the range of the old pre-UPDATE/DELETE tuple,
+ * so we can intersect it with the FOR PORTION OF target
+ * and see if there are any "leftovers" to insert.
+ *
+ * We have already locked the tuple in ExecUpdate/ExecDelete
+ * (TODO: if it was *not* concurrently updated, does table_tuple_update lock the tuple itself?
+ * I don't found the code for that yet, and maybe it depends on the AM?)
+ * and it has passed EvalPlanQual.
+ * Make sure we're looking at the most recent version.
+ * Otherwise concurrent updates of the same tuple in READ COMMITTED
+ * could insert conflicting "leftovers".
+ */
+ if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc, tupleid, SnapshotAny, oldtupleSlot))
+ elog(ERROR, "failed to fetch tuple for FOR PORTION OF");
+
+ oldRange = slot_getattr(oldtupleSlot, forPortionOf->rangeVar->varattno, &isNull);
+
+ if (isNull)
+ elog(ERROR, "found a NULL range in a temporal table");
+ oldRangeType = DatumGetRangeTypeP(oldRange);
+
+ /* Get the target range. */
+
+ targetRangeType = DatumGetRangeTypeP(fpoState->fp_targetRange);
+
+ /*
+ * Get the range's type cache entry. This is worth caching for the whole UPDATE
+ * like range functions do.
+ */
+
+ typcache = fpoState->fp_rangetypcache;
+ if (typcache == NULL)
+ {
+ typcache = lookup_type_cache(rangeTypeOid, TYPECACHE_RANGE_INFO);
+ if (typcache->rngelemtype == NULL)
+ elog(ERROR, "type %u is not a range type", rangeTypeOid);
+ fpoState->fp_rangetypcache = typcache;
+ }
+
+ /* Get the ranges to the left/right of the targeted range. */
+
+ range_leftover_internal(typcache, oldRangeType, targetRangeType, &leftoverRangeType1,
+ &leftoverRangeType2);
+
+ /*
+ * Insert a copy of the tuple with the lower leftover range.
+ * Even if the table is partitioned,
+ * our insert won't extend past the current row, so we don't need to re-route.
+ * TODO: Really? What if you update the partition key?
+ */
+
+ if (!RangeIsEmpty(leftoverRangeType1))
+ {
+ // TODO: anything we need to clear here?
+ // Are we in the row context?
+ HeapTuple oldtuple = ExecFetchSlotHeapTuple(oldtupleSlot, false, NULL);
+ ExecForceStoreHeapTuple(oldtuple, leftoverTuple1, false);
+
+ set_leftover_tuple_bounds(leftoverTuple1, forPortionOf, typcache, leftoverRangeType1);
+ ExecMaterializeSlot(leftoverTuple1);
+
+ // TODO: Need to save context->mtstate->mt_transition_capture? (See comment on ExecInsert)
+ ExecInsert(context, resultRelInfo, leftoverTuple1, node->canSetTag, NULL, NULL);
+ }
+
+ /*
+ * Insert a copy of the tuple with the upper leftover range
+ * Even if the table is partitioned,
+ * our insert won't extend past the current row, so we don't need to re-route.
+ * TODO: Really? What if you update the partition key?
+ */
+
+ if (!RangeIsEmpty(leftoverRangeType2))
+ {
+ HeapTuple oldtuple = ExecFetchSlotHeapTuple(oldtupleSlot, false, NULL);
+ ExecForceStoreHeapTuple(oldtuple, leftoverTuple2, false);
+
+ set_leftover_tuple_bounds(leftoverTuple2, forPortionOf, typcache, leftoverRangeType2);
+ ExecMaterializeSlot(leftoverTuple2);
+
+ // TODO: Need to save context->mtstate->mt_transition_capture? (See comment on ExecInsert)
+ ExecInsert(context, resultRelInfo, leftoverTuple2, node->canSetTag, NULL, NULL);
+ }
+}
+
/* ----------------------------------------------------------------
* ExecBatchInsert
*
@@ -1357,7 +1496,8 @@ ExecDeleteAct(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
*
* Closing steps of tuple deletion; this invokes AFTER FOR EACH ROW triggers,
* including the UPDATE triggers if the deletion is being done as part of a
- * cross-partition tuple move.
+ * cross-partition tuple move. It also inserts leftovers from a FOR PORTION OF
+ * delete.
*/
static void
ExecDeleteEpilogue(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
@@ -1390,6 +1530,11 @@ ExecDeleteEpilogue(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
ar_delete_trig_tcs = NULL;
}
+ /* Compute leftovers in FOR PORTION OF */
+ // TODO: Skip this for FDW deletes?
+ if (((ModifyTable *) context->mtstate->ps.plan)->forPortionOf)
+ ExecForPortionOfLeftovers(context, estate, resultRelInfo, tupleid);
+
/* AFTER ROW DELETE Triggers */
ExecARDeleteTriggers(estate, resultRelInfo, tupleid, oldtuple,
ar_delete_trig_tcs, changingPart);
@@ -2133,6 +2278,11 @@ ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt,
NULL, NIL,
(updateCxt->updateIndexes == TU_Summarizing));
+ /* Compute leftovers in FOR PORTION OF */
+ // TODO: Skip this for FDW updates?
+ if (((ModifyTable *) context->mtstate->ps.plan)->forPortionOf)
+ ExecForPortionOfLeftovers(context, context->estate, resultRelInfo, tupleid);
+
/* AFTER ROW UPDATE Triggers */
ExecARUpdateTriggers(context->estate, resultRelInfo,
NULL, NULL,
@@ -3610,6 +3760,7 @@ ExecModifyTable(PlanState *pstate)
* Reset per-tuple memory context used for processing on conflict and
* returning clauses, to free any expression evaluation storage
* allocated in the previous cycle.
+ * TODO: It sounds like FOR PORTION OF might need to do something here too?
*/
if (pstate->ps_ExprContext)
ResetExprContext(pstate->ps_ExprContext);
@@ -4282,6 +4433,75 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
}
}
+ /*
+ * If needed, initialize the target range for FOR PORTION OF.
+ */
+ if (node->forPortionOf)
+ {
+ TupleDesc tupDesc;
+ ForPortionOfExpr *forPortionOf;
+ Datum targetRange;
+ bool isNull;
+ ExprContext *econtext;
+ ExprState *exprState;
+ ForPortionOfState *fpoState;
+
+ resultRelInfo = mtstate->resultRelInfo;
+ tupDesc = resultRelInfo->ri_RelationDesc->rd_att;
+ forPortionOf = (ForPortionOfExpr *) node->forPortionOf;
+
+ /* Eval the FOR PORTION OF target */
+ if (mtstate->ps.ps_ExprContext == NULL)
+ ExecAssignExprContext(estate, &mtstate->ps);
+ econtext = mtstate->ps.ps_ExprContext;
+
+ exprState = ExecPrepareExpr((Expr *) forPortionOf->targetRange, estate);
+ targetRange = ExecEvalExpr(exprState, econtext, &isNull);
+ if (isNull)
+ elog(ERROR, "Got a NULL FOR PORTION OF target range");
+
+ /* Create state for FOR PORTION OF operation */
+
+ fpoState = makeNode(ForPortionOfState);
+ fpoState->fp_rangeName = forPortionOf->range_name;
+ fpoState->fp_rangeType = forPortionOf->rangeType;
+ fpoState->fp_rangeAttno = forPortionOf->rangeVar->varattno;
+ fpoState->fp_targetRange = targetRange;
+
+ /* Initialize slot for the existing tuple */
+
+ fpoState->fp_Existing =
+ table_slot_create(resultRelInfo->ri_RelationDesc,
+ &mtstate->ps.state->es_tupleTable);
+
+ /* Create the tuple slots for INSERTing the leftovers */
+
+ fpoState->fp_Leftover1 =
+ ExecInitExtraTupleSlot(mtstate->ps.state, tupDesc, &TTSOpsVirtual);
+ fpoState->fp_Leftover2 =
+ ExecInitExtraTupleSlot(mtstate->ps.state, tupDesc, &TTSOpsVirtual);
+
+ /*
+ * We must attach the ForPortionOfState to all result rels,
+ * in case of a cross-partition update or triggers firing
+ * on partitions.
+ *
+ * They should all have the same tupledesc, so it's okay
+ * that any one of them can use the tuple table slots there.
+ */
+ for (i = 0; i < nrels; i++)
+ {
+ resultRelInfo = &mtstate->resultRelInfo[i];
+ resultRelInfo->ri_forPortionOf = fpoState;
+ }
+
+ /* Make sure the root relation has the FOR PORTION OF clause too. */
+ if (node->rootRelation > 0)
+ mtstate->rootResultRelInfo->ri_forPortionOf = fpoState;
+
+ /* Don't free the ExprContext here because the result must last for the whole query */
+ }
+
/*
* If we have any secondary relations in an UPDATE or DELETE, they need to
* be treated like non-locked relations in SELECT FOR UPDATE, ie, the
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index c03f4f23e2..62ccbf9d3c 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2442,6 +2442,14 @@ expression_tree_walker_impl(Node *node,
return true;
}
break;
+ case T_ForPortionOfExpr:
+ {
+ ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node;
+
+ if (WALK(forPortionOf->targetRange))
+ return true;
+ }
+ break;
case T_PartitionPruneStepOp:
{
PartitionPruneStepOp *opstep = (PartitionPruneStepOp *) node;
@@ -2582,6 +2590,8 @@ query_tree_walker_impl(Query *query,
return true;
if (WALK(query->mergeActionList))
return true;
+ if (WALK(query->forPortionOf))
+ return true;
if (WALK(query->returningList))
return true;
if (WALK(query->jointree))
@@ -3425,6 +3435,19 @@ expression_tree_mutator_impl(Node *node,
return (Node *) newnode;
}
break;
+ case T_ForPortionOfExpr:
+ {
+ ForPortionOfExpr *fpo = (ForPortionOfExpr *) node;
+ ForPortionOfExpr *newnode;
+
+ FLATCOPY(newnode, fpo, ForPortionOfExpr);
+ MUTATE(newnode->rangeVar, fpo->rangeVar, Var *);
+ MUTATE(newnode->targetRange, fpo->targetRange, Node *);
+ MUTATE(newnode->rangeSet, fpo->rangeSet, List *);
+
+ return (Node *) newnode;
+ }
+ break;
case T_PartitionPruneStepOp:
{
PartitionPruneStepOp *opstep = (PartitionPruneStepOp *) node;
@@ -3603,6 +3626,7 @@ query_tree_mutator_impl(Query *query,
MUTATE(query->withCheckOptions, query->withCheckOptions, List *);
MUTATE(query->onConflict, query->onConflict, OnConflictExpr *);
MUTATE(query->mergeActionList, query->mergeActionList, List *);
+ MUTATE(query->forPortionOf, query->forPortionOf, ForPortionOfExpr *);
MUTATE(query->returningList, query->returningList, List *);
MUTATE(query->jointree, query->jointree, FromExpr *);
MUTATE(query->setOperations, query->setOperations, Node *);
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 34ca6d4ac2..954eb4da83 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -311,7 +311,8 @@ static ModifyTable *make_modifytable(PlannerInfo *root, Plan *subplan,
List *updateColnosLists,
List *withCheckOptionLists, List *returningLists,
List *rowMarks, OnConflictExpr *onconflict,
- List *mergeActionLists, int epqParam);
+ List *mergeActionLists, ForPortionOfExpr *forPortionOf,
+ int epqParam);
static GatherMerge *create_gather_merge_plan(PlannerInfo *root,
GatherMergePath *best_path);
@@ -2835,6 +2836,7 @@ create_modifytable_plan(PlannerInfo *root, ModifyTablePath *best_path)
best_path->rowMarks,
best_path->onconflict,
best_path->mergeActionLists,
+ best_path->forPortionOf,
best_path->epqParam);
copy_generic_path_info(&plan->plan, &best_path->path);
@@ -7005,7 +7007,8 @@ make_modifytable(PlannerInfo *root, Plan *subplan,
List *updateColnosLists,
List *withCheckOptionLists, List *returningLists,
List *rowMarks, OnConflictExpr *onconflict,
- List *mergeActionLists, int epqParam)
+ List *mergeActionLists, ForPortionOfExpr *forPortionOf,
+ int epqParam)
{
ModifyTable *node = makeNode(ModifyTable);
List *fdw_private_list;
@@ -7071,6 +7074,7 @@ make_modifytable(PlannerInfo *root, Plan *subplan,
node->exclRelTlist = onconflict->exclRelTlist;
}
node->updateColnosLists = updateColnosLists;
+ node->forPortionOf = (Node *) forPortionOf;
node->withCheckOptionLists = withCheckOptionLists;
node->returningLists = returningLists;
node->rowMarks = rowMarks;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 44efb1f4eb..b2d8a67f4c 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1969,6 +1969,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
rowMarks,
parse->onConflict,
mergeActionLists,
+ parse->forPortionOf,
assign_special_exec_param(root));
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 211ba65389..823d1fbab1 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3670,7 +3670,8 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel,
List *updateColnosLists,
List *withCheckOptionLists, List *returningLists,
List *rowMarks, OnConflictExpr *onconflict,
- List *mergeActionLists, int epqParam)
+ List *mergeActionLists, ForPortionOfExpr *forPortionOf,
+ int epqParam)
{
ModifyTablePath *pathnode = makeNode(ModifyTablePath);
@@ -3736,6 +3737,7 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel,
pathnode->returningLists = returningLists;
pathnode->rowMarks = rowMarks;
pathnode->onconflict = onconflict;
+ pathnode->forPortionOf = forPortionOf;
pathnode->epqParam = epqParam;
pathnode->mergeActionLists = mergeActionLists;
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 7a1dfb6364..c4b6d443cf 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -47,11 +47,13 @@
#include "parser/parse_relation.h"
#include "parser/parse_target.h"
#include "parser/parse_type.h"
+#include "parser/parser.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
#include "utils/backend_status.h"
#include "utils/builtins.h"
#include "utils/guc.h"
+#include "utils/lsyscache.h"
#include "utils/rel.h"
#include "utils/syscache.h"
@@ -60,10 +62,17 @@
post_parse_analyze_hook_type post_parse_analyze_hook = NULL;
static Query *transformOptionalSelectInto(ParseState *pstate, Node *parseTree);
+static Node *addForPortionOfWhereConditions(Query *qry, ForPortionOfClause *forPortionOf,
+ Node *whereClause);
static Query *transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt);
static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt);
static OnConflictExpr *transformOnConflictClause(ParseState *pstate,
OnConflictClause *onConflictClause);
+static Node *transformForPortionOfBound(Node *n, bool isLowerBound);
+static ForPortionOfExpr *transformForPortionOfClause(ParseState *pstate,
+ int rtindex,
+ ForPortionOfClause *forPortionOfClause,
+ bool isUpdate);
static int count_rowexpr_columns(ParseState *pstate, Node *expr);
static Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt);
static Query *transformValuesClause(ParseState *pstate, SelectStmt *stmt);
@@ -479,6 +488,20 @@ stmt_requires_parse_analysis(RawStmt *parseTree)
return result;
}
+static Node *
+addForPortionOfWhereConditions(Query *qry, ForPortionOfClause *forPortionOf, Node *whereClause)
+{
+ if (forPortionOf)
+ {
+ if (whereClause)
+ return (Node *) makeBoolExpr(AND_EXPR, list_make2(qry->forPortionOf->overlapsExpr, whereClause), -1);
+ else
+ return qry->forPortionOf->overlapsExpr;
+ }
+ else
+ return whereClause;
+}
+
/*
* analyze_requires_snapshot
* Returns true if a snapshot must be set before doing parse analysis
@@ -512,6 +535,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
{
Query *qry = makeNode(Query);
ParseNamespaceItem *nsitem;
+ Node *whereClause;
Node *qual;
qry->commandType = CMD_DELETE;
@@ -550,7 +574,11 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
nsitem->p_lateral_only = false;
nsitem->p_lateral_ok = true;
- qual = transformWhereClause(pstate, stmt->whereClause,
+ if (stmt->forPortionOf)
+ qry->forPortionOf = transformForPortionOfClause(pstate, qry->resultRelation, stmt->forPortionOf, false);
+
+ whereClause = addForPortionOfWhereConditions(qry, stmt->forPortionOf, stmt->whereClause);
+ qual = transformWhereClause(pstate, whereClause,
EXPR_KIND_WHERE, "WHERE");
qry->returningList = transformReturningList(pstate, stmt->returningList);
@@ -1173,7 +1201,7 @@ transformOnConflictClause(ParseState *pstate,
* Now transform the UPDATE subexpressions.
*/
onConflictSet =
- transformUpdateTargetList(pstate, onConflictClause->targetList);
+ transformUpdateTargetList(pstate, onConflictClause->targetList, NULL);
onConflictWhere = transformWhereClause(pstate,
onConflictClause->whereClause,
@@ -1203,6 +1231,161 @@ transformOnConflictClause(ParseState *pstate,
return result;
}
+/*
+ * transformForPortionOfBound
+ * transforms UNBOUNDED pseudo-column references to NULL
+ * (which represent "unbounded" in a range type, otherwise returns
+ * its input unchanged.
+ */
+static Node *
+transformForPortionOfBound(Node *n, bool isLowerBound)
+{
+ if (nodeTag(n) == T_ColumnRef)
+ {
+ ColumnRef *cref = (ColumnRef *) n;
+ char *cname = "";
+ A_Const *n2;
+
+ if (list_length(cref->fields) == 1 &&
+ IsA(linitial(cref->fields), String))
+ cname = strVal(linitial(cref->fields));
+
+ if (strcmp("unbounded", cname) != 0)
+ return n;
+
+ n2 = makeNode(A_Const);
+ n2->isnull = true;
+ n2->location = ((ColumnRef *)n)->location;
+
+ return (Node *)n2;
+ }
+ else
+ return n;
+}
+
+/*
+ * transformForPortionOfClause
+ *
+ * Transforms a ForPortionOfClause in an UPDATE/DELETE statement.
+ *
+ * - Look up the range/period requested.
+ * - Build a compatible range value from the FROM and TO expressions.
+ * - Build an "overlaps" expression for filtering.
+ * - For UPDATEs, build an "intersects" expression the rewriter can add
+ * - to the targetList to change the temporal bounds.
+ */
+static ForPortionOfExpr *
+transformForPortionOfClause(ParseState *pstate,
+ int rtindex,
+ ForPortionOfClause *forPortionOf,
+ bool isUpdate)
+{
+ Relation targetrel = pstate->p_target_relation;
+ RTEPermissionInfo *target_perminfo = pstate->p_target_nsitem->p_perminfo;
+ char *range_name = forPortionOf->range_name;
+ char *range_type_namespace = NULL;
+ char *range_type_name = NULL;
+ int range_attno = InvalidAttrNumber;
+ Form_pg_attribute attr;
+ ForPortionOfExpr *result;
+ List *targetList;
+ Node *target_start, *target_end;
+ Var *rangeVar;
+ FuncCall *fc;
+
+ result = makeNode(ForPortionOfExpr);
+
+ /* Look up the FOR PORTION OF name requested. */
+ range_attno = attnameAttNum(targetrel, range_name, false);
+ if (range_attno == InvalidAttrNumber)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column or period \"%s\" of relation \"%s\" does not exist",
+ range_name,
+ RelationGetRelationName(targetrel)),
+ parser_errposition(pstate, forPortionOf->range_name_location)));
+ attr = TupleDescAttr(targetrel->rd_att, range_attno - 1);
+ // TODO: check attr->attisdropped (?),
+ // and figure out concurrency issues with that in general.
+ // It should work the same as updating any other column.
+
+ /* Make sure it's a range column */
+ if (!type_is_range(attr->atttypid))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg("column \"%s\" of relation \"%s\" is not a range type",
+ range_name,
+ RelationGetRelationName(targetrel)),
+ parser_errposition(pstate, forPortionOf->range_name_location)));
+
+ rangeVar = makeVar(
+ rtindex,
+ range_attno,
+ attr->atttypid,
+ attr->atttypmod,
+ attr->attcollation,
+ 0);
+ rangeVar->location = forPortionOf->range_name_location;
+ result->rangeVar = rangeVar;
+ result->rangeType = attr->atttypid;
+ if (!get_typname_and_namespace(attr->atttypid, &range_type_name, &range_type_namespace))
+ elog(ERROR, "missing range type %d", attr->atttypid);
+
+
+ /*
+ * Build a range from the FROM ... TO .... bounds.
+ * This should give a constant result, so we accept functions like NOW()
+ * but not column references, subqueries, etc.
+ *
+ * It also permits UNBOUNDED in either place.
+ */
+ target_start = transformForPortionOfBound(forPortionOf->target_start, true);
+ target_end = transformForPortionOfBound(forPortionOf->target_end, false);
+ fc = makeFuncCall(
+ list_make2(makeString(range_type_namespace), makeString(range_type_name)),
+ list_make2(target_start, target_end),
+ COERCE_EXPLICIT_CALL,
+ forPortionOf->range_name_location);
+ result->targetRange = transformExpr(pstate, (Node *) fc, EXPR_KIND_UPDATE_PORTION);
+
+ /* overlapsExpr is something we can add to the whereClause */
+ result->overlapsExpr = (Node *) makeSimpleA_Expr(AEXPR_OP, "&&",
+ (Node *) copyObject(rangeVar), (Node *) fc,
+ forPortionOf->range_name_location);
+
+ if (isUpdate)
+ {
+ /*
+ * Now make sure we update the start/end time of the record.
+ * For a range col (r) this is `r = r * targetRange`.
+ */
+ Expr *rangeSetExpr = (Expr *) makeSimpleA_Expr(AEXPR_OP, "*",
+ (Node *) copyObject(rangeVar), (Node *) fc,
+ forPortionOf->range_name_location);
+ TargetEntry *tle;
+
+ targetList = NIL;
+ rangeSetExpr = (Expr *) transformExpr(pstate, (Node *) rangeSetExpr, EXPR_KIND_UPDATE_PORTION);
+ tle = makeTargetEntry(rangeSetExpr,
+ range_attno,
+ range_name,
+ false);
+
+ targetList = lappend(targetList, tle);
+
+ /* Mark the range column as requiring update permissions */
+ target_perminfo->updatedCols = bms_add_member(target_perminfo->updatedCols,
+ range_attno - FirstLowInvalidHeapAttributeNumber);
+
+ result->rangeSet = targetList;
+ }
+ else
+ result->rangeSet = NIL;
+
+ result->range_name = range_name;
+
+ return result;
+}
/*
* BuildOnConflictExcludedTargetlist
@@ -2409,6 +2592,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
{
Query *qry = makeNode(Query);
ParseNamespaceItem *nsitem;
+ Node *whereClause;
Node *qual;
qry->commandType = CMD_UPDATE;
@@ -2426,6 +2610,10 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
stmt->relation->inh,
true,
ACL_UPDATE);
+
+ if (stmt->forPortionOf)
+ qry->forPortionOf = transformForPortionOfClause(pstate, qry->resultRelation, stmt->forPortionOf, true);
+
nsitem = pstate->p_target_nsitem;
/* subqueries in FROM cannot access the result relation */
@@ -2442,7 +2630,8 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
nsitem->p_lateral_only = false;
nsitem->p_lateral_ok = true;
- qual = transformWhereClause(pstate, stmt->whereClause,
+ whereClause = addForPortionOfWhereConditions(qry, stmt->forPortionOf, stmt->whereClause);
+ qual = transformWhereClause(pstate, whereClause,
EXPR_KIND_WHERE, "WHERE");
qry->returningList = transformReturningList(pstate, stmt->returningList);
@@ -2451,7 +2640,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
* Now we are done with SELECT-like processing, and can get on with
* transforming the target list to match the UPDATE target columns.
*/
- qry->targetList = transformUpdateTargetList(pstate, stmt->targetList);
+ qry->targetList = transformUpdateTargetList(pstate, stmt->targetList, qry->forPortionOf);
qry->rtable = pstate->p_rtable;
qry->rteperminfos = pstate->p_rteperminfos;
@@ -2470,7 +2659,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
* handle SET clause in UPDATE/MERGE/INSERT ... ON CONFLICT UPDATE
*/
List *
-transformUpdateTargetList(ParseState *pstate, List *origTlist)
+transformUpdateTargetList(ParseState *pstate, List *origTlist, ForPortionOfExpr *forPortionOf)
{
List *tlist = NIL;
RTEPermissionInfo *target_perminfo;
@@ -2520,6 +2709,21 @@ transformUpdateTargetList(ParseState *pstate, List *origTlist)
RelationGetRelationName(pstate->p_target_relation)),
parser_errposition(pstate, origTarget->location)));
+ /*
+ * If this is a FOR PORTION OF update,
+ * forbid directly setting the range column,
+ * since that would conflict with the implicit updates.
+ */
+ if (forPortionOf != NULL)
+ {
+ if (attrno == forPortionOf->rangeVar->varattno)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("can't directly assign to \"%s\" in a FOR PORTION OF update",
+ origTarget->name),
+ parser_errposition(pstate, origTarget->location)));
+ }
+
updateTargetListEntry(pstate, tle, origTarget->name,
attrno,
origTarget->indirection,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7d22319d40..5a49ba6fa3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -258,6 +258,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
RangeVar *range;
IntoClause *into;
WithClause *with;
+ ForPortionOfClause *forportionof;
InferClause *infer;
OnConflictClause *onconflict;
A_Indices *aind;
@@ -551,6 +552,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <range> relation_expr
%type <range> extended_relation_expr
%type <range> relation_expr_opt_alias
+%type <forportionof> for_portion_of_clause
%type <node> tablesample_clause opt_repeatable_clause
%type <target> target_el set_target insert_column_item
@@ -747,7 +749,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD
PERIOD PLACING PLANS POLICY
- POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
+ PORTION POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
QUOTE
@@ -828,6 +830,16 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%nonassoc KEYS OBJECT_P SCALAR VALUE_P
%nonassoc WITH WITHOUT
+/*
+ * We need to handle this shift/reduce conflict:
+ * FOR PORTION OF valid_at FROM INTERVAL YEAR TO MONTH TO foo.
+ * This is basically the classic "dangling else" problem, and we want a
+ * similar resolution: treat the TO as part of the INTERVAL, not as part of
+ * the FROM ... TO .... Users can add parentheses if that's a problem.
+ * TO just needs to be higher precedence than YEAR_P etc.
+ */
+%nonassoc YEAR_P MONTH_P DAY_P HOUR_P MINUTE_P
+%nonassoc TO
/*
* To support target_el without AS, it used to be necessary to assign IDENT an
* explicit precedence just less than Op. While that's not really necessary
@@ -12166,14 +12178,16 @@ returning_clause:
*****************************************************************************/
DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
+ for_portion_of_clause
using_clause where_or_current_clause returning_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
n->relation = $4;
- n->usingClause = $5;
- n->whereClause = $6;
- n->returningList = $7;
+ n->forPortionOf = $5;
+ n->usingClause = $6;
+ n->whereClause = $7;
+ n->returningList = $8;
n->withClause = $1;
$$ = (Node *) n;
}
@@ -12236,6 +12250,7 @@ opt_nowait_or_skip:
*****************************************************************************/
UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
+ for_portion_of_clause
SET set_clause_list
from_clause
where_or_current_clause
@@ -12244,10 +12259,11 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
UpdateStmt *n = makeNode(UpdateStmt);
n->relation = $3;
- n->targetList = $5;
- n->fromClause = $6;
- n->whereClause = $7;
- n->returningList = $8;
+ n->forPortionOf = $4;
+ n->targetList = $6;
+ n->fromClause = $7;
+ n->whereClause = $8;
+ n->returningList = $9;
n->withClause = $1;
$$ = (Node *) n;
}
@@ -13683,6 +13699,19 @@ relation_expr_opt_alias: relation_expr %prec UMINUS
}
;
+for_portion_of_clause:
+ FOR PORTION OF ColId FROM a_expr TO a_expr
+ {
+ ForPortionOfClause *n = makeNode(ForPortionOfClause);
+ n->range_name = $4;
+ n->range_name_location = @4;
+ n->target_start = $6;
+ n->target_end = $8;
+ $$ = n;
+ }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+
/*
* TABLESAMPLE decoration in a FROM item
*/
@@ -17238,6 +17267,7 @@ unreserved_keyword:
| PASSWORD
| PLANS
| POLICY
+ | PORTION
| PRECEDING
| PREPARE
| PREPARED
@@ -17842,6 +17872,7 @@ bare_label_keyword:
| PLACING
| PLANS
| POLICY
+ | PORTION
| POSITION
| PRECEDING
| PREPARE
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 85cd47b7ae..ce8e0be045 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -563,6 +563,13 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr)
case EXPR_KIND_CYCLE_MARK:
errkind = true;
break;
+ case EXPR_KIND_UPDATE_PORTION:
+ if (isAgg)
+ err = _("aggregate functions are not allowed in FOR PORTION OF expressions");
+ else
+ err = _("grouping operations are not allowed in FOR PORTION OF expressions");
+
+ break;
/*
* There is intentionally no default: case here, so that the
@@ -953,6 +960,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
case EXPR_KIND_CYCLE_MARK:
errkind = true;
break;
+ case EXPR_KIND_UPDATE_PORTION:
+ err = _("window functions are not allowed in FOR PORTION OF expressions");
+ break;
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_collate.c b/src/backend/parser/parse_collate.c
index 9f6afc351c..d09b2d2b1c 100644
--- a/src/backend/parser/parse_collate.c
+++ b/src/backend/parser/parse_collate.c
@@ -484,6 +484,7 @@ assign_collations_walker(Node *node, assign_collations_context *context)
case T_JoinExpr:
case T_FromExpr:
case T_OnConflictExpr:
+ case T_ForPortionOfExpr:
case T_SortGroupClause:
case T_MergeAction:
(void) expression_tree_walker(node,
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 64c582c344..fee6bd902a 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -566,6 +566,9 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
case EXPR_KIND_PARTITION_BOUND:
err = _("cannot use column reference in partition bound expression");
break;
+ case EXPR_KIND_UPDATE_PORTION:
+ err = _("cannot use column reference in FOR PORTION OF expression");
+ break;
/*
* There is intentionally no default: case here, so that the
@@ -1813,6 +1816,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_GENERATED_COLUMN:
err = _("cannot use subquery in column generation expression");
break;
+ case EXPR_KIND_UPDATE_PORTION:
+ err = _("cannot use subquery in FOR PORTION OF expression");
+ break;
/*
* There is intentionally no default: case here, so that the
@@ -3104,6 +3110,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "UPDATE";
case EXPR_KIND_MERGE_WHEN:
return "MERGE WHEN";
+ case EXPR_KIND_UPDATE_PORTION:
+ return "FOR PORTION OF";
case EXPR_KIND_GROUP_BY:
return "GROUP BY";
case EXPR_KIND_ORDER_BY:
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index b3f0b6a137..31ed01a84e 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2656,6 +2656,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location)
case EXPR_KIND_CYCLE_MARK:
errkind = true;
break;
+ case EXPR_KIND_UPDATE_PORTION:
+ err = _("set-returning functions are not allowed in FOR PORTION OF expressions");
+ break;
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_merge.c b/src/backend/parser/parse_merge.c
index 91b1156d99..c00802ae23 100644
--- a/src/backend/parser/parse_merge.c
+++ b/src/backend/parser/parse_merge.c
@@ -372,7 +372,7 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
pstate->p_is_insert = false;
action->targetList =
transformUpdateTargetList(pstate,
- mergeWhenClause->targetList);
+ mergeWhenClause->targetList, NULL);
}
break;
case CMD_DELETE:
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index b486ab559a..576a98e0f4 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3473,6 +3473,30 @@ rewriteTargetView(Query *parsetree, Relation view)
&parsetree->hasSubLinks);
}
+ if (parsetree->forPortionOf && parsetree->commandType == CMD_UPDATE)
+ {
+ /*
+ * Like the INSERT/UPDATE code above, update the resnos in the
+ * auxiliary UPDATE targetlist to refer to columns of the base
+ * relation.
+ */
+ foreach(lc, parsetree->forPortionOf->rangeSet)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ TargetEntry *view_tle;
+
+ if (tle->resjunk)
+ continue;
+
+ view_tle = get_tle_by_resno(view_targetlist, tle->resno);
+ if (view_tle != NULL && !view_tle->resjunk && IsA(view_tle->expr, Var))
+ tle->resno = ((Var *) view_tle->expr)->varattno;
+ else
+ elog(ERROR, "attribute number %d not found in view targetlist",
+ tle->resno);
+ }
+ }
+
/*
* For UPDATE/DELETE, pull up any WHERE quals from the view. We know that
* any Vars in the quals must reference the one base relation, so we need
@@ -3811,6 +3835,22 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length)
else if (event == CMD_UPDATE)
{
Assert(parsetree->override == OVERRIDING_NOT_SET);
+ /*
+ * Update FOR PORTION OF column(s) automatically. Don't
+ * do this until we're done rewriting a view update, so
+ * that we don't add the same update on the recursion.
+ */
+ if (parsetree->forPortionOf &&
+ rt_entry_relation->rd_rel->relkind != RELKIND_VIEW)
+ {
+ ListCell *tl;
+ foreach(tl, parsetree->forPortionOf->rangeSet)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(tl);
+ parsetree->targetList = lappend(parsetree->targetList, tle);
+ }
+ }
+
parsetree->targetList =
rewriteTargetListIU(parsetree->targetList,
parsetree->commandType,
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index e3ccf6c7f7..6781e55020 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1560,7 +1560,7 @@ ProcessUtilitySlow(ParseState *pstate,
true, /* check_rights */
true, /* check_not_in_use */
false, /* skip_build */
- false); /* quiet */
+ false); /* quiet */
/*
* Add the CREATE INDEX node itself to stash right away;
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 0de0bbb1b8..a4f226ec8d 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -78,6 +78,7 @@ OBJS = \
oracle_compat.o \
orderedsetaggs.o \
partitionfuncs.o \
+ period.o \
pg_locale.o \
pg_lsn.o \
pg_upgrade_support.o \
diff --git a/src/backend/utils/adt/period.c b/src/backend/utils/adt/period.c
new file mode 100644
index 0000000000..0ed4304e16
--- /dev/null
+++ b/src/backend/utils/adt/period.c
@@ -0,0 +1,56 @@
+/*-------------------------------------------------------------------------
+ *
+ * period.c
+ * Functions to support periods.
+ *
+ *
+ * Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/utils/adt/period.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "executor/tuptable.h"
+#include "fmgr.h"
+#include "nodes/primnodes.h"
+#include "utils/fmgrprotos.h"
+#include "utils/period.h"
+#include "utils/rangetypes.h"
+
+Datum period_to_range(TupleTableSlot *slot, int startattno, int endattno, Oid rangetype)
+{
+ Datum startvalue;
+ Datum endvalue;
+ Datum result;
+ bool startisnull;
+ bool endisnull;
+ LOCAL_FCINFO(fcinfo, 2);
+ FmgrInfo flinfo;
+ FuncExpr *f;
+
+ InitFunctionCallInfoData(*fcinfo, &flinfo, 2, InvalidOid, NULL, NULL);
+ f = makeNode(FuncExpr);
+ f->funcresulttype = rangetype;
+ flinfo.fn_expr = (Node *) f;
+ flinfo.fn_extra = NULL;
+
+ /* compute oldvalue */
+ startvalue = slot_getattr(slot, startattno, &startisnull);
+ endvalue = slot_getattr(slot, endattno, &endisnull);
+
+ fcinfo->args[0].value = startvalue;
+ fcinfo->args[0].isnull = startisnull;
+ fcinfo->args[1].value = endvalue;
+ fcinfo->args[1].isnull = endisnull;
+
+ result = range_constructor2(fcinfo);
+ if (fcinfo->isnull)
+ elog(ERROR, "function %u returned NULL", flinfo.fn_oid);
+
+ return result;
+}
diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index d65e5625c7..d4e1732377 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -1206,6 +1206,48 @@ range_split_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeT
return false;
}
+/*
+ * range_leftover_internal - Sets output1 and output2 to the remaining parts of r1
+ * after subtracting r2, or if nothing is left then to the empty range.
+ * output1 will always be "before" r2 and output2 "after".
+ */
+void
+range_leftover_internal(TypeCacheEntry *typcache, const RangeType *r1,
+ const RangeType *r2, RangeType **output1, RangeType **output2)
+{
+ RangeBound lower1,
+ lower2;
+ RangeBound upper1,
+ upper2;
+ bool empty1,
+ empty2;
+
+ range_deserialize(typcache, r1, &lower1, &upper1, &empty1);
+ range_deserialize(typcache, r2, &lower2, &upper2, &empty2);
+
+ if (range_cmp_bounds(typcache, &lower1, &lower2) < 0)
+ {
+ lower2.inclusive = !lower2.inclusive;
+ lower2.lower = false;
+ *output1 = make_range(typcache, &lower1, &lower2, false, NULL);
+ }
+ else
+ {
+ *output1 = make_empty_range(typcache);
+ }
+
+ if (range_cmp_bounds(typcache, &upper1, &upper2) > 0)
+ {
+ upper2.inclusive = !upper2.inclusive;
+ upper2.lower = true;
+ *output2 = make_range(typcache, &upper2, &upper1, false, NULL);
+ }
+ else
+ {
+ *output2 = make_empty_range(typcache);
+ }
+}
+
/* range -> range aggregate functions */
Datum
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 365b844bf3..446e898eb6 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -227,6 +227,7 @@ static bool ri_PerformCheck(const RI_ConstraintInfo *riinfo,
RI_QueryKey *qkey, SPIPlanPtr qplan,
Relation fk_rel, Relation pk_rel,
TupleTableSlot *oldslot, TupleTableSlot *newslot,
+ int forPortionOfParam, Datum forPortionOf,
bool detectNewRows, int expect_OK);
static void ri_ExtractValues(Relation rel, TupleTableSlot *slot,
const RI_ConstraintInfo *riinfo, bool rel_is_pk,
@@ -235,6 +236,11 @@ static void ri_ReportViolation(const RI_ConstraintInfo *riinfo,
Relation pk_rel, Relation fk_rel,
TupleTableSlot *violatorslot, TupleDesc tupdesc,
int queryno, bool partgone) pg_attribute_noreturn();
+static bool fpo_targets_pk_range(const ForPortionOfState *tg_temporal,
+ const RI_ConstraintInfo *riinfo);
+static Datum restrict_cascading_range(const ForPortionOfState *tg_temporal,
+ const RI_ConstraintInfo *riinfo,
+ TupleTableSlot *oldslot);
/*
@@ -443,6 +449,7 @@ RI_FKey_check(TriggerData *trigdata)
ri_PerformCheck(riinfo, &qkey, qplan,
fk_rel, pk_rel,
NULL, newslot,
+ -1, 0,
pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE,
SPI_OK_SELECT);
@@ -600,6 +607,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
result = ri_PerformCheck(riinfo, &qkey, qplan,
fk_rel, pk_rel,
oldslot, NULL,
+ -1, 0,
true, /* treat like update */
SPI_OK_SELECT);
@@ -699,6 +707,8 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
TupleTableSlot *oldslot;
RI_QueryKey qkey;
SPIPlanPtr qplan;
+ int targetRangeParam = -1;
+ Datum targetRange = 0;
riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
trigdata->tg_relation, true);
@@ -795,9 +805,16 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
/*
* We have a plan now. Run it to check for existing references.
*/
+ if (trigdata->tg_temporal)
+ {
+ targetRangeParam = riinfo->nkeys - 1;
+ targetRange = restrict_cascading_range(trigdata->tg_temporal, riinfo, oldslot);
+ }
+
ri_PerformCheck(riinfo, &qkey, qplan,
fk_rel, pk_rel,
oldslot, NULL,
+ targetRangeParam, targetRange,
true, /* must detect new rows */
SPI_OK_SELECT);
@@ -904,6 +921,7 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
ri_PerformCheck(riinfo, &qkey, qplan,
fk_rel, pk_rel,
oldslot, NULL,
+ -1, 0,
true, /* must detect new rows */
SPI_OK_DELETE);
@@ -1025,6 +1043,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
ri_PerformCheck(riinfo, &qkey, qplan,
fk_rel, pk_rel,
oldslot, newslot,
+ -1, 0,
true, /* must detect new rows */
SPI_OK_UPDATE);
@@ -1257,6 +1276,7 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind)
ri_PerformCheck(riinfo, &qkey, qplan,
fk_rel, pk_rel,
oldslot, NULL,
+ -1, 0,
true, /* must detect new rows */
SPI_OK_UPDATE);
@@ -2519,6 +2539,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
RI_QueryKey *qkey, SPIPlanPtr qplan,
Relation fk_rel, Relation pk_rel,
TupleTableSlot *oldslot, TupleTableSlot *newslot,
+ int forPortionOfParam, Datum forPortionOf,
bool detectNewRows, int expect_OK)
{
Relation query_rel,
@@ -2574,6 +2595,12 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
ri_ExtractValues(source_rel, oldslot, riinfo, source_is_pk,
vals, nulls);
}
+ /* Add one more query param if we are using FOR PORTION OF */
+ if (forPortionOf)
+ {
+ vals[forPortionOfParam] = forPortionOf;
+ nulls[forPortionOfParam] = ' ';
+ }
/*
* In READ COMMITTED mode, we just need to use an up-to-date regular
@@ -3287,3 +3314,50 @@ RI_FKey_trigger_type(Oid tgfoid)
return RI_TRIGGER_NONE;
}
+
+/*
+ * fpo_targets_pk_range
+ *
+ * Returns true iff the primary key referenced by riinfo includes the range
+ * column targeted by the FOR PORTION OF clause (according to tg_temporal).
+ */
+static bool
+fpo_targets_pk_range(const ForPortionOfState *tg_temporal, const RI_ConstraintInfo *riinfo)
+{
+ if (tg_temporal == NULL)
+ return false;
+
+ return riinfo->pk_attnums[riinfo->nkeys - 1] == tg_temporal->fp_rangeAttno;
+}
+
+/*
+ * restrict_cascading_range -
+ *
+ * Returns a Datum of RangeTypeP holding the appropriate timespan
+ * to target child records when we CASCADE/SET NULL/SET DEFAULT.
+ *
+ * In a normal UPDATE/DELETE this should be the parent's own valid time,
+ * but if there was a FOR PORTION OF clause, then we should use that to
+ * trim down the parent's span further.
+ */
+static Datum
+restrict_cascading_range(const ForPortionOfState *tg_temporal, const RI_ConstraintInfo *riinfo, TupleTableSlot *oldslot)
+{
+ Datum pkRecordRange;
+ bool isnull;
+
+ pkRecordRange = slot_getattr(oldslot, riinfo->pk_attnums[riinfo->nkeys - 1], &isnull);
+ if (isnull)
+ elog(ERROR, "application time should not be null");
+
+ if (fpo_targets_pk_range(tg_temporal, riinfo))
+ {
+ RangeType *r1 = DatumGetRangeTypeP(pkRecordRange);
+ RangeType *r2 = DatumGetRangeTypeP(tg_temporal->fp_targetRange);
+ Oid rngtypid = RangeTypeGetOid(r1);
+ TypeCacheEntry *typcache = lookup_type_cache(rngtypid, TYPECACHE_RANGE_INFO);
+ return RangeTypePGetDatum(range_intersect_internal(typcache, r1, r2));
+ }
+ else
+ return pkRecordRange;
+}
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index fc6d267e44..391762f308 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -2170,6 +2170,58 @@ get_typisdefined(Oid typid)
return false;
}
+/*
+ * get_typname
+ *
+ * Returns the name of a given type
+ *
+ * Returns a palloc'd copy of the string, or NULL if no such type.
+ */
+char *
+get_typname(Oid typid)
+{
+ HeapTuple tp;
+
+ tp = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid));
+ if (HeapTupleIsValid(tp))
+ {
+ Form_pg_type typtup = (Form_pg_type) GETSTRUCT(tp);
+ char *result;
+
+ result = pstrdup(NameStr(typtup->typname));
+ ReleaseSysCache(tp);
+ return result;
+ }
+ else
+ return NULL;
+}
+
+/*
+ * get_typname_and_namespace
+ *
+ * Returns the name and namespace of a given type
+ *
+ * Returns true if one found, or false if not.
+ */
+bool
+get_typname_and_namespace(Oid typid, char **typname, char **typnamespace)
+{
+ HeapTuple tp;
+
+ tp = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid));
+ if (HeapTupleIsValid(tp))
+ {
+ Form_pg_type typtup = (Form_pg_type) GETSTRUCT(tp);
+
+ *typname = pstrdup(NameStr(typtup->typname));
+ *typnamespace = get_namespace_name(typtup->typnamespace);
+ ReleaseSysCache(tp);
+ return *typnamespace;
+ }
+ else
+ return false;
+}
+
/*
* get_typlen
*
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index 430e3ca7dd..b3d90deaa7 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -41,6 +41,7 @@ typedef struct TriggerData
Tuplestorestate *tg_oldtable;
Tuplestorestate *tg_newtable;
const Bitmapset *tg_updatedcols;
+ ForPortionOfState *tg_temporal;
} TriggerData;
/*
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 35c22c37c7..b5a2c22487 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -41,12 +41,14 @@
#include "storage/condition_variable.h"
#include "utils/hsearch.h"
#include "utils/queryenvironment.h"
+#include "utils/rangetypes.h"
#include "utils/reltrigger.h"
#include "utils/sharedtuplestore.h"
#include "utils/snapshot.h"
#include "utils/sortsupport.h"
#include "utils/tuplesort.h"
#include "utils/tuplestore.h"
+#include "utils/typcache.h"
struct PlanState; /* forward references in this file */
struct ParallelHashJoinState;
@@ -423,6 +425,28 @@ typedef struct MergeActionState
ExprState *mas_whenqual; /* WHEN [NOT] MATCHED AND conditions */
} MergeActionState;
+/*
+ * ForPortionOfState
+ *
+ * Executor state of a FOR PORTION OF operation.
+ */
+typedef struct ForPortionOfState
+{
+ NodeTag type;
+
+ char *fp_rangeName; /* the column/PERIOD named in FOR PORTION OF */
+ Oid fp_rangeType; /* the type of the FOR PORTION OF expression */
+ bool fp_hasPeriod; /* true iff this is a PERIOD not a range */
+ int fp_rangeAttno; /* the attno of the range column (or 0 for a PERIOD) */
+ int fp_periodStartAttno; /* the attno of the PERIOD start column (or 0 for a range) */
+ int fp_periodEndAttno; /* the attno of the PERIOD end column (or 0 for a range) */
+ Datum fp_targetRange; /* the range from FOR PORTION OF */
+ TypeCacheEntry *fp_rangetypcache; /* type cache entry of the range */
+ TupleTableSlot *fp_Existing; /* slot to store existing target tuple in */
+ TupleTableSlot *fp_Leftover1; /* slot to store leftover below the target range */
+ TupleTableSlot *fp_Leftover2; /* slot to store leftover above the target range */
+} ForPortionOfState;
+
/*
* ResultRelInfo
*
@@ -543,6 +567,9 @@ typedef struct ResultRelInfo
List *ri_matchedMergeAction;
List *ri_notMatchedMergeAction;
+ /* FOR PORTION OF evaluation state */
+ ForPortionOfState *ri_forPortionOf;
+
/* partition check expression state (NULL if not set up yet) */
ExprState *ri_PartitionCheckExpr;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 479b5ffa35..177df96c86 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -148,6 +148,9 @@ typedef struct Query
*/
int resultRelation pg_node_attr(query_jumble_ignore);
+ /* FOR PORTION OF clause for UPDATE/DELETE */
+ ForPortionOfExpr *forPortionOf;
+
/* has aggregates in tlist or havingQual */
bool hasAggs pg_node_attr(query_jumble_ignore);
/* has window functions in tlist */
@@ -1537,6 +1540,19 @@ typedef struct RowMarkClause
bool pushedDown; /* pushed down from higher query level? */
} RowMarkClause;
+/*
+ * ForPortionOfClause
+ * representation of FOR PORTION OF <period-name> FROM <t1> TO <t2>
+ */
+typedef struct ForPortionOfClause
+{
+ NodeTag type;
+ char *range_name;
+ int range_name_location;
+ Node *target_start;
+ Node *target_end;
+} ForPortionOfClause;
+
/*
* WithClause -
* representation of WITH clause
@@ -1917,12 +1933,13 @@ typedef struct InsertStmt
*/
typedef struct DeleteStmt
{
- NodeTag type;
- RangeVar *relation; /* relation to delete from */
- List *usingClause; /* optional using clause for more tables */
- Node *whereClause; /* qualifications */
- List *returningList; /* list of expressions to return */
- WithClause *withClause; /* WITH clause */
+ NodeTag type;
+ RangeVar *relation; /* relation to delete from */
+ ForPortionOfClause *forPortionOf; /* FOR PORTION OF clause */
+ List *usingClause; /* optional using clause for more tables */
+ Node *whereClause; /* qualifications */
+ List *returningList; /* list of expressions to return */
+ WithClause *withClause; /* WITH clause */
} DeleteStmt;
/* ----------------------
@@ -1931,13 +1948,14 @@ typedef struct DeleteStmt
*/
typedef struct UpdateStmt
{
- NodeTag type;
- RangeVar *relation; /* relation to update */
- List *targetList; /* the target list (of ResTarget) */
- Node *whereClause; /* qualifications */
- List *fromClause; /* optional from clause for more tables */
- List *returningList; /* list of expressions to return */
- WithClause *withClause; /* WITH clause */
+ NodeTag type;
+ RangeVar *relation; /* relation to update */
+ ForPortionOfClause *forPortionOf; /* FOR PORTION OF clause */
+ List *targetList; /* the target list (of ResTarget) */
+ Node *whereClause; /* qualifications */
+ List *fromClause; /* optional from clause for more tables */
+ List *returningList; /* list of expressions to return */
+ WithClause *withClause; /* WITH clause */
} UpdateStmt;
/* ----------------------
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 5702fbba60..d2be7f26d1 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2352,6 +2352,7 @@ typedef struct ModifyTablePath
List *returningLists; /* per-target-table RETURNING tlists */
List *rowMarks; /* PlanRowMarks (non-locking only) */
OnConflictExpr *onconflict; /* ON CONFLICT clause, or NULL */
+ ForPortionOfExpr *forPortionOf; /* FOR PORTION OF clause for UPDATE/DELETE */
int epqParam; /* ID of Param for EvalPlanQual re-eval */
List *mergeActionLists; /* per-target-table lists of actions for
* MERGE */
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 1b787fe031..2bca7086e2 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -243,6 +243,7 @@ typedef struct ModifyTable
List *rowMarks; /* PlanRowMarks (non-locking only) */
int epqParam; /* ID of Param for EvalPlanQual re-eval */
OnConflictAction onConflictAction; /* ON CONFLICT action */
+ Node *forPortionOf; /* FOR PORTION OF clause for UPDATE/DELETE */
List *arbiterIndexes; /* List of ON CONFLICT arbiter index OIDs */
List *onConflictSet; /* INSERT ON CONFLICT DO UPDATE targetlist */
List *onConflictCols; /* target column numbers for onConflictSet */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 60d72a876b..b3c6f6cecf 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -2041,4 +2041,29 @@ typedef struct OnConflictExpr
List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */
} OnConflictExpr;
+/*----------
+ * ForPortionOfExpr - represents a FOR PORTION OF ... expression
+ *
+ * We set up an expression to make a range from the FROM/TO bounds,
+ * so that we can use range operators with it.
+ *
+ * Then we set up an overlaps expression between that and the range column,
+ * so that we can find the rows we need to update/delete.
+ *
+ * In the executor we'll also build an intersect expression between the
+ * targeted range and the range column, so that we can update the start/end
+ * bounds of the UPDATE'd record.
+ *----------
+ */
+typedef struct ForPortionOfExpr
+{
+ NodeTag type;
+ Var *rangeVar; /* Range column */
+ char *range_name; /* Range name */
+ Node *targetRange; /* FOR PORTION OF bounds as a range */
+ Oid rangeType; /* type of targetRange */
+ Node *overlapsExpr; /* range && targetRange */
+ List *rangeSet; /* List of TargetEntrys to set the time column(s) */
+} ForPortionOfExpr;
+
#endif /* PRIMNODES_H */
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 6e557bebc4..c932d1204c 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -283,7 +283,8 @@ extern ModifyTablePath *create_modifytable_path(PlannerInfo *root,
List *updateColnosLists,
List *withCheckOptionLists, List *returningLists,
List *rowMarks, OnConflictExpr *onconflict,
- List *mergeActionLists, int epqParam);
+ List *mergeActionLists,
+ ForPortionOfExpr *forPortionOf, int epqParam);
extern LimitPath *create_limit_path(PlannerInfo *root, RelOptInfo *rel,
Path *subpath,
Node *limitOffset, Node *limitCount,
diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h
index c96483ae78..b6d5065e12 100644
--- a/src/include/parser/analyze.h
+++ b/src/include/parser/analyze.h
@@ -43,7 +43,8 @@ extern List *transformInsertRow(ParseState *pstate, List *exprlist,
List *stmtcols, List *icolumns, List *attrnos,
bool strip_indirection);
extern List *transformUpdateTargetList(ParseState *pstate,
- List *origTlist);
+ List *origTlist,
+ ForPortionOfExpr *forPortionOf);
extern Query *transformTopLevelStmt(ParseState *pstate, RawStmt *parseTree);
extern Query *transformStmt(ParseState *pstate, Node *parseTree);
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index ccf5bf8aa6..c89ceea9f6 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -330,6 +330,7 @@ PG_KEYWORD("period", PERIOD, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("portion", PORTION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("position", POSITION, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("preceding", PRECEDING, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("precision", PRECISION, COL_NAME_KEYWORD, AS_LABEL)
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index f589112d5e..0d601f34a5 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -56,6 +56,7 @@ typedef enum ParseExprKind
EXPR_KIND_UPDATE_SOURCE, /* UPDATE assignment source item */
EXPR_KIND_UPDATE_TARGET, /* UPDATE assignment target item */
EXPR_KIND_MERGE_WHEN, /* MERGE WHEN [NOT] MATCHED condition */
+ EXPR_KIND_UPDATE_PORTION, /* UPDATE FOR PORTION OF item */
EXPR_KIND_GROUP_BY, /* GROUP BY */
EXPR_KIND_ORDER_BY, /* ORDER BY */
EXPR_KIND_DISTINCT_ON, /* DISTINCT ON */
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index f5fdbfe116..a117527d8a 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -143,6 +143,8 @@ extern char get_rel_persistence(Oid relid);
extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes);
extern Oid get_transform_tosql(Oid typid, Oid langid, List *trftypes);
extern bool get_typisdefined(Oid typid);
+extern char *get_typname(Oid typid);
+extern bool get_typname_and_namespace(Oid typid, char **typname, char **typnamespace);
extern int16 get_typlen(Oid typid);
extern bool get_typbyval(Oid typid);
extern void get_typlenbyval(Oid typid, int16 *typlen, bool *typbyval);
diff --git a/src/include/utils/period.h b/src/include/utils/period.h
new file mode 100644
index 0000000000..0a8af3edb0
--- /dev/null
+++ b/src/include/utils/period.h
@@ -0,0 +1,19 @@
+/*-------------------------------------------------------------------------
+ *
+ * period.h
+ * support for Postgres periods.
+ *
+ *
+ * Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/utils/period.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PERIOD_H
+#define PERIOD_H
+
+extern Datum period_to_range(TupleTableSlot *slot, int startattno, int endattno, Oid rangetype);
+
+#endif /* PERIOD_H */
diff --git a/src/include/utils/rangetypes.h b/src/include/utils/rangetypes.h
index 6b420a8618..48ee2debed 100644
--- a/src/include/utils/rangetypes.h
+++ b/src/include/utils/rangetypes.h
@@ -164,5 +164,8 @@ extern RangeType *make_empty_range(TypeCacheEntry *typcache);
extern bool range_split_internal(TypeCacheEntry *typcache, const RangeType *r1,
const RangeType *r2, RangeType **output1,
RangeType **output2);
+extern void range_leftover_internal(TypeCacheEntry *typcache, const RangeType *r1,
+ const RangeType *r2, RangeType **output1,
+ RangeType **output2);
#endif /* RANGETYPES_H */
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
new file mode 100644
index 0000000000..30919b55e5
--- /dev/null
+++ b/src/test/regress/expected/for_portion_of.out
@@ -0,0 +1,431 @@
+-- Tests for UPDATE/DELETE FOR PORTION OF
+-- Works on non-PK columns
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid_at tsrange,
+ name text NOT NULL
+);
+INSERT INTO for_portion_of_test VALUES
+('[1,2)', '[2018-01-02,2020-01-01)', 'one');
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
+SET name = 'foo';
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM '2019-01-15' TO UNBOUNDED;
+SELECT * FROM for_portion_of_test;
+ id | valid_at | name
+-------+---------------------------------------------------------+------
+ [1,2) | ["Mon Jan 15 00:00:00 2018","Tue Jan 01 00:00:00 2019") | foo
+ [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | one
+ [1,2) | ["Tue Jan 01 00:00:00 2019","Tue Jan 15 00:00:00 2019") | one
+(3 rows)
+
+-- Works on more than one range
+DROP TABLE for_portion_of_test;
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid1_at tsrange,
+ valid2_at tsrange,
+ name text NOT NULL
+);
+INSERT INTO for_portion_of_test VALUES
+('[1,2)', '[2018-01-02,2018-02-03)', '[2015-01-01,2025-01-01)', 'one');
+UPDATE for_portion_of_test
+FOR PORTION OF valid1_at FROM '2018-01-15' TO UNBOUNDED
+SET name = 'foo';
+SELECT * FROM for_portion_of_test;
+ id | valid1_at | valid2_at | name
+-------+---------------------------------------------------------+---------------------------------------------------------+------
+ [1,2) | ["Mon Jan 15 00:00:00 2018","Sat Feb 03 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Wed Jan 01 00:00:00 2025") | foo
+ [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Wed Jan 01 00:00:00 2025") | one
+(2 rows)
+
+UPDATE for_portion_of_test
+FOR PORTION OF valid2_at FROM '2018-01-15' TO UNBOUNDED
+SET name = 'bar';
+SELECT * FROM for_portion_of_test;
+ id | valid1_at | valid2_at | name
+-------+---------------------------------------------------------+---------------------------------------------------------+------
+ [1,2) | ["Mon Jan 15 00:00:00 2018","Sat Feb 03 00:00:00 2018") | ["Mon Jan 15 00:00:00 2018","Wed Jan 01 00:00:00 2025") | bar
+ [1,2) | ["Mon Jan 15 00:00:00 2018","Sat Feb 03 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Mon Jan 15 00:00:00 2018") | foo
+ [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | ["Mon Jan 15 00:00:00 2018","Wed Jan 01 00:00:00 2025") | bar
+ [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Mon Jan 15 00:00:00 2018") | one
+(4 rows)
+
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid1_at FROM '2018-01-20' TO UNBOUNDED;
+SELECT * FROM for_portion_of_test;
+ id | valid1_at | valid2_at | name
+-------+---------------------------------------------------------+---------------------------------------------------------+------
+ [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | ["Mon Jan 15 00:00:00 2018","Wed Jan 01 00:00:00 2025") | bar
+ [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Mon Jan 15 00:00:00 2018") | one
+ [1,2) | ["Mon Jan 15 00:00:00 2018","Sat Jan 20 00:00:00 2018") | ["Mon Jan 15 00:00:00 2018","Wed Jan 01 00:00:00 2025") | bar
+ [1,2) | ["Mon Jan 15 00:00:00 2018","Sat Jan 20 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Mon Jan 15 00:00:00 2018") | foo
+(4 rows)
+
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid2_at FROM '2018-01-20' TO UNBOUNDED;
+SELECT * FROM for_portion_of_test;
+ id | valid1_at | valid2_at | name
+-------+---------------------------------------------------------+---------------------------------------------------------+------
+ [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Mon Jan 15 00:00:00 2018") | one
+ [1,2) | ["Mon Jan 15 00:00:00 2018","Sat Jan 20 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Mon Jan 15 00:00:00 2018") | foo
+ [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | ["Mon Jan 15 00:00:00 2018","Sat Jan 20 00:00:00 2018") | bar
+ [1,2) | ["Mon Jan 15 00:00:00 2018","Sat Jan 20 00:00:00 2018") | ["Mon Jan 15 00:00:00 2018","Sat Jan 20 00:00:00 2018") | bar
+(4 rows)
+
+DROP TABLE for_portion_of_test;
+CREATE TABLE for_portion_of_test (
+ id int4range NOT NULL,
+ valid_at tsrange NOT NULL,
+ name text NOT NULL,
+ CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO for_portion_of_test
+VALUES
+('[1,2)', '[2018-01-02,2018-02-03)', 'one'),
+('[1,2)', '[2018-02-03,2018-03-03)', 'one'),
+('[1,2)', '[2018-03-03,2018-04-04)', 'one'),
+('[2,3)', '[2018-01-01,2018-01-05)', 'two'),
+('[3,4)', '[2018-01-01,)', 'three'),
+('[4,5)', '(,2018-04-01)', 'four'),
+('[5,6)', '(,)', 'five')
+;
+--
+-- UPDATE tests
+--
+-- Setting with a missing column fails
+UPDATE for_portion_of_test
+FOR PORTION OF invalid_at FROM '2018-06-01' TO UNBOUNDED
+SET name = 'foo'
+WHERE id = '[5,6)';
+ERROR: column or period "invalid_at" of relation "for_portion_of_test" does not exist
+LINE 2: FOR PORTION OF invalid_at FROM '2018-06-01' TO UNBOUNDED
+ ^
+-- Setting the range fails
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-06-01' TO UNBOUNDED
+SET valid_at = '[1990-01-01,1999-01-01)'
+WHERE id = '[5,6)';
+ERROR: can't directly assign to "valid_at" in a FOR PORTION OF update
+LINE 3: SET valid_at = '[1990-01-01,1999-01-01)'
+ ^
+-- The wrong type fails
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM 1 TO 4
+SET name = 'nope'
+WHERE id = '[3,4)';
+ERROR: function pg_catalog.tsrange(integer, integer) does not exist
+LINE 2: FOR PORTION OF valid_at FROM 1 TO 4
+ ^
+HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+-- Setting with timestamps reversed fails
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01'
+SET name = 'three^1'
+WHERE id = '[3,4)';
+ERROR: range lower bound must be less than or equal to range upper bound
+-- Setting with a subquery fails
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01'
+SET name = 'nope'
+WHERE id = '[3,4)';
+ERROR: cannot use subquery in FOR PORTION OF expression
+LINE 2: FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-...
+ ^
+-- Setting with a column fails
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM lower(valid_at) TO UNBOUNDED
+SET name = 'nope'
+WHERE id = '[3,4)';
+ERROR: cannot use column reference in FOR PORTION OF expression
+LINE 2: FOR PORTION OF valid_at FROM lower(valid_at) TO UNBOUNDED
+ ^
+-- Setting with timestamps equal does nothing
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01'
+SET name = 'three^0'
+WHERE id = '[3,4)';
+-- Updating a finite/open portion with a finite/open target
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-06-01' TO UNBOUNDED
+SET name = 'three^1'
+WHERE id = '[3,4)';
+-- Updating a finite/open portion with an open/finite target
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM UNBOUNDED TO '2018-03-01'
+SET name = 'three^2'
+WHERE id = '[3,4)';
+-- Updating an open/finite portion with an open/finite target
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM UNBOUNDED TO '2018-02-01'
+SET name = 'four^1'
+WHERE id = '[4,5)';
+-- Updating an open/finite portion with a finite/open target
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2017-01-01' TO UNBOUNDED
+SET name = 'four^2'
+WHERE id = '[4,5)';
+-- Updating a finite/finite portion with an exact fit
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2017-01-01' TO '2018-02-01'
+SET name = 'four^3'
+WHERE id = '[4,5)';
+-- Updating an enclosed span
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM UNBOUNDED TO UNBOUNDED
+SET name = 'two^2'
+WHERE id = '[2,3)';
+-- Updating an open/open portion with a finite/finite target
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01'
+SET name = 'five^2'
+WHERE id = '[5,6)';
+-- Updating an enclosed span with separate protruding spans
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2017-01-01' TO '2020-01-01'
+SET name = 'five^3'
+WHERE id = '[5,6)';
+-- Updating multiple enclosed spans
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM NULL TO NULL
+SET name = 'one^2'
+WHERE id = '[1,2)';
+-- Updating with a shift/reduce conflict
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at
+ FROM '2018-03-01' AT TIME ZONE INTERVAL '1' HOUR TO MINUTE
+ TO '2019-01-01'
+SET name = 'one^3'
+WHERE id = '[1,2)';
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at
+ FROM '2018-03-01' AT TIME ZONE INTERVAL '2' HOUR
+ TO '2019-01-01'
+SET name = 'one^4'
+WHERE id = '[1,2)';
+ERROR: syntax error at or near "'2019-01-01'"
+LINE 4: TO '2019-01-01'
+ ^
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at
+ FROM ('2018-03-01' AT TIME ZONE INTERVAL '2' HOUR)
+ TO '2019-01-01'
+SET name = 'one^4'
+WHERE id = '[1,2)';
+-- Updating the non-range part of the PK:
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-02-15' TO NULL
+SET id = '[6,7)'
+WHERE id = '[1,2)';
+-- UPDATE with no WHERE clause
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2030-01-01' TO NULL
+SET name = name || '*';
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+ id | valid_at | name
+-------+---------------------------------------------------------+----------
+ [1,2) | ["Tue Jan 02 00:00:00 2018","Sat Feb 03 00:00:00 2018") | one^2
+ [1,2) | ["Sat Feb 03 00:00:00 2018","Thu Feb 15 00:00:00 2018") | one^2
+ [2,3) | ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:00:00 2018") | two^2
+ [3,4) | ["Mon Jan 01 00:00:00 2018","Thu Mar 01 00:00:00 2018") | three^2
+ [3,4) | ["Thu Mar 01 00:00:00 2018","Fri Jun 01 00:00:00 2018") | three
+ [3,4) | ["Fri Jun 01 00:00:00 2018","Tue Jan 01 00:00:00 2030") | three^1
+ [3,4) | ["Tue Jan 01 00:00:00 2030",) | three^1*
+ [4,5) | (,"Sun Jan 01 00:00:00 2017") | four^1
+ [4,5) | ["Sun Jan 01 00:00:00 2017","Thu Feb 01 00:00:00 2018") | four^3
+ [4,5) | ["Thu Feb 01 00:00:00 2018","Sun Apr 01 00:00:00 2018") | four^2
+ [5,6) | (,"Sun Jan 01 00:00:00 2017") | five
+ [5,6) | ["Sun Jan 01 00:00:00 2017","Mon Jan 01 00:00:00 2018") | five^3
+ [5,6) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | five^3
+ [5,6) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | five^3
+ [5,6) | ["Wed Jan 01 00:00:00 2020","Tue Jan 01 00:00:00 2030") | five
+ [5,6) | ["Tue Jan 01 00:00:00 2030",) | five*
+ [6,7) | ["Thu Feb 15 00:00:00 2018","Thu Mar 01 08:01:00 2018") | one^2
+ [6,7) | ["Thu Mar 01 08:01:00 2018","Thu Mar 01 10:00:00 2018") | one^3
+ [6,7) | ["Thu Mar 01 10:00:00 2018","Sat Mar 03 00:00:00 2018") | one^4
+ [6,7) | ["Sat Mar 03 00:00:00 2018","Wed Apr 04 00:00:00 2018") | one^4
+(20 rows)
+
+--
+-- DELETE tests
+--
+-- Deleting with a missing column fails
+DELETE FROM for_portion_of_test
+FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL
+WHERE id = '[5,6)';
+ERROR: column or period "invalid_at" of relation "for_portion_of_test" does not exist
+LINE 2: FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL
+ ^
+-- Deleting with timestamps reversed fails
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01'
+WHERE id = '[3,4)';
+ERROR: range lower bound must be less than or equal to range upper bound
+-- Deleting with timestamps equal does nothing
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01'
+WHERE id = '[3,4)';
+-- Deleting with a closed/closed target
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-06-01' TO '2020-06-01'
+WHERE id = '[5,6)';
+-- Deleting with a closed/open target
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-04-01' TO NULL
+WHERE id = '[3,4)';
+-- Deleting with an open/closed target
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM NULL TO '2018-02-08'
+WHERE id = '[1,2)';
+-- Deleting with an open/open target
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM NULL TO NULL
+WHERE id = '[6,7)';
+-- DELETE with no WHERE clause
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM '2025-01-01' TO NULL;
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+ id | valid_at | name
+-------+---------------------------------------------------------+---------
+ [1,2) | ["Thu Feb 08 00:00:00 2018","Thu Feb 15 00:00:00 2018") | one^2
+ [2,3) | ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:00:00 2018") | two^2
+ [3,4) | ["Mon Jan 01 00:00:00 2018","Thu Mar 01 00:00:00 2018") | three^2
+ [3,4) | ["Thu Mar 01 00:00:00 2018","Sun Apr 01 00:00:00 2018") | three
+ [4,5) | (,"Sun Jan 01 00:00:00 2017") | four^1
+ [4,5) | ["Sun Jan 01 00:00:00 2017","Thu Feb 01 00:00:00 2018") | four^3
+ [4,5) | ["Thu Feb 01 00:00:00 2018","Sun Apr 01 00:00:00 2018") | four^2
+ [5,6) | (,"Sun Jan 01 00:00:00 2017") | five
+ [5,6) | ["Sun Jan 01 00:00:00 2017","Mon Jan 01 00:00:00 2018") | five^3
+ [5,6) | ["Mon Jan 01 00:00:00 2018","Fri Jun 01 00:00:00 2018") | five^3
+ [5,6) | ["Mon Jun 01 00:00:00 2020","Wed Jan 01 00:00:00 2025") | five
+(11 rows)
+
+-- UPDATE ... RETURNING returns only the updated values (not the inserted side values)
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-02-15'
+SET name = 'three^3'
+WHERE id = '[3,4)'
+RETURNING *;
+ id | valid_at | name
+-------+---------------------------------------------------------+---------
+ [3,4) | ["Thu Feb 01 00:00:00 2018","Thu Feb 15 00:00:00 2018") | three^3
+(1 row)
+
+-- test that we run triggers on the UPDATE/DELETEd row and the INSERTed rows
+CREATE FUNCTION for_portion_of_trigger()
+RETURNS trigger
+AS
+$$
+BEGIN
+ RAISE NOTICE '% % % of %', TG_WHEN, TG_OP, NEW.valid_at, OLD.valid_at;
+ IF TG_OP = 'DELETE' THEN
+ RETURN OLD;
+ ELSE
+ RETURN NEW;
+ END IF;
+END;
+$$
+LANGUAGE plpgsql;
+CREATE TRIGGER trg_for_portion_of_before_insert
+ BEFORE INSERT ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger();
+CREATE TRIGGER trg_for_portion_of_after_insert
+ AFTER INSERT ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger();
+CREATE TRIGGER trg_for_portion_of_before_update
+ BEFORE UPDATE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger();
+CREATE TRIGGER trg_for_portion_of_after_update
+ AFTER UPDATE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger();
+CREATE TRIGGER trg_for_portion_of_before_delete
+ BEFORE DELETE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger();
+CREATE TRIGGER trg_for_portion_of_after_delete
+ AFTER DELETE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger();
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2021-01-01' TO '2022-01-01'
+SET name = 'five^4'
+WHERE id = '[5,6)';
+NOTICE: BEFORE UPDATE ["Fri Jan 01 00:00:00 2021","Sat Jan 01 00:00:00 2022") of ["Mon Jun 01 00:00:00 2020","Wed Jan 01 00:00:00 2025")
+NOTICE: BEFORE INSERT ["Mon Jun 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") of <NULL>
+NOTICE: BEFORE INSERT ["Sat Jan 01 00:00:00 2022","Wed Jan 01 00:00:00 2025") of <NULL>
+NOTICE: AFTER INSERT ["Mon Jun 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") of <NULL>
+NOTICE: AFTER INSERT ["Sat Jan 01 00:00:00 2022","Wed Jan 01 00:00:00 2025") of <NULL>
+NOTICE: AFTER UPDATE ["Fri Jan 01 00:00:00 2021","Sat Jan 01 00:00:00 2022") of ["Mon Jun 01 00:00:00 2020","Wed Jan 01 00:00:00 2025")
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM '2023-01-01' TO '2024-01-01'
+WHERE id = '[5,6)';
+NOTICE: BEFORE DELETE <NULL> of ["Sat Jan 01 00:00:00 2022","Wed Jan 01 00:00:00 2025")
+NOTICE: BEFORE INSERT ["Sat Jan 01 00:00:00 2022","Sun Jan 01 00:00:00 2023") of <NULL>
+NOTICE: BEFORE INSERT ["Mon Jan 01 00:00:00 2024","Wed Jan 01 00:00:00 2025") of <NULL>
+NOTICE: AFTER INSERT ["Sat Jan 01 00:00:00 2022","Sun Jan 01 00:00:00 2023") of <NULL>
+NOTICE: AFTER INSERT ["Mon Jan 01 00:00:00 2024","Wed Jan 01 00:00:00 2025") of <NULL>
+NOTICE: AFTER DELETE <NULL> of ["Sat Jan 01 00:00:00 2022","Wed Jan 01 00:00:00 2025")
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+ id | valid_at | name
+-------+---------------------------------------------------------+---------
+ [1,2) | ["Thu Feb 08 00:00:00 2018","Thu Feb 15 00:00:00 2018") | one^2
+ [2,3) | ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:00:00 2018") | two^2
+ [3,4) | ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018") | three^2
+ [3,4) | ["Thu Feb 01 00:00:00 2018","Thu Feb 15 00:00:00 2018") | three^3
+ [3,4) | ["Thu Feb 15 00:00:00 2018","Thu Mar 01 00:00:00 2018") | three^2
+ [3,4) | ["Thu Mar 01 00:00:00 2018","Sun Apr 01 00:00:00 2018") | three
+ [4,5) | (,"Sun Jan 01 00:00:00 2017") | four^1
+ [4,5) | ["Sun Jan 01 00:00:00 2017","Thu Feb 01 00:00:00 2018") | four^3
+ [4,5) | ["Thu Feb 01 00:00:00 2018","Sun Apr 01 00:00:00 2018") | four^2
+ [5,6) | (,"Sun Jan 01 00:00:00 2017") | five
+ [5,6) | ["Sun Jan 01 00:00:00 2017","Mon Jan 01 00:00:00 2018") | five^3
+ [5,6) | ["Mon Jan 01 00:00:00 2018","Fri Jun 01 00:00:00 2018") | five^3
+ [5,6) | ["Mon Jun 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | five
+ [5,6) | ["Fri Jan 01 00:00:00 2021","Sat Jan 01 00:00:00 2022") | five^4
+ [5,6) | ["Sat Jan 01 00:00:00 2022","Sun Jan 01 00:00:00 2023") | five
+ [5,6) | ["Mon Jan 01 00:00:00 2024","Wed Jan 01 00:00:00 2025") | five
+(16 rows)
+
+-- Test with a custom range type
+CREATE TYPE mydaterange AS range(subtype=date);
+CREATE TABLE for_portion_of_test2 (
+ id int4range NOT NULL,
+ valid_at mydaterange NOT NULL,
+ name text NOT NULL,
+ CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO for_portion_of_test2
+VALUES
+('[1,1]', '[2018-01-02,2018-02-03)', 'one'),
+('[1,1]', '[2018-02-03,2018-03-03)', 'one'),
+('[1,1]', '[2018-03-03,2018-04-04)', 'one'),
+('[2,2]', '[2018-01-01,2018-05-01)', 'two'),
+('[3,3]', '[2018-01-01,)', 'three');
+;
+UPDATE for_portion_of_test2
+FOR PORTION OF valid_at FROM '2018-01-10' TO '2018-02-10'
+SET name = 'one^1'
+WHERE id = '[1,1]';
+DELETE FROM for_portion_of_test2
+FOR PORTION OF valid_at FROM '2018-01-15' TO '2018-02-15'
+WHERE id = '[2,2]';
+SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [1,2) | [01-02-2018,01-10-2018) | one
+ [1,2) | [01-10-2018,02-03-2018) | one^1
+ [1,2) | [02-03-2018,02-10-2018) | one^1
+ [1,2) | [02-10-2018,03-03-2018) | one
+ [1,2) | [03-03-2018,04-04-2018) | one
+ [2,3) | [01-01-2018,01-15-2018) | two
+ [2,3) | [02-15-2018,05-01-2018) | two
+ [3,4) | [01-01-2018,) | three
+(8 rows)
+
+DROP TABLE for_portion_of_test2;
+DROP TYPE mydaterange;
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index c1e610e62f..cf2c7df900 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -985,6 +985,24 @@ ERROR: null value in column "b" of relation "errtst_part_2" violates not-null c
DETAIL: Failing row contains (a, b, c) = (aaaa, null, ccc).
SET SESSION AUTHORIZATION regress_priv_user1;
DROP TABLE errtst;
+-- test column-level privileges on the range/PERIOD used in FOR PORTION OF
+SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TABLE t1 (
+ c1 int4range,
+ valid_at tsrange,
+ CONSTRAINT t1pk PRIMARY KEY (c1, valid_at WITHOUT OVERLAPS)
+);
+GRANT SELECT ON t1 TO regress_priv_user2;
+GRANT SELECT ON t1 TO regress_priv_user3;
+GRANT UPDATE (c1) ON t1 TO regress_priv_user2;
+GRANT UPDATE (c1, valid_at) ON t1 TO regress_priv_user3;
+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)';
+SET SESSION AUTHORIZATION regress_priv_user1;
+DROP TABLE t1;
-- test column-level privileges when involved with DELETE
SET SESSION AUTHORIZATION regress_priv_user1;
ALTER TABLE atest6 ADD COLUMN three integer;
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 1950e6f281..f4b88bcc38 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -3023,6 +3023,38 @@ select * from uv_iocu_tab;
drop view uv_iocu_view;
drop table uv_iocu_tab;
+-- Check UPDATE FOR PORTION OF works correctly
+create table uv_fpo_tab (id int4range, valid_at tsrange, b float,
+ constraint pk_uv_fpo_tab primary key (id, valid_at without overlaps));
+insert into uv_fpo_tab values ('[1,1]', '[2020-01-01, 2030-01-01)', 0);
+create view uv_fpo_view as
+ select b, b+1 as c, valid_at, id, '2.0'::text as two from uv_fpo_tab;
+insert into uv_fpo_view (id, valid_at, b) values ('[1,1]', '[2010-01-01, 2020-01-01)', 1);
+select * from uv_fpo_view;
+ b | c | valid_at | id | two
+---+---+---------------------------------------------------------+-------+-----
+ 0 | 1 | ["Wed Jan 01 00:00:00 2020","Tue Jan 01 00:00:00 2030") | [1,2) | 2.0
+ 1 | 2 | ["Fri Jan 01 00:00:00 2010","Wed Jan 01 00:00:00 2020") | [1,2) | 2.0
+(2 rows)
+
+update uv_fpo_view for portion of valid_at from '2015-01-01' to '2020-01-01' set b = 2 where id = '[1,1]';
+select * from uv_fpo_view;
+ b | c | valid_at | id | two
+---+---+---------------------------------------------------------+-------+-----
+ 0 | 1 | ["Wed Jan 01 00:00:00 2020","Tue Jan 01 00:00:00 2030") | [1,2) | 2.0
+ 2 | 3 | ["Thu Jan 01 00:00:00 2015","Wed Jan 01 00:00:00 2020") | [1,2) | 2.0
+ 1 | 2 | ["Fri Jan 01 00:00:00 2010","Thu Jan 01 00:00:00 2015") | [1,2) | 2.0
+(3 rows)
+
+delete from uv_fpo_view for portion of valid_at from '2017-01-01' to '2022-01-01' where id = '[1,1]';
+select * from uv_fpo_view;
+ b | c | valid_at | id | two
+---+---+---------------------------------------------------------+-------+-----
+ 1 | 2 | ["Fri Jan 01 00:00:00 2010","Thu Jan 01 00:00:00 2015") | [1,2) | 2.0
+ 0 | 1 | ["Sat Jan 01 00:00:00 2022","Tue Jan 01 00:00:00 2030") | [1,2) | 2.0
+ 2 | 3 | ["Thu Jan 01 00:00:00 2015","Sun Jan 01 00:00:00 2017") | [1,2) | 2.0
+(3 rows)
+
-- Test whole-row references to the view
create table uv_iocu_tab (a int unique, b text);
create view uv_iocu_view as
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index e0c37ac601..f4b1297abd 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -269,6 +269,36 @@ INSERT INTO temporal3 (id, valid_at, id2, name)
('[1,1]', daterange('2000-01-01', '2010-01-01'), '[7,7]', 'foo'),
('[2,2]', daterange('2000-01-01', '2010-01-01'), '[9,9]', 'bar')
;
+UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01'
+ SET name = name || '1';
+UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-04-01' TO '2000-06-01'
+ SET name = name || '2'
+ WHERE id = '[2,2]';
+SELECT * FROM temporal3 ORDER BY id, valid_at;
+ id | valid_at | id2 | name
+-------+-------------------------+--------+-------
+ [1,2) | [01-01-2000,05-01-2000) | [7,8) | foo
+ [1,2) | [05-01-2000,07-01-2000) | [7,8) | foo1
+ [1,2) | [07-01-2000,01-01-2010) | [7,8) | foo
+ [2,3) | [01-01-2000,04-01-2000) | [9,10) | bar
+ [2,3) | [04-01-2000,05-01-2000) | [9,10) | bar2
+ [2,3) | [05-01-2000,06-01-2000) | [9,10) | bar12
+ [2,3) | [06-01-2000,07-01-2000) | [9,10) | bar1
+ [2,3) | [07-01-2000,01-01-2010) | [9,10) | bar
+(8 rows)
+
+-- conflicting id only:
+INSERT INTO temporal3 (id, valid_at, id2, name)
+ VALUES
+ ('[1,1]', daterange('2005-01-01', '2006-01-01'), '[8,8]', 'foo3');
+ERROR: conflicting key value violates exclusion constraint "temporal3_pk"
+DETAIL: Key (id, valid_at)=([1,2), [01-01-2005,01-01-2006)) conflicts with existing key (id, valid_at)=([1,2), [07-01-2000,01-01-2010)).
+-- conflicting id2 only:
+INSERT INTO temporal3 (id, valid_at, id2, name)
+ VALUES
+ ('[3,3]', daterange('2005-01-01', '2010-01-01'), '[9,9]', 'bar3');
+ERROR: conflicting key value violates exclusion constraint "temporal3_uniq"
+DETAIL: Key (id2, valid_at)=([9,10), [01-01-2005,01-01-2010)) conflicts with existing key (id2, valid_at)=([9,10), [07-01-2000,01-01-2010)).
DROP TABLE temporal3;
--
-- test changing the PK's dependencies
@@ -321,6 +351,36 @@ SELECT * FROM tp2 ORDER BY id, valid_at;
[3,4) | [01-01-2000,01-01-2010) | three
(1 row)
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ SET name = 'one2'
+ WHERE id = '[1,1]';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25'
+ SET id = '[4,4]'
+ WHERE name = 'one';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01'
+ SET id = '[2,2]'
+ WHERE name = 'three';
+DELETE FROM temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ WHERE id = '[3,3]';
+SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [1,2) | [01-01-2000,01-15-2000) | one
+ [1,2) | [01-15-2000,02-01-2000) | one2
+ [1,2) | [02-01-2000,02-15-2000) | one2
+ [1,2) | [02-15-2000,02-20-2000) | one
+ [1,2) | [02-25-2000,03-01-2000) | one
+ [2,3) | [01-01-2002,01-01-2003) | three
+ [3,4) | [01-01-2000,01-15-2000) | three
+ [3,4) | [02-15-2000,01-01-2002) | three
+ [3,4) | [01-01-2003,01-01-2010) | three
+ [4,5) | [02-20-2000,02-25-2000) | one
+(10 rows)
+
DROP TABLE temporal_partitioned;
-- temporal UNIQUE:
CREATE TABLE temporal_partitioned (
@@ -356,6 +416,36 @@ SELECT * FROM tp2 ORDER BY id, valid_at;
[3,4) | [01-01-2000,01-01-2010) | three
(1 row)
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ SET name = 'one2'
+ WHERE id = '[1,1]';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25'
+ SET id = '[4,4]'
+ WHERE name = 'one';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01'
+ SET id = '[2,2]'
+ WHERE name = 'three';
+DELETE FROM temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ WHERE id = '[3,3]';
+SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [1,2) | [01-01-2000,01-15-2000) | one
+ [1,2) | [01-15-2000,02-01-2000) | one2
+ [1,2) | [02-01-2000,02-15-2000) | one2
+ [1,2) | [02-15-2000,02-20-2000) | one
+ [1,2) | [02-25-2000,03-01-2000) | one
+ [2,3) | [01-01-2002,01-01-2003) | three
+ [3,4) | [01-01-2000,01-15-2000) | three
+ [3,4) | [02-15-2000,01-01-2002) | three
+ [3,4) | [01-01-2003,01-01-2010) | three
+ [4,5) | [02-20-2000,02-25-2000) | one
+(10 rows)
+
DROP TABLE temporal_partitioned;
--
-- test FK dependencies
@@ -542,6 +632,13 @@ UPDATE temporal_rng SET id = '[7,7]'
WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL: Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng".
+-- changing just a part fails:
+UPDATE temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
+SET id = '[7,7]'
+WHERE id = '[5,5]';
+ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL: Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng".
-- then delete the objecting FK record and the same PK update succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]';
UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01')
@@ -579,13 +676,25 @@ UPDATE temporal_rng SET id = '[7,7]'
WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL: Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng".
+-- changing an unreferenced part is okay:
+UPDATE temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03'
+SET id = '[7,7]'
+WHERE id = '[5,5]';
+-- changing just a part fails:
+UPDATE temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
+SET id = '[7,7]'
+WHERE id = '[5,5]';
+ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL: Key (id, valid_at)=([5,6), ["Wed Jan 03 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng".
-- then delete the objecting FK record and the same PK update succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]';
UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01')
WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
-- clean up:
DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,5]';
-DELETE FROM temporal_rng WHERE id = '[5,5]';
+DELETE FROM temporal_rng WHERE id IN ('[5,5]', '[7,7]');
--
-- test FK parent deletes NO ACTION
--
@@ -607,6 +716,12 @@ DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01',
DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL: Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng".
+-- deleting just a part fails:
+DELETE FROM temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
+WHERE id = '[5,5]';
+ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL: Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng".
-- then delete the objecting FK record and the same PK delete succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]';
DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
@@ -631,9 +746,19 @@ DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01',
DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL: Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng".
+-- deleting an unreferenced part is okay:
+DELETE FROM temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03'
+WHERE id = '[5,5]';
+-- deleting just a part fails:
+DELETE FROM temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
+WHERE id = '[5,5]';
+ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL: Key (id, valid_at)=([5,6), ["Wed Jan 03 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng".
-- then delete the objecting FK record and the same PK delete succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]';
-DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
+DELETE FROM temporal_rng WHERE id = '[5,5]';
--
-- test ON UPDATE/DELETE options
--
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 66b75fa12f..99cf6f18d6 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -48,7 +48,7 @@ test: create_index create_index_spgist create_view index_including index_includi
# ----------
# Another group of parallel tests
# ----------
-test: create_aggregate create_function_sql create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse
+test: create_aggregate create_function_sql create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse for_portion_of
# ----------
# sanity_check does a vacuum, affecting the sort order of SELECT *
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
new file mode 100644
index 0000000000..bf2ab093a6
--- /dev/null
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -0,0 +1,340 @@
+-- Tests for UPDATE/DELETE FOR PORTION OF
+
+-- Works on non-PK columns
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid_at tsrange,
+ name text NOT NULL
+);
+INSERT INTO for_portion_of_test VALUES
+('[1,2)', '[2018-01-02,2020-01-01)', 'one');
+
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
+SET name = 'foo';
+
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM '2019-01-15' TO UNBOUNDED;
+
+SELECT * FROM for_portion_of_test;
+
+-- Works on more than one range
+DROP TABLE for_portion_of_test;
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid1_at tsrange,
+ valid2_at tsrange,
+ name text NOT NULL
+);
+INSERT INTO for_portion_of_test VALUES
+('[1,2)', '[2018-01-02,2018-02-03)', '[2015-01-01,2025-01-01)', 'one');
+
+UPDATE for_portion_of_test
+FOR PORTION OF valid1_at FROM '2018-01-15' TO UNBOUNDED
+SET name = 'foo';
+SELECT * FROM for_portion_of_test;
+
+UPDATE for_portion_of_test
+FOR PORTION OF valid2_at FROM '2018-01-15' TO UNBOUNDED
+SET name = 'bar';
+SELECT * FROM for_portion_of_test;
+
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid1_at FROM '2018-01-20' TO UNBOUNDED;
+SELECT * FROM for_portion_of_test;
+
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid2_at FROM '2018-01-20' TO UNBOUNDED;
+SELECT * FROM for_portion_of_test;
+
+
+DROP TABLE for_portion_of_test;
+CREATE TABLE for_portion_of_test (
+ id int4range NOT NULL,
+ valid_at tsrange NOT NULL,
+ name text NOT NULL,
+ CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO for_portion_of_test
+VALUES
+('[1,2)', '[2018-01-02,2018-02-03)', 'one'),
+('[1,2)', '[2018-02-03,2018-03-03)', 'one'),
+('[1,2)', '[2018-03-03,2018-04-04)', 'one'),
+('[2,3)', '[2018-01-01,2018-01-05)', 'two'),
+('[3,4)', '[2018-01-01,)', 'three'),
+('[4,5)', '(,2018-04-01)', 'four'),
+('[5,6)', '(,)', 'five')
+;
+
+--
+-- UPDATE tests
+--
+
+-- Setting with a missing column fails
+UPDATE for_portion_of_test
+FOR PORTION OF invalid_at FROM '2018-06-01' TO UNBOUNDED
+SET name = 'foo'
+WHERE id = '[5,6)';
+
+-- Setting the range fails
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-06-01' TO UNBOUNDED
+SET valid_at = '[1990-01-01,1999-01-01)'
+WHERE id = '[5,6)';
+
+-- The wrong type fails
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM 1 TO 4
+SET name = 'nope'
+WHERE id = '[3,4)';
+
+-- Setting with timestamps reversed fails
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01'
+SET name = 'three^1'
+WHERE id = '[3,4)';
+
+-- Setting with a subquery fails
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01'
+SET name = 'nope'
+WHERE id = '[3,4)';
+
+-- Setting with a column fails
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM lower(valid_at) TO UNBOUNDED
+SET name = 'nope'
+WHERE id = '[3,4)';
+
+-- Setting with timestamps equal does nothing
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01'
+SET name = 'three^0'
+WHERE id = '[3,4)';
+
+-- Updating a finite/open portion with a finite/open target
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-06-01' TO UNBOUNDED
+SET name = 'three^1'
+WHERE id = '[3,4)';
+
+-- Updating a finite/open portion with an open/finite target
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM UNBOUNDED TO '2018-03-01'
+SET name = 'three^2'
+WHERE id = '[3,4)';
+
+-- Updating an open/finite portion with an open/finite target
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM UNBOUNDED TO '2018-02-01'
+SET name = 'four^1'
+WHERE id = '[4,5)';
+
+-- Updating an open/finite portion with a finite/open target
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2017-01-01' TO UNBOUNDED
+SET name = 'four^2'
+WHERE id = '[4,5)';
+
+-- Updating a finite/finite portion with an exact fit
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2017-01-01' TO '2018-02-01'
+SET name = 'four^3'
+WHERE id = '[4,5)';
+
+-- Updating an enclosed span
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM UNBOUNDED TO UNBOUNDED
+SET name = 'two^2'
+WHERE id = '[2,3)';
+
+-- Updating an open/open portion with a finite/finite target
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01'
+SET name = 'five^2'
+WHERE id = '[5,6)';
+
+-- Updating an enclosed span with separate protruding spans
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2017-01-01' TO '2020-01-01'
+SET name = 'five^3'
+WHERE id = '[5,6)';
+
+-- Updating multiple enclosed spans
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM NULL TO NULL
+SET name = 'one^2'
+WHERE id = '[1,2)';
+
+-- Updating with a shift/reduce conflict
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at
+ FROM '2018-03-01' AT TIME ZONE INTERVAL '1' HOUR TO MINUTE
+ TO '2019-01-01'
+SET name = 'one^3'
+WHERE id = '[1,2)';
+
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at
+ FROM '2018-03-01' AT TIME ZONE INTERVAL '2' HOUR
+ TO '2019-01-01'
+SET name = 'one^4'
+WHERE id = '[1,2)';
+
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at
+ FROM ('2018-03-01' AT TIME ZONE INTERVAL '2' HOUR)
+ TO '2019-01-01'
+SET name = 'one^4'
+WHERE id = '[1,2)';
+
+-- Updating the non-range part of the PK:
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-02-15' TO NULL
+SET id = '[6,7)'
+WHERE id = '[1,2)';
+
+-- UPDATE with no WHERE clause
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2030-01-01' TO NULL
+SET name = name || '*';
+
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+
+--
+-- DELETE tests
+--
+
+-- Deleting with a missing column fails
+DELETE FROM for_portion_of_test
+FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL
+WHERE id = '[5,6)';
+
+-- Deleting with timestamps reversed fails
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01'
+WHERE id = '[3,4)';
+
+-- Deleting with timestamps equal does nothing
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01'
+WHERE id = '[3,4)';
+
+-- Deleting with a closed/closed target
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-06-01' TO '2020-06-01'
+WHERE id = '[5,6)';
+
+-- Deleting with a closed/open target
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-04-01' TO NULL
+WHERE id = '[3,4)';
+
+-- Deleting with an open/closed target
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM NULL TO '2018-02-08'
+WHERE id = '[1,2)';
+
+-- Deleting with an open/open target
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM NULL TO NULL
+WHERE id = '[6,7)';
+
+-- DELETE with no WHERE clause
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM '2025-01-01' TO NULL;
+
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+
+-- UPDATE ... RETURNING returns only the updated values (not the inserted side values)
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-02-15'
+SET name = 'three^3'
+WHERE id = '[3,4)'
+RETURNING *;
+
+-- test that we run triggers on the UPDATE/DELETEd row and the INSERTed rows
+
+CREATE FUNCTION for_portion_of_trigger()
+RETURNS trigger
+AS
+$$
+BEGIN
+ RAISE NOTICE '% % % of %', TG_WHEN, TG_OP, NEW.valid_at, OLD.valid_at;
+ IF TG_OP = 'DELETE' THEN
+ RETURN OLD;
+ ELSE
+ RETURN NEW;
+ END IF;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_for_portion_of_before_insert
+ BEFORE INSERT ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger();
+CREATE TRIGGER trg_for_portion_of_after_insert
+ AFTER INSERT ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger();
+CREATE TRIGGER trg_for_portion_of_before_update
+ BEFORE UPDATE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger();
+CREATE TRIGGER trg_for_portion_of_after_update
+ AFTER UPDATE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger();
+CREATE TRIGGER trg_for_portion_of_before_delete
+ BEFORE DELETE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger();
+CREATE TRIGGER trg_for_portion_of_after_delete
+ AFTER DELETE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger();
+
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2021-01-01' TO '2022-01-01'
+SET name = 'five^4'
+WHERE id = '[5,6)';
+
+DELETE FROM for_portion_of_test
+FOR PORTION OF valid_at FROM '2023-01-01' TO '2024-01-01'
+WHERE id = '[5,6)';
+
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+
+-- Test with a custom range type
+
+CREATE TYPE mydaterange AS range(subtype=date);
+
+CREATE TABLE for_portion_of_test2 (
+ id int4range NOT NULL,
+ valid_at mydaterange NOT NULL,
+ name text NOT NULL,
+ CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO for_portion_of_test2
+VALUES
+('[1,1]', '[2018-01-02,2018-02-03)', 'one'),
+('[1,1]', '[2018-02-03,2018-03-03)', 'one'),
+('[1,1]', '[2018-03-03,2018-04-04)', 'one'),
+('[2,2]', '[2018-01-01,2018-05-01)', 'two'),
+('[3,3]', '[2018-01-01,)', 'three');
+;
+
+UPDATE for_portion_of_test2
+FOR PORTION OF valid_at FROM '2018-01-10' TO '2018-02-10'
+SET name = 'one^1'
+WHERE id = '[1,1]';
+
+DELETE FROM for_portion_of_test2
+FOR PORTION OF valid_at FROM '2018-01-15' TO '2018-02-15'
+WHERE id = '[2,2]';
+
+SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at;
+
+DROP TABLE for_portion_of_test2;
+DROP TYPE mydaterange;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index bf0035d96d..366be99a27 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -713,6 +713,24 @@ UPDATE errtst SET a = 'aaaa', b = NULL WHERE a = 'aaa';
SET SESSION AUTHORIZATION regress_priv_user1;
DROP TABLE errtst;
+-- test column-level privileges on the range/PERIOD used in FOR PORTION OF
+SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TABLE t1 (
+ c1 int4range,
+ valid_at tsrange,
+ CONSTRAINT t1pk PRIMARY KEY (c1, valid_at WITHOUT OVERLAPS)
+);
+GRANT SELECT ON t1 TO regress_priv_user2;
+GRANT SELECT ON t1 TO regress_priv_user3;
+GRANT UPDATE (c1) ON t1 TO regress_priv_user2;
+GRANT UPDATE (c1, valid_at) ON t1 TO regress_priv_user3;
+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_user1;
+DROP TABLE t1;
+
-- test column-level privileges when involved with DELETE
SET SESSION AUTHORIZATION regress_priv_user1;
ALTER TABLE atest6 ADD COLUMN three integer;
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
index eaee0b7e1d..f814701a43 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -1541,6 +1541,20 @@ select * from uv_iocu_tab;
drop view uv_iocu_view;
drop table uv_iocu_tab;
+-- Check UPDATE FOR PORTION OF works correctly
+create table uv_fpo_tab (id int4range, valid_at tsrange, b float,
+ constraint pk_uv_fpo_tab primary key (id, valid_at without overlaps));
+insert into uv_fpo_tab values ('[1,1]', '[2020-01-01, 2030-01-01)', 0);
+create view uv_fpo_view as
+ select b, b+1 as c, valid_at, id, '2.0'::text as two from uv_fpo_tab;
+
+insert into uv_fpo_view (id, valid_at, b) values ('[1,1]', '[2010-01-01, 2020-01-01)', 1);
+select * from uv_fpo_view;
+update uv_fpo_view for portion of valid_at from '2015-01-01' to '2020-01-01' set b = 2 where id = '[1,1]';
+select * from uv_fpo_view;
+delete from uv_fpo_view for portion of valid_at from '2017-01-01' to '2022-01-01' where id = '[1,1]';
+select * from uv_fpo_view;
+
-- Test whole-row references to the view
create table uv_iocu_tab (a int unique, b text);
create view uv_iocu_view as
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 8a03b14961..6d9ed8781d 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -203,6 +203,20 @@ INSERT INTO temporal3 (id, valid_at, id2, name)
('[1,1]', daterange('2000-01-01', '2010-01-01'), '[7,7]', 'foo'),
('[2,2]', daterange('2000-01-01', '2010-01-01'), '[9,9]', 'bar')
;
+UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01'
+ SET name = name || '1';
+UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-04-01' TO '2000-06-01'
+ SET name = name || '2'
+ WHERE id = '[2,2]';
+SELECT * FROM temporal3 ORDER BY id, valid_at;
+-- conflicting id only:
+INSERT INTO temporal3 (id, valid_at, id2, name)
+ VALUES
+ ('[1,1]', daterange('2005-01-01', '2006-01-01'), '[8,8]', 'foo3');
+-- conflicting id2 only:
+INSERT INTO temporal3 (id, valid_at, id2, name)
+ VALUES
+ ('[3,3]', daterange('2005-01-01', '2010-01-01'), '[9,9]', 'bar3');
DROP TABLE temporal3;
--
@@ -241,6 +255,22 @@ INSERT INTO temporal_partitioned VALUES
SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
SELECT * FROM tp1 ORDER BY id, valid_at;
SELECT * FROM tp2 ORDER BY id, valid_at;
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ SET name = 'one2'
+ WHERE id = '[1,1]';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25'
+ SET id = '[4,4]'
+ WHERE name = 'one';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01'
+ SET id = '[2,2]'
+ WHERE name = 'three';
+DELETE FROM temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ WHERE id = '[3,3]';
+SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
DROP TABLE temporal_partitioned;
-- temporal UNIQUE:
@@ -259,6 +289,22 @@ INSERT INTO temporal_partitioned VALUES
SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
SELECT * FROM tp1 ORDER BY id, valid_at;
SELECT * FROM tp2 ORDER BY id, valid_at;
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ SET name = 'one2'
+ WHERE id = '[1,1]';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25'
+ SET id = '[4,4]'
+ WHERE name = 'one';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01'
+ SET id = '[2,2]'
+ WHERE name = 'three';
+DELETE FROM temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ WHERE id = '[3,3]';
+SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
DROP TABLE temporal_partitioned;
--
@@ -440,6 +486,11 @@ WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
-- changing the scalar part fails:
UPDATE temporal_rng SET id = '[7,7]'
WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
+-- changing just a part fails:
+UPDATE temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
+SET id = '[7,7]'
+WHERE id = '[5,5]';
-- then delete the objecting FK record and the same PK update succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]';
UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01')
@@ -475,13 +526,23 @@ WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
-- changing the scalar part fails:
UPDATE temporal_rng SET id = '[7,7]'
WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
+-- changing an unreferenced part is okay:
+UPDATE temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03'
+SET id = '[7,7]'
+WHERE id = '[5,5]';
+-- changing just a part fails:
+UPDATE temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
+SET id = '[7,7]'
+WHERE id = '[5,5]';
-- then delete the objecting FK record and the same PK update succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]';
UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01')
WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
-- clean up:
DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,5]';
-DELETE FROM temporal_rng WHERE id = '[5,5]';
+DELETE FROM temporal_rng WHERE id IN ('[5,5]', '[7,7]');
--
-- test FK parent deletes NO ACTION
--
@@ -501,6 +562,10 @@ INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-
DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
-- a PK delete that fails because both are referenced:
DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
+-- deleting just a part fails:
+DELETE FROM temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
+WHERE id = '[5,5]';
-- then delete the objecting FK record and the same PK delete succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]';
DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
@@ -525,9 +590,17 @@ INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-
DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
-- a PK delete that fails because both are referenced:
DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
+-- deleting an unreferenced part is okay:
+DELETE FROM temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03'
+WHERE id = '[5,5]';
+-- deleting just a part fails:
+DELETE FROM temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
+WHERE id = '[5,5]';
-- then delete the objecting FK record and the same PK delete succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]';
-DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
+DELETE FROM temporal_rng WHERE id = '[5,5]';
--
-- test ON UPDATE/DELETE options
--
2.25.1