v11-0003-Add-UPDATE-DELETE-FOR-PORTION-OF.patch
text/x-patch
Filename: v11-0003-Add-UPDATE-DELETE-FOR-PORTION-OF.patch
Type: text/x-patch
Part: 1
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 v11-0003
Subject: Add UPDATE/DELETE FOR PORTION OF
| File | + | − |
|---|---|---|
| doc/src/sgml/ddl.sgml | 4 | 0 |
| 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 | 48 | 0 |
| src/backend/executor/execMain.c | 1 | 0 |
| src/backend/executor/nodeModifyTable.c | 244 | 7 |
| src/backend/nodes/nodeFuncs.c | 26 | 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 | 339 | 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 | 17 | 2 |
| 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 | 23 | 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/period.h | 19 | 0 |
| src/include/utils/rangetypes.h | 3 | 0 |
| src/test/regress/expected/for_portion_of.out | 797 | 0 |
| src/test/regress/expected/privileges.out | 36 | 0 |
| src/test/regress/expected/updatable_views.out | 32 | 0 |
| src/test/regress/expected/without_overlaps.out | 58 | 58 |
| src/test/regress/parallel_schedule | 1 | 1 |
| src/test/regress/sql/for_portion_of.sql | 628 | 0 |
| src/test/regress/sql/privileges.sql | 36 | 0 |
| src/test/regress/sql/updatable_views.sql | 14 | 0 |
| src/test/regress/sql/without_overlaps.sql | 30 | 30 |
From 745d1c7b58463e28c2bde86bfde76ef41674b6c9 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 v11 3/4] 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/ddl.sgml | 4 +
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 | 48 ++
src/backend/executor/execMain.c | 1 +
src/backend/executor/nodeModifyTable.c | 251 +++++-
src/backend/nodes/nodeFuncs.c | 26 +
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 | 344 +++++++-
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 | 19 +-
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 | 23 +
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/period.h | 19 +
src/include/utils/rangetypes.h | 3 +
src/test/regress/expected/for_portion_of.out | 797 ++++++++++++++++++
src/test/regress/expected/privileges.out | 36 +
src/test/regress/expected/updatable_views.out | 32 +
.../regress/expected/without_overlaps.out | 116 +--
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/for_portion_of.sql | 628 ++++++++++++++
src/test/regress/sql/privileges.sql | 36 +
src/test/regress/sql/updatable_views.sql | 14 +
src/test/regress/sql/without_overlaps.sql | 60 +-
46 files changed, 2778 insertions(+), 142 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/ddl.sgml b/doc/src/sgml/ddl.sgml
index f057879955..343e91b975 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1286,6 +1286,10 @@ CREATE TABLE billing_addresses (
and second a period (or range column) that qualifies when the row applies. So a temporal primary permits multiple rows with equal values in the ordinary key parts, as long as those rows don't have overlapping periods. Each row makes a statement about the entity identified by the ordinary key parts, but applying only to the span given by the period.
Temporal primary keys are essentially <link linkend="sql-createtable-exclude">exclusion constraints</link> where the first key parts are compared for equality and the last part for overlaps.
</para>
+
+ <para>
+ Any table with a temporal primary key supports temporal <literal>UPDATE</literal> and <literal>DELETE</literal> using <literal>FOR PORTION OF</literal> syntax. See <xref linkend="sql-update"/> and <xref linkend="sql-delete"/> for details.
+ </para>
</sect2>
<sect2 id="ddl-periods-system-periods">
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 1b81b4e7d7..868cf0d1f9 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">temporal
+ primary key</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..f2042e0b25 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">temporal
+ primary key</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 906f0a41a7..6117edc684 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -12275,6 +12275,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 335d31c4f0..a630c463a4 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"
@@ -2639,6 +2640,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];
@@ -2738,6 +2740,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;
@@ -2829,6 +2832,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);
@@ -2892,6 +2896,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];
@@ -3009,6 +3014,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++)
@@ -3158,6 +3164,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);
@@ -3624,6 +3631,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;
@@ -3897,6 +3905,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);
@@ -4439,6 +4448,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);
@@ -5975,6 +5985,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()
*
@@ -6390,6 +6437,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
else
new_shared.ats_table = NULL;
new_shared.ats_modifiedcols = 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 eaf6f31a15..7b03333cfb 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1244,6 +1244,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 dc1a2ec551..a3160a1f5d 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,164 @@ ExecInsert(ModifyTableContext *context,
return result;
}
+/* ----------------------------------------------------------------
+ * set_leftover_tuple_bounds
+ *
+ * Saves the new bounds into either the range attribute or the
+ * PERIOD start/end attributes.
+ * ----------------------------------------------------------------
+ */
+static void set_leftover_tuple_bounds(TupleTableSlot *leftoverTuple,
+ ForPortionOfExpr *forPortionOf,
+ TypeCacheEntry *typcache,
+ RangeType *leftoverRangeType)
+{
+ if (forPortionOf->rangeVar == NULL)
+ {
+ /* Store the lower/upper bounds to the PERIOD start/end cols */
+
+ RangeBound lower;
+ RangeBound upper;
+ bool empty;
+
+ range_deserialize(typcache, leftoverRangeType, &lower, &upper, &empty);
+
+ if (empty || lower.infinite)
+ leftoverTuple->tts_isnull[forPortionOf->startVar->varattno - 1] = true;
+ else
+ {
+ leftoverTuple->tts_values[forPortionOf->startVar->varattno - 1] = lower.val;
+ leftoverTuple->tts_isnull[forPortionOf->startVar->varattno - 1] = false;
+ }
+
+ if (empty || upper.infinite)
+ leftoverTuple->tts_isnull[forPortionOf->endVar->varattno - 1] = true;
+ else
+ {
+ leftoverTuple->tts_values[forPortionOf->endVar->varattno - 1] = upper.val;
+ leftoverTuple->tts_isnull[forPortionOf->endVar->varattno - 1] = false;
+ }
+ }
+ else
+ {
+ /* 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)
+{
+ // TODO: figure out if I need to make a coyp of the slot somehow in order to insert it...
+
+ 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;
+ TupleTableSlot *oldtupleSlot = resultRelInfo->ri_forPortionOf->fp_Existing;
+ TupleTableSlot *leftoverTuple1 = resultRelInfo->ri_forPortionOf->fp_Leftover1;
+ TupleTableSlot *leftoverTuple2 = resultRelInfo->ri_forPortionOf->fp_Leftover2;
+
+ /* Get the range of the existing pre-UPDATE/DELETE tuple */
+
+ // TODO: Seems like we shouldn't have to do this,
+ // because the old tuple should already be available somehow?
+ // But this is what triggers do.... (Are you sure this is how they get the OLD tuple?)
+ // And even if we do have to do this, is SnapshotAny really correct?
+ // Shouldn't it be the snapshot of the UPDATE?
+ if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc, tupleid, SnapshotAny, oldtupleSlot))
+ elog(ERROR, "failed to fetch tuple for FOR PORTION OF");
+
+ if (forPortionOf->rangeVar == NULL)
+ oldRange = period_to_range(oldtupleSlot, forPortionOf->startVar->varattno, forPortionOf->endVar->varattno, rangeTypeOid);
+ else
+ oldRange = slot_getattr(oldtupleSlot, forPortionOf->rangeVar->varattno, &isNull);
+
+
+ if (isNull)
+ elog(ERROR, "found a NULL range in a temporal table");
+ oldRangeType = DatumGetRangeTypeP(oldRange);
+
+ /* Evaluate the target range if we haven't yet */
+
+ targetRangeType = DatumGetRangeTypeP(resultRelInfo->ri_forPortionOf->fp_targetRange);
+
+ /*
+ * Get the range's type cache entry. This is worth caching for the whole UPDATE
+ * like range functions do.
+ */
+
+ typcache = resultRelInfo->ri_forPortionOf->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);
+ resultRelInfo->ri_forPortionOf->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.
+ */
+
+ if (!RangeIsEmpty(leftoverRangeType1))
+ {
+ MinimalTuple oldtuple = ExecFetchSlotMinimalTuple(oldtupleSlot, NULL);
+ ExecForceStoreMinimalTuple(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.
+ */
+
+ if (!RangeIsEmpty(leftoverRangeType2))
+ {
+ MinimalTuple oldtuple = ExecFetchSlotMinimalTuple(oldtupleSlot, NULL);
+ ExecForceStoreMinimalTuple(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,11 +1522,13 @@ 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,
- ItemPointer tupleid, HeapTuple oldtuple, bool changingPart)
+ ItemPointer tupleid, HeapTuple oldtuple, bool changingPart,
+ ForPortionOfExpr *forPortionOf)
{
ModifyTableState *mtstate = context->mtstate;
EState *estate = context->estate;
@@ -1390,6 +1557,11 @@ ExecDeleteEpilogue(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
ar_delete_trig_tcs = NULL;
}
+ /* Compute leftovers in FOR PORTION OF */
+ // TODO: Skip this for FDW deletes?
+ if (forPortionOf)
+ ExecForPortionOfLeftovers(context, estate, resultRelInfo, tupleid);
+
/* AFTER ROW DELETE Triggers */
ExecARDeleteTriggers(estate, resultRelInfo, tupleid, oldtuple,
ar_delete_trig_tcs, changingPart);
@@ -1661,7 +1833,8 @@ ldelete:
if (tupleDeleted)
*tupleDeleted = true;
- ExecDeleteEpilogue(context, resultRelInfo, tupleid, oldtuple, changingPart);
+ ExecDeleteEpilogue(context, resultRelInfo, tupleid, oldtuple, changingPart,
+ (ForPortionOfExpr *)((ModifyTable *) context->mtstate->ps.plan)->forPortionOf);
/* Process RETURNING if present and if requested */
if (processReturning && resultRelInfo->ri_projectReturning)
@@ -2120,7 +2293,8 @@ lreplace:
static void
ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt,
ResultRelInfo *resultRelInfo, ItemPointer tupleid,
- HeapTuple oldtuple, TupleTableSlot *slot)
+ HeapTuple oldtuple, TupleTableSlot *slot,
+ ForPortionOfExpr *forPortionOf)
{
ModifyTableState *mtstate = context->mtstate;
List *recheckIndexes = NIL;
@@ -2133,6 +2307,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 (forPortionOf)
+ ExecForPortionOfLeftovers(context, context->estate, resultRelInfo, tupleid);
+
/* AFTER ROW UPDATE Triggers */
ExecARUpdateTriggers(context->estate, resultRelInfo,
NULL, NULL,
@@ -2476,7 +2655,8 @@ redo_act:
(estate->es_processed)++;
ExecUpdateEpilogue(context, &updateCxt, resultRelInfo, tupleid, oldtuple,
- slot);
+ slot,
+ (ForPortionOfExpr *) ((ModifyTable *) context->mtstate->ps.plan)->forPortionOf);
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
@@ -2901,7 +3081,7 @@ lmerge_matched:
if (result == TM_Ok && updateCxt.updated)
{
ExecUpdateEpilogue(context, &updateCxt, resultRelInfo,
- tupleid, NULL, newslot);
+ tupleid, NULL, newslot, NULL);
mtstate->mt_merge_updated += 1;
}
break;
@@ -2919,7 +3099,7 @@ lmerge_matched:
if (result == TM_Ok)
{
ExecDeleteEpilogue(context, resultRelInfo, tupleid, NULL,
- false);
+ false, NULL);
mtstate->mt_merge_deleted += 1;
}
break;
@@ -4280,6 +4460,63 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
}
}
+ /*
+ * If needed, initialize the target range for FOR PORTION OF.
+ */
+ if (node->forPortionOf)
+ {
+ TupleDesc tupDesc = resultRelInfo->ri_RelationDesc->rd_att;
+ ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node->forPortionOf;
+ Datum targetRange;
+ bool isNull;
+ ExprContext *econtext;
+ ExprState *exprState;
+
+ /* 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 */
+
+ resultRelInfo->ri_forPortionOf = makeNode(ForPortionOfState);
+ resultRelInfo->ri_forPortionOf->fp_rangeName = forPortionOf->range_name;
+ resultRelInfo->ri_forPortionOf->fp_rangeType = forPortionOf->rangeType;
+ if (forPortionOf->rangeVar == NULL)
+ {
+ resultRelInfo->ri_forPortionOf->fp_hasPeriod = true;
+ resultRelInfo->ri_forPortionOf->fp_rangeAttno = InvalidAttrNumber;
+ resultRelInfo->ri_forPortionOf->fp_periodStartAttno = forPortionOf->startVar->varattno;
+ resultRelInfo->ri_forPortionOf->fp_periodEndAttno = forPortionOf->endVar->varattno;
+ } else {
+ resultRelInfo->ri_forPortionOf->fp_hasPeriod = false;
+ resultRelInfo->ri_forPortionOf->fp_rangeAttno = forPortionOf->rangeVar->varattno;
+ resultRelInfo->ri_forPortionOf->fp_periodStartAttno = InvalidAttrNumber;
+ resultRelInfo->ri_forPortionOf->fp_periodEndAttno = InvalidAttrNumber;
+ }
+ resultRelInfo->ri_forPortionOf->fp_targetRange = targetRange;
+
+ /* Initialize slot for the existing tuple */
+
+ resultRelInfo->ri_forPortionOf->fp_Existing =
+ table_slot_create(resultRelInfo->ri_RelationDesc,
+ &mtstate->ps.state->es_tupleTable);
+
+ /* Create the tuple slots for INSERTing the leftovers */
+
+ resultRelInfo->ri_forPortionOf->fp_Leftover1 =
+ ExecInitExtraTupleSlot(mtstate->ps.state, tupDesc, &TTSOpsVirtual);
+ resultRelInfo->ri_forPortionOf->fp_Leftover2 =
+ ExecInitExtraTupleSlot(mtstate->ps.state, tupDesc, &TTSOpsVirtual);
+
+ /* 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 a1169b77cd..9e7f1e4c93 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2425,6 +2425,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;
@@ -2565,6 +2573,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))
@@ -3407,6 +3417,21 @@ 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->startVar, fpo->startVar, Var *);
+ MUTATE(newnode->endVar, fpo->endVar, Var *);
+ MUTATE(newnode->targetRange, fpo->targetRange, Node *);
+ MUTATE(newnode->rangeSet, fpo->rangeSet, List *);
+
+ return (Node *) newnode;
+ }
+ break;
case T_PartitionPruneStepOp:
{
PartitionPruneStepOp *opstep = (PartitionPruneStepOp *) node;
@@ -3585,6 +3610,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 910ffbf1e1..eccef3e340 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);
@@ -2823,6 +2824,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);
@@ -6986,7 +6988,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;
@@ -7052,6 +7055,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 502ccbcea2..ecd571cdaa 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1948,6 +1948,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 5f5596841c..e7afd5586c 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3653,7 +3653,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);
@@ -3719,6 +3720,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 70932dba61..303f356c55 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -25,6 +25,7 @@
#include "postgres.h"
#include "access/sysattr.h"
+#include "catalog/pg_period.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
@@ -47,11 +48,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 +63,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);
@@ -465,6 +475,20 @@ analyze_requires_snapshot(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;
+}
+
/*
* transformDeleteStmt -
* transforms a Delete Statement
@@ -474,6 +498,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
{
Query *qry = makeNode(Query);
ParseNamespaceItem *nsitem;
+ Node *whereClause;
Node *qual;
qry->commandType = CMD_DELETE;
@@ -512,7 +537,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);
@@ -1135,7 +1164,7 @@ transformOnConflictClause(ParseState *pstate,
* Now transform the UPDATE subexpressions.
*/
onConflictSet =
- transformUpdateTargetList(pstate, onConflictClause->targetList);
+ transformUpdateTargetList(pstate, onConflictClause->targetList, NULL);
onConflictWhere = transformWhereClause(pstate,
onConflictClause->whereClause,
@@ -1165,6 +1194,278 @@ 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_name = NULL;
+ int range_attno = InvalidAttrNumber;
+ int start_attno = InvalidAttrNumber;
+ int end_attno = InvalidAttrNumber;
+ char *startcolname = NULL;
+ char *endcolname = NULL;
+ ForPortionOfExpr *result;
+ List *targetList;
+ Node *target_start, *target_end;
+ Expr *rangeExpr = NULL;
+ FuncCall *fc;
+
+ result = makeNode(ForPortionOfExpr);
+
+ /*
+ * Look up the FOR PORTION OF name requested.
+ * First look for a range column, then look for a period.
+ * If the relation is an updateable view, then only range columns are
+ * possible, since the standard doesn't give any way to define a period
+ * on a view.
+ */
+ range_attno = attnameAttNum(targetrel, range_name, false);
+ if (range_attno != InvalidAttrNumber)
+ {
+ Form_pg_attribute attr = TupleDescAttr(targetrel->rd_att, range_attno - 1);
+ Var *v;
+
+ // TODO: check attr->attisdropped ?
+
+ /* 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)));
+
+ v = makeVar(
+ rtindex,
+ range_attno,
+ attr->atttypid,
+ attr->atttypmod,
+ attr->attcollation,
+ 0);
+ v->location = forPortionOf->range_name_location;
+ rangeExpr = (Expr *) v;
+ result->rangeVar = copyObject(v);
+ result->startVar = NULL;
+ result->endVar = NULL;
+ result->rangeType = attr->atttypid;
+ range_type_name = get_typname(attr->atttypid);
+
+ } else {
+ Oid relid = RelationGetRelid(targetrel);
+ HeapTuple perTuple = SearchSysCache2(PERIODNAME,
+ ObjectIdGetDatum(relid),
+ PointerGetDatum(range_name));
+ if (HeapTupleIsValid(perTuple))
+ {
+ Form_pg_period per = (Form_pg_period) GETSTRUCT(perTuple);
+ Form_pg_attribute startattr, endattr;
+ Var *startvar, *endvar;
+
+ Type rngtype = typeidType(per->perrngtype);
+ range_type_name = typeTypeName(rngtype);
+ ReleaseSysCache(rngtype);
+ start_attno = per->perstart;
+ end_attno = per->perend;
+
+ startattr = TupleDescAttr(targetrel->rd_att, start_attno - 1);
+ endattr = TupleDescAttr(targetrel->rd_att, end_attno - 1);
+
+ startcolname = NameStr(startattr->attname);
+ endcolname = NameStr(endattr->attname);
+ result->period_start_name = startcolname;
+ result->period_end_name = endcolname;
+
+ startvar = makeVar(
+ rtindex,
+ per->perstart,
+ startattr->atttypid,
+ startattr->atttypmod,
+ startattr->attcollation,
+ 0);
+ startvar->location = forPortionOf->range_name_location;
+
+ endvar = makeVar(
+ rtindex,
+ per->perend,
+ endattr->atttypid,
+ endattr->atttypmod,
+ endattr->attcollation,
+ 0);
+ endvar->location = forPortionOf->range_name_location;
+
+ ReleaseSysCache(perTuple);
+
+ rangeExpr = (Expr *) makeFuncCall(SystemFuncName(range_type_name),
+ list_make2(startvar, endvar),
+ COERCE_EXPLICIT_CALL,
+ forPortionOf->range_name_location);
+ result->rangeVar = NULL;
+ result->startVar = copyObject(startvar);
+ result->endVar = copyObject(endvar);
+ result->rangeType = typenameTypeId(pstate, typeStringToTypeName(range_type_name, NULL));
+ }
+ }
+
+ if (rangeExpr == NULL)
+ 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)));
+
+ /*
+ * 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 MINVALUE and MAXVALUE like declarative partitions.
+ */
+ target_start = transformForPortionOfBound(forPortionOf->target_start, true);
+ target_end = transformForPortionOfBound(forPortionOf->target_end, false);
+ fc = makeFuncCall(SystemFuncName(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(rangeExpr), (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`.
+ * For a PERIOD with cols (s, e) this is `s = lower(tsrange(s, e) * targetRange)`
+ * and `e = upper(tsrange(s, e) * targetRange)` (of course not necessarily with
+ * tsrange, but with whatever range type is used there).
+ */
+ targetList = NIL;
+ if (range_attno != InvalidAttrNumber)
+ {
+ Expr *rangeSetExpr = (Expr *) makeSimpleA_Expr(AEXPR_OP, "*",
+ (Node *) copyObject(rangeExpr), (Node *) fc,
+ forPortionOf->range_name_location);
+ TargetEntry *tle;
+
+ 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);
+
+ } else {
+ Expr *intersectExpr;
+ Expr *boundSetExpr;
+ TargetEntry *tle;
+
+ /* Set up targetList for the PERIOD start column */
+
+ intersectExpr = (Expr *) makeSimpleA_Expr(AEXPR_OP, "*",
+ (Node *) copyObject(rangeExpr), (Node *) fc,
+ forPortionOf->range_name_location);
+
+ boundSetExpr = (Expr *) makeFuncCall(SystemFuncName("lower"),
+ list_make1(intersectExpr),
+ COERCE_EXPLICIT_CALL,
+ forPortionOf->range_name_location);
+ boundSetExpr = (Expr *) transformExpr(pstate, (Node *) boundSetExpr, EXPR_KIND_UPDATE_PORTION);
+
+ tle = makeTargetEntry(boundSetExpr,
+ start_attno,
+ startcolname,
+ false);
+
+ targetList = lappend(targetList, tle);
+
+ /* Set up targetList for the PERIOD end column */
+
+ boundSetExpr = (Expr *) makeFuncCall(SystemFuncName("upper"),
+ list_make1(intersectExpr),
+ COERCE_EXPLICIT_CALL,
+ forPortionOf->range_name_location);
+ boundSetExpr = (Expr *) transformExpr(pstate, (Node *) boundSetExpr, EXPR_KIND_UPDATE_PORTION);
+
+ tle = makeTargetEntry(boundSetExpr,
+ end_attno,
+ endcolname,
+ false);
+
+ targetList = lappend(targetList, tle);
+
+ /* Mark the bound columns as requiring update permissions */
+ target_perminfo->updatedCols = bms_add_member(target_perminfo->updatedCols,
+ start_attno - FirstLowInvalidHeapAttributeNumber);
+ target_perminfo->updatedCols = bms_add_member(target_perminfo->updatedCols,
+ end_attno - FirstLowInvalidHeapAttributeNumber);
+ }
+ result->rangeSet = targetList;
+ }
+ else
+ result->rangeSet = NIL;
+
+ result->range_name = range_name;
+
+ return result;
+}
/*
* BuildOnConflictExcludedTargetlist
@@ -2371,6 +2672,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
{
Query *qry = makeNode(Query);
ParseNamespaceItem *nsitem;
+ Node *whereClause;
Node *qual;
qry->commandType = CMD_UPDATE;
@@ -2388,6 +2690,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 */
@@ -2404,7 +2710,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);
@@ -2413,7 +2720,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;
@@ -2432,7 +2739,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;
@@ -2482,6 +2789,33 @@ 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/period columns,
+ * since that would conflict with the implicit updates.
+ */
+ if (forPortionOf != NULL)
+ {
+ if (forPortionOf->rangeVar != 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)));
+ }
+ else
+ {
+ if (attrno == forPortionOf->startVar->varattno || attrno == forPortionOf->endVar->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 70974081a5..ba6925ad89 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -256,6 +256,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;
@@ -548,6 +549,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
@@ -746,7 +748,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
@@ -827,6 +829,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
@@ -12168,14 +12180,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;
}
@@ -12238,6 +12252,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
@@ -12246,10 +12261,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;
}
@@ -13685,6 +13701,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
*/
@@ -17246,6 +17275,7 @@ unreserved_keyword:
| PASSWORD
| PLANS
| POLICY
+ | PORTION
| PRECEDING
| PREPARE
| PREPARED
@@ -17845,6 +17875,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 64356436ef..12291313bc 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -543,6 +543,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
@@ -1790,6 +1793,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
@@ -3028,6 +3034,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 d8866373b8..2235d81dfe 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 980dc1816f..7f5ee75251 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3472,6 +3472,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
@@ -3810,6 +3834,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 30b51bf4d3..7e6b579f64 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1559,7 +1559,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 375b17b9f3..e2695c0aa6 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -48,6 +48,7 @@
#include "utils/inval.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
+#include "utils/period.h"
#include "utils/rel.h"
#include "utils/rls.h"
#include "utils/ruleutils.h"
@@ -2463,8 +2464,22 @@ ri_ExtractValues(Relation rel, TupleTableSlot *slot,
for (int i = 0; i < riinfo->nkeys; i++)
{
- vals[i] = slot_getattr(slot, attnums[i], &isnull);
- nulls[i] = isnull ? 'n' : ' ';
+ if (attnums[i] != InvalidAttrNumber)
+ {
+ vals[i] = slot_getattr(slot, attnums[i], &isnull);
+ nulls[i] = isnull ? 'n' : ' ';
+ }
+ else
+ {
+ vals[i] = build_period_range(riinfo, slot, rel_is_pk);
+ nulls[i] = ' ';
+ }
+ }
+
+ if (hasForPortionOf)
+ {
+ vals[riinfo->nkeys] = forPortionOf;
+ nulls[riinfo->nkeys] = ' ';
}
}
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 96143c09b1..4980c1ae75 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;
@@ -426,6 +428,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
*
@@ -546,6 +570,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 d6b28d07ec..a4452d6d39 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -149,6 +149,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 */
@@ -1536,6 +1539,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
@@ -1879,12 +1895,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;
/* ----------------------
@@ -1893,13 +1910,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 cf28416da8..b06a472b78 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2345,6 +2345,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 659bd05c0c..96f8aea6a0 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -246,6 +246,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 // TODO: Instead of re-using Expr here, break it into pieces like onConflict{Action,Set,Where}? */
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 be9c29f0bf..90853dc2df 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1988,4 +1988,27 @@ typedef struct OnConflictExpr
List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */
} OnConflictExpr;
+/*----------
+ * ForPortionOfExpr - represents a FOR PORTION OF ... expression
+ *
+ * The contents here will vary depending on whether we are targeting a range
+ * column or a PERIOD. For PERIODs we hold extra info about the start/end
+ * columns.
+ *----------
+ */
+typedef struct ForPortionOfExpr
+{
+ NodeTag type;
+ Var *rangeVar; /* Range column */
+ Var *startVar; /* PERIOD start column */
+ Var *endVar; /* PERIOD end column */
+ char *range_name; /* Range or PERIOD name */
+ char *period_start_name; /* PERIOD start column name */
+ char *period_end_name; /* PERIOD end column 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 69be701b16..1f25e2fc3f 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -280,7 +280,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 1cef1833a6..084e17187f 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 199cd74738..ecae82ed55 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -328,6 +328,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/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..e3179a0fbf
--- /dev/null
+++ b/src/test/regress/expected/for_portion_of.out
@@ -0,0 +1,797 @@
+-- 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 period
+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)
+
+--
+-- Now re-run the same tests but with a PERIOD instead of a range:
+--
+DROP TABLE for_portion_of_test;
+-- Works on non-PK columns
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid_from timestamp,
+ valid_til timestamp,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ 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_from | valid_til | 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 period
+DROP TABLE for_portion_of_test;
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid1_from timestamp,
+ valid1_til timestamp,
+ valid2_from timestamp,
+ valid2_til timestamp,
+ PERIOD FOR valid1_at (valid1_from, valid1_til),
+ PERIOD FOR valid2_at (valid2_from, valid2_til),
+ 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_from | valid1_til | valid2_from | valid2_til | 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_from | valid1_til | valid2_from | valid2_til | 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_from | valid1_til | valid2_from | valid2_til | 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_from | valid1_til | valid2_from | valid2_til | 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_from timestamp,
+ valid_til timestamp,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ 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', null, 'three'),
+('[4,5)', null, '2018-04-01', 'four'),
+('[5,6)', null, null, '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 start column fails
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-06-01' TO UNBOUNDED
+SET valid_from = '1990-01-01'
+WHERE id = '[5,6)';
+ERROR: can't directly assign to "valid_from" in a FOR PORTION OF update
+LINE 3: SET valid_from = '1990-01-01'
+ ^
+-- Setting the end column fails
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-06-01' TO UNBOUNDED
+SET valid_til = '1999-01-01'
+WHERE id = '[5,6)';
+ERROR: can't directly assign to "valid_til" in a FOR PORTION OF update
+LINE 3: SET valid_til = '1999-01-01'
+ ^
+-- 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 valid_from 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 valid_from 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 || '*';
+-- TODO: UPDATE with generated columns too
+SELECT * FROM for_portion_of_test ORDER BY id, valid_from NULLS FIRST, valid_til;
+ id | valid_from | valid_til | 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_from NULLS FIRST, valid_til;
+ id | valid_from | valid_til | 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_from | valid_til | 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_trigger2()
+RETURNS trigger
+AS
+$$
+BEGIN
+ RAISE NOTICE '% % [%,%) of [%,%)', TG_WHEN, TG_OP, NEW.valid_from, NEW.valid_til, OLD.valid_from, OLD.valid_til;
+ 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_trigger2();
+CREATE TRIGGER trg_for_portion_of_after_insert
+ AFTER INSERT ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger2();
+CREATE TRIGGER trg_for_portion_of_before_update
+ BEFORE UPDATE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger2();
+CREATE TRIGGER trg_for_portion_of_after_update
+ AFTER UPDATE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger2();
+CREATE TRIGGER trg_for_portion_of_before_delete
+ BEFORE DELETE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger2();
+CREATE TRIGGER trg_for_portion_of_after_delete
+ AFTER DELETE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger2();
+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>,<NULL>)
+NOTICE: BEFORE INSERT [Sat Jan 01 00:00:00 2022,Wed Jan 01 00:00:00 2025) of [<NULL>,<NULL>)
+NOTICE: AFTER INSERT [Mon Jun 01 00:00:00 2020,Fri Jan 01 00:00:00 2021) of [<NULL>,<NULL>)
+NOTICE: AFTER INSERT [Sat Jan 01 00:00:00 2022,Wed Jan 01 00:00:00 2025) of [<NULL>,<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>,<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>,<NULL>)
+NOTICE: BEFORE INSERT [Mon Jan 01 00:00:00 2024,Wed Jan 01 00:00:00 2025) of [<NULL>,<NULL>)
+NOTICE: AFTER INSERT [Sat Jan 01 00:00:00 2022,Sun Jan 01 00:00:00 2023) of [<NULL>,<NULL>)
+NOTICE: AFTER INSERT [Mon Jan 01 00:00:00 2024,Wed Jan 01 00:00:00 2025) of [<NULL>,<NULL>)
+NOTICE: AFTER DELETE [<NULL>,<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_from NULLS FIRST, valid_til;
+ id | valid_from | valid_til | 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)
+
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 3cf4ac8c9e..23232b45af 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -985,6 +985,42 @@ 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;
+CREATE TABLE t1 (
+ c1 int4range,
+ valid_from timestamp,
+ valid_til timestamp,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ 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_from, valid_til) 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 0cbedc657d..c09d947462 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -2991,6 +2991,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 83bd29c98e..98b2950f3c 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -454,7 +454,7 @@ DETAIL: Failing row contains ([3,4), null).
--
CREATE TABLE temporal3 (
id int4range,
- valid_at tsrange,
+ valid_at daterange,
id2 int8range,
name TEXT,
CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
@@ -462,105 +462,105 @@ CREATE TABLE temporal3 (
);
INSERT INTO temporal3 (id, valid_at, id2, name)
VALUES
- ('[1,1]', tsrange('2000-01-01', '2010-01-01'), '[7,7]', 'foo'),
- ('[2,2]', tsrange('2000-01-01', '2010-01-01'), '[9,9]', 'bar')
+ ('[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 without_overlaps_test2 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01'
+UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01'
SET name = name || '1';
-UPDATE without_overlaps_test2 FOR PORTION OF valid_at FROM '2000-04-01' TO '2000-06-01'
+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 without_overlaps_test2 ORDER BY id, valid_at;
- id | valid_at | id2 | name
--------+---------------------------------------------------------+--------+-------
- [1,2) | ["Sat Jan 01 00:00:00 2000","Mon May 01 00:00:00 2000") | [7,8) | foo
- [1,2) | ["Mon May 01 00:00:00 2000","Sat Jul 01 00:00:00 2000") | [7,8) | foo1
- [1,2) | ["Sat Jul 01 00:00:00 2000","Fri Jan 01 00:00:00 2010") | [7,8) | foo
- [2,3) | ["Sat Jan 01 00:00:00 2000","Sat Apr 01 00:00:00 2000") | [9,10) | bar
- [2,3) | ["Sat Apr 01 00:00:00 2000","Mon May 01 00:00:00 2000") | [9,10) | bar2
- [2,3) | ["Mon May 01 00:00:00 2000","Thu Jun 01 00:00:00 2000") | [9,10) | bar12
- [2,3) | ["Thu Jun 01 00:00:00 2000","Sat Jul 01 00:00:00 2000") | [9,10) | bar1
- [2,3) | ["Sat Jul 01 00:00:00 2000","Fri Jan 01 00:00:00 2010") | [9,10) | bar
+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 without_overlaps_test2 (id, valid_at, id2, name)
+INSERT INTO temporal3 (id, valid_at, id2, name)
VALUES
- ('[1,1]', tsrange('2005-01-01', '2006-01-01'), '[8,8]', 'foo3');
-ERROR: conflicting key value violates exclusion constraint "without_overlaps_test2_pk"
-DETAIL: Key (id, valid_at)=([1,2), ["Sat Jan 01 00:00:00 2005","Sun Jan 01 00:00:00 2006")) conflicts with existing key (id, valid_at)=([1,2), ["Sat Jul 01 00:00:00 2000","Fri Jan 01 00:00:00 2010")).
+ ('[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 without_overlaps_test2 (id, valid_at, id2, name)
+INSERT INTO temporal3 (id, valid_at, id2, name)
VALUES
- ('[3,3]', tsrange('2005-01-01', '2010-01-01'), '[9,9]', 'bar3')
+ ('[3,3]', daterange('2005-01-01', '2010-01-01'), '[9,9]', 'bar3')
;
-ERROR: conflicting key value violates exclusion constraint "without_overlaps_test2_uniq"
-DETAIL: Key (id2, valid_at)=([9,10), ["Sat Jan 01 00:00:00 2005","Fri Jan 01 00:00:00 2010")) conflicts with existing key (id2, valid_at)=([9,10), ["Sat Jul 01 00:00:00 2000","Fri Jan 01 00:00:00 2010")).
-DROP TABLE without_overlaps_test2;
+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 a PERIOD with both a PK and a UNIQUE constraint
--
-CREATE TABLE without_overlaps_test2 (
+CREATE TABLE temporal3 (
id int4range,
- valid_from timestamp,
- valid_til timestamp,
+ valid_from date,
+ valid_til date,
PERIOD FOR valid_at (valid_from, valid_til),
id2 int8range,
name TEXT,
- CONSTRAINT without_overlaps_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
- CONSTRAINT without_overlaps_test2_uniq UNIQUE (id2, valid_at WITHOUT OVERLAPS)
+ CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+ CONSTRAINT temporal3_uniq UNIQUE (id2, valid_at WITHOUT OVERLAPS)
);
-INSERT INTO without_overlaps_test2 (id, valid_from, valid_til, id2, name)
+INSERT INTO temporal3 (id, valid_from, valid_til, id2, name)
VALUES
('[1,1]', '2000-01-01', '2010-01-01', '[7,7]', 'foo'),
('[2,2]', '2000-01-01', '2010-01-01', '[9,9]', 'bar')
;
-UPDATE without_overlaps_test2 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01'
+UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01'
SET name = name || '1';
-UPDATE without_overlaps_test2 FOR PORTION OF valid_at FROM '2000-04-01' TO '2000-06-01'
+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 without_overlaps_test2 ORDER BY id, valid_from, valid_til;
- id | valid_from | valid_til | id2 | name
--------+--------------------------+--------------------------+--------+-------
- [1,2) | Sat Jan 01 00:00:00 2000 | Mon May 01 00:00:00 2000 | [7,8) | foo
- [1,2) | Mon May 01 00:00:00 2000 | Sat Jul 01 00:00:00 2000 | [7,8) | foo1
- [1,2) | Sat Jul 01 00:00:00 2000 | Fri Jan 01 00:00:00 2010 | [7,8) | foo
- [2,3) | Sat Jan 01 00:00:00 2000 | Sat Apr 01 00:00:00 2000 | [9,10) | bar
- [2,3) | Sat Apr 01 00:00:00 2000 | Mon May 01 00:00:00 2000 | [9,10) | bar2
- [2,3) | Mon May 01 00:00:00 2000 | Thu Jun 01 00:00:00 2000 | [9,10) | bar12
- [2,3) | Thu Jun 01 00:00:00 2000 | Sat Jul 01 00:00:00 2000 | [9,10) | bar1
- [2,3) | Sat Jul 01 00:00:00 2000 | Fri Jan 01 00:00:00 2010 | [9,10) | bar
+SELECT * FROM temporal3 ORDER BY id, valid_from, valid_til;
+ id | valid_from | valid_til | 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 without_overlaps_test2 (id, valid_from, valid_til, id2, name)
+INSERT INTO temporal3 (id, valid_from, valid_til, id2, name)
VALUES
('[1,1]', '2005-01-01', '2006-01-01', '[8,8]', 'foo3');
-ERROR: conflicting key value violates exclusion constraint "without_overlaps_test2_pk"
-DETAIL: Key (id, tsrange(valid_from, valid_til))=([1,2), ["Sat Jan 01 00:00:00 2005","Sun Jan 01 00:00:00 2006")) conflicts with existing key (id, tsrange(valid_from, valid_til))=([1,2), ["Sat Jul 01 00:00:00 2000","Fri Jan 01 00:00:00 2010")).
+ERROR: conflicting key value violates exclusion constraint "temporal3_pk"
+DETAIL: Key (id, daterange(valid_from, valid_til))=([1,2), [01-01-2005,01-01-2006)) conflicts with existing key (id, daterange(valid_from, valid_til))=([1,2), [07-01-2000,01-01-2010)).
-- conflicting id2 only:
-INSERT INTO without_overlaps_test2 (id, valid_from, valid_til, id2, name)
+INSERT INTO temporal3 (id, valid_from, valid_til, id2, name)
VALUES
('[3,3]', '2005-01-01', '2010-01-01', '[9,9]', 'bar3')
;
-ERROR: conflicting key value violates exclusion constraint "without_overlaps_test2_uniq"
-DETAIL: Key (id2, tsrange(valid_from, valid_til))=([9,10), ["Sat Jan 01 00:00:00 2005","Fri Jan 01 00:00:00 2010")) conflicts with existing key (id2, tsrange(valid_from, valid_til))=([9,10), ["Sat Jul 01 00:00:00 2000","Fri Jan 01 00:00:00 2010")).
-DROP TABLE without_overlaps_test2;
+ERROR: conflicting key value violates exclusion constraint "temporal3_uniq"
+DETAIL: Key (id2, daterange(valid_from, valid_til))=([9,10), [01-01-2005,01-01-2010)) conflicts with existing key (id2, daterange(valid_from, valid_til))=([9,10), [07-01-2000,01-01-2010)).
+DROP TABLE temporal3;
--
-- test changing the PK's dependencies
--
-CREATE TABLE without_overlaps_test2 (
+CREATE TABLE temporal3 (
id int4range,
valid_at tsrange,
- CONSTRAINT without_overlaps2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+ CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);
-ALTER TABLE without_overlaps_test2 ALTER COLUMN valid_at DROP NOT NULL;
+ALTER TABLE temporal3 ALTER COLUMN valid_at DROP NOT NULL;
ERROR: column "valid_at" is in a primary key
-ALTER TABLE without_overlaps_test2 ALTER COLUMN valid_at TYPE tstzrange USING tstzrange(lower(valid_at), upper(valid_at));
-ALTER TABLE without_overlaps_test2 RENAME COLUMN valid_at TO valid_thru;
-ALTER TABLE without_overlaps_test2 DROP COLUMN valid_thru;
-DROP TABLE without_overlaps_test2;
+ALTER TABLE temporal3 ALTER COLUMN valid_at TYPE tstzrange USING tstzrange(lower(valid_at), upper(valid_at));
+ALTER TABLE temporal3 RENAME COLUMN valid_at TO valid_thru;
+ALTER TABLE temporal3 DROP COLUMN valid_thru;
+DROP TABLE temporal3;
--
-- test PARTITION BY for ranges
--
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index f36e15a0fe..202c320ed8 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 periods 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 periods 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..bc7f0bab96
--- /dev/null
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -0,0 +1,628 @@
+-- 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 period
+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;
+
+--
+-- Now re-run the same tests but with a PERIOD instead of a range:
+--
+
+DROP TABLE for_portion_of_test;
+
+-- Works on non-PK columns
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid_from timestamp,
+ valid_til timestamp,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ 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 period
+DROP TABLE for_portion_of_test;
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid1_from timestamp,
+ valid1_til timestamp,
+ valid2_from timestamp,
+ valid2_til timestamp,
+ PERIOD FOR valid1_at (valid1_from, valid1_til),
+ PERIOD FOR valid2_at (valid2_from, valid2_til),
+ 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_from timestamp,
+ valid_til timestamp,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ 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', null, 'three'),
+('[4,5)', null, '2018-04-01', 'four'),
+('[5,6)', null, null, '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 start column fails
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-06-01' TO UNBOUNDED
+SET valid_from = '1990-01-01'
+WHERE id = '[5,6)';
+
+-- Setting the end column fails
+UPDATE for_portion_of_test
+FOR PORTION OF valid_at FROM '2018-06-01' TO UNBOUNDED
+SET valid_til = '1999-01-01'
+WHERE id = '[5,6)';
+
+-- 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 valid_from 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 || '*';
+
+-- TODO: UPDATE with generated columns too
+SELECT * FROM for_portion_of_test ORDER BY id, valid_from NULLS FIRST, valid_til;
+
+--
+-- 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_from NULLS FIRST, valid_til;
+
+-- 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_trigger2()
+RETURNS trigger
+AS
+$$
+BEGIN
+ RAISE NOTICE '% % [%,%) of [%,%)', TG_WHEN, TG_OP, NEW.valid_from, NEW.valid_til, OLD.valid_from, OLD.valid_til;
+ 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_trigger2();
+CREATE TRIGGER trg_for_portion_of_after_insert
+ AFTER INSERT ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger2();
+CREATE TRIGGER trg_for_portion_of_before_update
+ BEFORE UPDATE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger2();
+CREATE TRIGGER trg_for_portion_of_after_update
+ AFTER UPDATE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger2();
+CREATE TRIGGER trg_for_portion_of_before_delete
+ BEFORE DELETE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger2();
+CREATE TRIGGER trg_for_portion_of_after_delete
+ AFTER DELETE ON for_portion_of_test
+ FOR EACH ROW
+ EXECUTE FUNCTION for_portion_of_trigger2();
+
+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_from NULLS FIRST, valid_til;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 134809e8cc..568ce97b60 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -713,6 +713,42 @@ 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;
+
+CREATE TABLE t1 (
+ c1 int4range,
+ valid_from timestamp,
+ valid_til timestamp,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ 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_from, valid_til) 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 7739b3be76..9845b3a817 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -1524,6 +1524,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 bdfdc50967..4634a5ecc9 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -303,7 +303,7 @@ INSERT INTO temporal_rng VALUES ('[3,3]', NULL);
CREATE TABLE temporal3 (
id int4range,
- valid_at tsrange,
+ valid_at daterange,
id2 int8range,
name TEXT,
CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
@@ -311,77 +311,77 @@ CREATE TABLE temporal3 (
);
INSERT INTO temporal3 (id, valid_at, id2, name)
VALUES
- ('[1,1]', tsrange('2000-01-01', '2010-01-01'), '[7,7]', 'foo'),
- ('[2,2]', tsrange('2000-01-01', '2010-01-01'), '[9,9]', 'bar')
+ ('[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 without_overlaps_test2 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01'
+UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01'
SET name = name || '1';
-UPDATE without_overlaps_test2 FOR PORTION OF valid_at FROM '2000-04-01' TO '2000-06-01'
+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 without_overlaps_test2 ORDER BY id, valid_at;
+SELECT * FROM temporal3 ORDER BY id, valid_at;
-- conflicting id only:
-INSERT INTO without_overlaps_test2 (id, valid_at, id2, name)
+INSERT INTO temporal3 (id, valid_at, id2, name)
VALUES
- ('[1,1]', tsrange('2005-01-01', '2006-01-01'), '[8,8]', 'foo3');
+ ('[1,1]', daterange('2005-01-01', '2006-01-01'), '[8,8]', 'foo3');
-- conflicting id2 only:
-INSERT INTO without_overlaps_test2 (id, valid_at, id2, name)
+INSERT INTO temporal3 (id, valid_at, id2, name)
VALUES
- ('[3,3]', tsrange('2005-01-01', '2010-01-01'), '[9,9]', 'bar3')
+ ('[3,3]', daterange('2005-01-01', '2010-01-01'), '[9,9]', 'bar3')
;
-DROP TABLE without_overlaps_test2;
+DROP TABLE temporal3;
--
-- test a PERIOD with both a PK and a UNIQUE constraint
--
-CREATE TABLE without_overlaps_test2 (
+CREATE TABLE temporal3 (
id int4range,
- valid_from timestamp,
- valid_til timestamp,
+ valid_from date,
+ valid_til date,
PERIOD FOR valid_at (valid_from, valid_til),
id2 int8range,
name TEXT,
- CONSTRAINT without_overlaps_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
- CONSTRAINT without_overlaps_test2_uniq UNIQUE (id2, valid_at WITHOUT OVERLAPS)
+ CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+ CONSTRAINT temporal3_uniq UNIQUE (id2, valid_at WITHOUT OVERLAPS)
);
-INSERT INTO without_overlaps_test2 (id, valid_from, valid_til, id2, name)
+INSERT INTO temporal3 (id, valid_from, valid_til, id2, name)
VALUES
('[1,1]', '2000-01-01', '2010-01-01', '[7,7]', 'foo'),
('[2,2]', '2000-01-01', '2010-01-01', '[9,9]', 'bar')
;
-UPDATE without_overlaps_test2 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01'
+UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01'
SET name = name || '1';
-UPDATE without_overlaps_test2 FOR PORTION OF valid_at FROM '2000-04-01' TO '2000-06-01'
+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 without_overlaps_test2 ORDER BY id, valid_from, valid_til;
+SELECT * FROM temporal3 ORDER BY id, valid_from, valid_til;
-- conflicting id only:
-INSERT INTO without_overlaps_test2 (id, valid_from, valid_til, id2, name)
+INSERT INTO temporal3 (id, valid_from, valid_til, id2, name)
VALUES
('[1,1]', '2005-01-01', '2006-01-01', '[8,8]', 'foo3');
-- conflicting id2 only:
-INSERT INTO without_overlaps_test2 (id, valid_from, valid_til, id2, name)
+INSERT INTO temporal3 (id, valid_from, valid_til, id2, name)
VALUES
('[3,3]', '2005-01-01', '2010-01-01', '[9,9]', 'bar3')
;
-DROP TABLE without_overlaps_test2;
+DROP TABLE temporal3;
--
-- test changing the PK's dependencies
--
-CREATE TABLE without_overlaps_test2 (
+CREATE TABLE temporal3 (
id int4range,
valid_at tsrange,
- CONSTRAINT without_overlaps2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+ CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);
-ALTER TABLE without_overlaps_test2 ALTER COLUMN valid_at DROP NOT NULL;
-ALTER TABLE without_overlaps_test2 ALTER COLUMN valid_at TYPE tstzrange USING tstzrange(lower(valid_at), upper(valid_at));
-ALTER TABLE without_overlaps_test2 RENAME COLUMN valid_at TO valid_thru;
-ALTER TABLE without_overlaps_test2 DROP COLUMN valid_thru;
-DROP TABLE without_overlaps_test2;
+ALTER TABLE temporal3 ALTER COLUMN valid_at DROP NOT NULL;
+ALTER TABLE temporal3 ALTER COLUMN valid_at TYPE tstzrange USING tstzrange(lower(valid_at), upper(valid_at));
+ALTER TABLE temporal3 RENAME COLUMN valid_at TO valid_thru;
+ALTER TABLE temporal3 DROP COLUMN valid_thru;
+DROP TABLE temporal3;
--
-- test PARTITION BY for ranges
--
2.25.1