v11-0002-Add-temporal-PRIMARY-KEY-and-UNIQUE-constraints.patch
text/x-patch
Filename: v11-0002-Add-temporal-PRIMARY-KEY-and-UNIQUE-constraints.patch
Type: text/x-patch
Part: 2
Message:
Re: SQL:2011 application time
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v11-0002
Subject: Add temporal PRIMARY KEY and UNIQUE constraints
| File | + | − |
|---|---|---|
| doc/src/sgml/ddl.sgml | 6 | 0 |
| doc/src/sgml/ref/create_table.sgml | 32 | 6 |
| src/backend/catalog/heap.c | 2 | 0 |
| src/backend/catalog/index.c | 56 | 7 |
| src/backend/catalog/pg_constraint.c | 14 | 1 |
| src/backend/catalog/toasting.c | 1 | 0 |
| src/backend/commands/indexcmds.c | 176 | 7 |
| src/backend/commands/tablecmds.c | 15 | 3 |
| src/backend/commands/trigger.c | 2 | 0 |
| src/backend/commands/typecmds.c | 2 | 0 |
| src/backend/nodes/makefuncs.c | 2 | 1 |
| src/backend/parser/gram.y | 21 | 11 |
| src/backend/parser/parse_utilcmd.c | 206 | 1 |
| src/backend/utils/adt/ruleutils.c | 47 | 9 |
| src/backend/utils/cache/lsyscache.c | 26 | 0 |
| src/backend/utils/cache/relcache.c | 15 | 5 |
| src/bin/pg_dump/pg_dump.c | 68 | 10 |
| src/bin/pg_dump/pg_dump.h | 2 | 0 |
| src/bin/pg_dump/t/002_pg_dump.pl | 96 | 0 |
| src/bin/psql/describe.c | 10 | 2 |
| src/include/catalog/index.h | 1 | 0 |
| src/include/catalog/pg_constraint.h | 11 | 1 |
| src/include/commands/defrem.h | 3 | 1 |
| src/include/nodes/execnodes.h | 4 | 0 |
| src/include/nodes/makefuncs.h | 1 | 1 |
| src/include/nodes/parsenodes.h | 4 | 0 |
| src/include/utils/lsyscache.h | 1 | 0 |
| src/test/regress/expected/without_overlaps.out | 585 | 0 |
| src/test/regress/parallel_schedule | 1 | 1 |
| src/test/regress/sql/without_overlaps.sql | 408 | 0 |
From 426ca3bed36d7fd20aaf3925075b156e74fb6452 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Mon, 28 Jun 2021 17:33:27 -0700
Subject: [PATCH v11 2/4] Add temporal PRIMARY KEY and UNIQUE constraints
- Added WITHOUT OVERLAPS to the bison grammar. We permit either range
columns or PERIODs.
- Temporal PRIMARY KEYs and UNIQUE constraints are backed by GiST
indexes instead of B-tree indexes, since they are essentially
exclusion constraints with = for the scalar parts of the key and &&
for the temporal part. With PERIODs we index a range expression using
the PERIOD start and end columns. With ranges we can just index the
column, so no expression is needed.
- Added pg_index.indperiod to record when a PERIOD is used in an index
constraint.
- Added pg_constraint.contemporal to say whether a constraint is a
temporal constraint.
- Added pg_constraint.conperiod to record the PERIOD oid if a PERIOD is
used. For range columns we can just include the column attnum, like
any other column appearing in a constraint.
- Added docs and tests.
- Added pg_dump support.
---
doc/src/sgml/ddl.sgml | 6 +
doc/src/sgml/ref/create_table.sgml | 38 +-
src/backend/catalog/heap.c | 2 +
src/backend/catalog/index.c | 63 +-
src/backend/catalog/pg_constraint.c | 15 +-
src/backend/catalog/toasting.c | 1 +
src/backend/commands/indexcmds.c | 183 +++++-
src/backend/commands/tablecmds.c | 18 +-
src/backend/commands/trigger.c | 2 +
src/backend/commands/typecmds.c | 2 +
src/backend/nodes/makefuncs.c | 3 +-
src/backend/parser/gram.y | 32 +-
src/backend/parser/parse_utilcmd.c | 207 ++++++-
src/backend/utils/adt/ruleutils.c | 56 +-
src/backend/utils/cache/lsyscache.c | 26 +
src/backend/utils/cache/relcache.c | 20 +-
src/bin/pg_dump/pg_dump.c | 78 ++-
src/bin/pg_dump/pg_dump.h | 2 +
src/bin/pg_dump/t/002_pg_dump.pl | 96 +++
src/bin/psql/describe.c | 12 +-
src/include/catalog/index.h | 1 +
src/include/catalog/pg_constraint.h | 12 +-
src/include/commands/defrem.h | 4 +-
src/include/nodes/execnodes.h | 4 +
src/include/nodes/makefuncs.h | 2 +-
src/include/nodes/parsenodes.h | 4 +
src/include/utils/lsyscache.h | 1 +
.../regress/expected/without_overlaps.out | 585 ++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/without_overlaps.sql | 408 ++++++++++++
30 files changed, 1818 insertions(+), 67 deletions(-)
create mode 100644 src/test/regress/expected/without_overlaps.out
create mode 100644 src/test/regress/sql/without_overlaps.sql
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index e722e53b2f..f057879955 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1280,6 +1280,12 @@ CREATE TABLE billing_addresses (
Application periods can be used to define temporal primary and foreign keys.
Any table with a temporal primary key is a temporal table and supports temporal update and delete commands.
</para>
+
+ <para>
+ Temporal primary keys enforce a modified version of referential integrity called temporal referential integrity. They have two kinds of element: first one or more columns that behave as in ordinary primary keys, uniquely determiniing an entity,
+ 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>
</sect2>
<sect2 id="ddl-periods-system-periods">
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 53f9ed55aa..b02486b940 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -85,8 +85,8 @@ PERIOD FOR { <replaceable class="parameter">period_name</replaceable> | SYSTEM_T
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
- UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
- PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">temporal_interval</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> |
+ PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">temporal_interval</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> |
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable
@@ -114,6 +114,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
+<phrase><replaceable class="parameter">temporal_interval</replaceable> in a <literal>PRIMARY KEY</literal>, <literal>UNIQUE</literal>, or <literal>FOREIGN KEY</literal> constraint is:</phrase>
+
+<replaceable class="parameter">range_column_name</replaceable> | <replaceable class="parameter">period_name</replaceable>
+
<phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase>
{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] | SET DEFAULT [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] }
@@ -1040,7 +1044,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Adding a unique constraint will automatically create a unique btree
- index on the column or group of columns used in the constraint.
+ index on the column or group of columns used in the constraint. But if
+ the constraint includes a <literal>WITHOUT OVERLAPS</literal> clause,
+ it will use a GiST index and behave like a temporal <literal>PRIMARY
+ KEY</literal>: preventing duplicates only in overlapping time periods.
</para>
<para>
@@ -1058,7 +1065,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<varlistentry id="sql-createtable-parms-primary-key">
<term><literal>PRIMARY KEY</literal> (column constraint)</term>
- <term><literal>PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
+ <term><literal>PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ]
+ [, <replaceable class="parameter">temporal_interval</replaceable> WITHOUT OVERLAPS ] )</literal>
<optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term>
<listitem>
<para>
@@ -1092,8 +1100,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Adding a <literal>PRIMARY KEY</literal> constraint will automatically
- create a unique btree index on the column or group of columns used in the
- constraint.
+ create a unique btree index (or GiST if temporal) on the column or group of
+ columns used in the constraint.
</para>
<para>
@@ -1106,6 +1114,24 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
(e.g., <literal>DROP COLUMN</literal>) can cause cascaded constraint and
index deletion.
</para>
+
+ <para>
+ A <literal>PRIMARY KEY</literal> with a <literal>WITHOUT OVERLAPS</literal> option
+ is a <emphasis>temporal</emphasis> primary key.
+ The <literal>WITHOUT OVERLAPS</literal> value
+ must be a period or range type and is used to constrain the record's applicability
+ to just that interval (usually a range of dates or timestamps).
+ The main part of the primary key may be repeated in other rows of the table,
+ as long as records with the same key don't overlap in the
+ <literal>WITHOUT OVERLAPS</literal> column.
+ </para>
+
+ <para>
+ A temporal <literal>PRIMARY KEY</literal> is enforced with an
+ <literal>EXCLUDE</literal> constraint rather than a <literal>UNIQUE</literal>
+ constraint, backed by a GiST index. You may need to install the
+ <xref linkend="btree-gist"/> extension to create temporal primary keys.
+ </para>
</listitem>
</varlistentry>
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index d7938898db..cf361921b4 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2218,6 +2218,8 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
is_local, /* conislocal */
inhcount, /* coninhcount */
is_no_inherit, /* connoinherit */
+ false, /* contemporal */
+ InvalidOid, /* conperiod */
is_internal); /* internally constructed? */
pfree(ccbin);
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 352e43d0e6..e648bf73d3 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -50,6 +50,7 @@
#include "catalog/pg_inherits.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_period.h"
#include "catalog/pg_tablespace.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -249,13 +250,16 @@ index_check_primary_key(Relation heapRel,
HeapTuple atttuple;
Form_pg_attribute attform;
- if (attnum == 0)
+ if (attnum == 0 && !(stmt->istemporal && i > 0))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("primary keys cannot be expressions")));
- /* System attributes are never null, so no need to check */
- if (attnum < 0)
+ /*
+ * System attributes are never null, so no need to check.
+ * Also skip expressions.
+ */
+ if (attnum <= 0)
continue;
atttuple = SearchSysCache2(ATTNUM,
@@ -561,6 +565,7 @@ UpdateIndexRelation(Oid indexoid,
bool isready)
{
int2vector *indkey;
+ Oid indperiod;
oidvector *indcollation;
oidvector *indclass;
int2vector *indoption;
@@ -579,6 +584,7 @@ UpdateIndexRelation(Oid indexoid,
indkey = buildint2vector(NULL, indexInfo->ii_NumIndexAttrs);
for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
indkey->values[i] = indexInfo->ii_IndexAttrNumbers[i];
+ indperiod = indexInfo->ii_Period ? ((PeriodDef *) indexInfo->ii_Period)->oid : InvalidOid;
indcollation = buildoidvector(collationOids, indexInfo->ii_NumIndexKeyAttrs);
indclass = buildoidvector(classOids, indexInfo->ii_NumIndexKeyAttrs);
indoption = buildint2vector(coloptions, indexInfo->ii_NumIndexKeyAttrs);
@@ -637,6 +643,7 @@ UpdateIndexRelation(Oid indexoid,
values[Anum_pg_index_indislive - 1] = BoolGetDatum(true);
values[Anum_pg_index_indisreplident - 1] = BoolGetDatum(false);
values[Anum_pg_index_indkey - 1] = PointerGetDatum(indkey);
+ values[Anum_pg_index_indperiod - 1] = ObjectIdGetDatum(indperiod);
values[Anum_pg_index_indcollation - 1] = PointerGetDatum(indcollation);
values[Anum_pg_index_indclass - 1] = PointerGetDatum(indclass);
values[Anum_pg_index_indoption - 1] = PointerGetDatum(indoption);
@@ -1295,6 +1302,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
Datum indclassDatum,
colOptionDatum,
optionDatum;
+ Oid periodid;
oidvector *indclass;
int2vector *indcoloptions;
bool isnull;
@@ -1328,6 +1336,9 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
Anum_pg_index_indoption);
indcoloptions = (int2vector *) DatumGetPointer(colOptionDatum);
+ /* Get the period */
+ periodid = oldInfo->ii_Period ? ((PeriodDef *) oldInfo->ii_Period)->oid : InvalidOid;
+
/* Fetch options of index if any */
classTuple = SearchSysCache1(RELOID, oldIndexId);
if (!HeapTupleIsValid(classTuple))
@@ -1380,7 +1391,8 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
oldInfo->ii_NullsNotDistinct,
false, /* not ready for inserts */
true,
- indexRelation->rd_indam->amsummarizing);
+ indexRelation->rd_indam->amsummarizing,
+ oldInfo->ii_Temporal);
/*
* Extract the list of column names and the column numbers for the new
@@ -1405,6 +1417,16 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
newInfo->ii_OpclassOptions[i] = get_attoptions(oldIndexId, i + 1);
}
+ /* Set the period */
+ if (periodid == InvalidOid)
+ newInfo->ii_Period = NULL;
+ else
+ {
+ PeriodDef *p = makeNode(PeriodDef);
+ p->oid = periodid;
+ newInfo->ii_Period = (Node *) p;
+ }
+
/*
* Now create the new index.
*
@@ -1900,6 +1922,7 @@ index_concurrently_set_dead(Oid heapId, Oid indexId)
* INDEX_CONSTR_CREATE_UPDATE_INDEX: update the pg_index row
* INDEX_CONSTR_CREATE_REMOVE_OLD_DEPS: remove existing dependencies
* of index on table's columns
+ * INDEX_CONSTR_CREATE_TEMPORAL: constraint is for a temporal primary key
* allow_system_table_mods: allow table to be a system catalog
* is_internal: index is constructed due to internal process
*/
@@ -1918,16 +1941,19 @@ index_constraint_create(Relation heapRelation,
ObjectAddress myself,
idxaddr;
Oid conOid;
+ Oid periodid;
bool deferrable;
bool initdeferred;
bool mark_as_primary;
bool islocal;
bool noinherit;
+ bool is_temporal;
int inhcount;
deferrable = (constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) != 0;
initdeferred = (constr_flags & INDEX_CONSTR_CREATE_INIT_DEFERRED) != 0;
mark_as_primary = (constr_flags & INDEX_CONSTR_CREATE_MARK_AS_PRIMARY) != 0;
+ is_temporal = (constr_flags & INDEX_CONSTR_CREATE_TEMPORAL) != 0;
/* constraint creation support doesn't work while bootstrapping */
Assert(!IsBootstrapProcessingMode());
@@ -1942,7 +1968,8 @@ index_constraint_create(Relation heapRelation,
/* primary/unique constraints shouldn't have any expressions */
if (indexInfo->ii_Expressions &&
- constraintType != CONSTRAINT_EXCLUSION)
+ constraintType != CONSTRAINT_EXCLUSION &&
+ !indexInfo->ii_Temporal)
elog(ERROR, "constraints cannot have index expressions");
/*
@@ -1971,6 +1998,11 @@ index_constraint_create(Relation heapRelation,
noinherit = true;
}
+ if (indexInfo->ii_Period != NULL)
+ periodid = ((PeriodDef *)indexInfo->ii_Period)->oid;
+ else
+ periodid = InvalidOid;
+
/*
* Construct a pg_constraint entry.
*/
@@ -2004,6 +2036,8 @@ index_constraint_create(Relation heapRelation,
islocal,
inhcount,
noinherit,
+ is_temporal, /* contemporal */
+ periodid, /* conperiod */
is_internal);
/*
@@ -2453,12 +2487,23 @@ BuildIndexInfo(Relation index)
indexStruct->indnullsnotdistinct,
indexStruct->indisready,
false,
- index->rd_indam->amsummarizing);
+ index->rd_indam->amsummarizing,
+ false);
/* fill in attribute numbers */
for (i = 0; i < numAtts; i++)
ii->ii_IndexAttrNumbers[i] = indexStruct->indkey.values[i];
+ /* set the period */
+ if (indexStruct->indperiod == InvalidOid)
+ ii->ii_Period = NULL;
+ else
+ {
+ PeriodDef *p = makeNode(PeriodDef);
+ p->oid = indexStruct->indperiod;
+ ii->ii_Period = (Node *) p;
+ }
+
/* fetch exclusion constraint info if any */
if (indexStruct->indisexclusion)
{
@@ -2514,12 +2559,16 @@ BuildDummyIndexInfo(Relation index)
indexStruct->indnullsnotdistinct,
indexStruct->indisready,
false,
- index->rd_indam->amsummarizing);
+ index->rd_indam->amsummarizing,
+ false);
/* fill in attribute numbers */
for (i = 0; i < numAtts; i++)
ii->ii_IndexAttrNumbers[i] = indexStruct->indkey.values[i];
+ /* no need for a period */
+ ii->ii_Period = NULL;
+
/* We ignore the exclusion constraint if any */
return ii;
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 4002317f70..3b5e8cf533 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -25,6 +25,7 @@
#include "catalog/objectaccess.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_period.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
#include "commands/tablecmds.h"
@@ -77,6 +78,8 @@ CreateConstraintEntry(const char *constraintName,
bool conIsLocal,
int conInhCount,
bool conNoInherit,
+ bool conTemporal,
+ Oid period,
bool is_internal)
{
Relation conDesc;
@@ -192,6 +195,8 @@ CreateConstraintEntry(const char *constraintName,
values[Anum_pg_constraint_conislocal - 1] = BoolGetDatum(conIsLocal);
values[Anum_pg_constraint_coninhcount - 1] = Int16GetDatum(conInhCount);
values[Anum_pg_constraint_connoinherit - 1] = BoolGetDatum(conNoInherit);
+ values[Anum_pg_constraint_contemporal - 1] = BoolGetDatum(conTemporal);
+ values[Anum_pg_constraint_conperiod - 1] = ObjectIdGetDatum(period);
if (conkeyArray)
values[Anum_pg_constraint_conkey - 1] = PointerGetDatum(conkeyArray);
@@ -248,7 +253,7 @@ CreateConstraintEntry(const char *constraintName,
{
/*
* Register auto dependency from constraint to owning relation, or to
- * specific column(s) if any are mentioned.
+ * specific column(s) and period if any are mentioned.
*/
ObjectAddress relobject;
@@ -266,6 +271,14 @@ CreateConstraintEntry(const char *constraintName,
ObjectAddressSet(relobject, RelationRelationId, relId);
add_exact_object_address(&relobject, addrs_auto);
}
+
+ if (OidIsValid(period))
+ {
+ ObjectAddress periodobject;
+
+ ObjectAddressSet(periodobject, PeriodRelationId, period);
+ add_exact_object_address(&periodobject, addrs_auto);
+ }
}
if (OidIsValid(domainId))
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index ab12b0b9de..e2bcb8a08d 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -292,6 +292,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
indexInfo->ii_NumIndexKeyAttrs = 2;
indexInfo->ii_IndexAttrNumbers[0] = 1;
indexInfo->ii_IndexAttrNumbers[1] = 2;
+ indexInfo->ii_Period = NULL;
indexInfo->ii_Expressions = NIL;
indexInfo->ii_ExpressionsState = NIL;
indexInfo->ii_Predicate = NIL;
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index e6ee99e51f..7e0607b47b 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -74,6 +74,11 @@
/* non-export function prototypes */
static bool CompareOpclassOptions(Datum *opts1, Datum *opts2, int natts);
static void CheckPredicate(Expr *predicate);
+static void get_index_attr_temporal_operator(Oid opclass,
+ Oid atttype,
+ bool isoverlaps,
+ Oid *opid,
+ int *strat);
static void ComputeIndexAttrs(IndexInfo *indexInfo,
Oid *typeOidP,
Oid *collationOidP,
@@ -85,9 +90,13 @@ static void ComputeIndexAttrs(IndexInfo *indexInfo,
const char *accessMethodName, Oid accessMethodId,
bool amcanorder,
bool isconstraint,
+ bool istemporal,
Oid ddl_userid,
int ddl_sec_context,
int *ddl_save_nestlevel);
+static void ComputeIndexPeriod(IndexInfo *indexInfo,
+ Oid relId,
+ const char *periodName);
static char *ChooseIndexName(const char *tabname, Oid namespaceId,
List *colnames, List *exclusionOpNames,
bool primary, bool isconstraint);
@@ -142,6 +151,10 @@ typedef struct ReindexErrorInfo
* to index on.
* 'exclusionOpNames': list of names of exclusion-constraint operators,
* or NIL if not an exclusion constraint.
+ * 'istemporal': true iff this index has a WITHOUT OVERLAPS clause.
+ * 'indexPeriodName': the name of the PERIOD used in WITHOUT OVERLAPS. If a
+ * range column is used, this should be NULL. We'll check that in
+ * attributeList.
*
* This is tailored to the needs of ALTER TABLE ALTER TYPE, which recreates
* any indexes that depended on a changing column from their pg_get_indexdef
@@ -171,7 +184,9 @@ bool
CheckIndexCompatible(Oid oldId,
const char *accessMethodName,
List *attributeList,
- List *exclusionOpNames)
+ List *exclusionOpNames,
+ bool istemporal,
+ const char *indexPeriodName)
{
bool isconstraint;
Oid *typeObjectId;
@@ -190,6 +205,8 @@ CheckIndexCompatible(Oid oldId,
int numberOfAttributes;
int old_natts;
bool ret = true;
+ Oid old_periodid;
+ Oid new_periodid;
oidvector *old_indclass;
oidvector *old_indcollation;
Relation irel;
@@ -234,7 +251,7 @@ CheckIndexCompatible(Oid oldId,
*/
indexInfo = makeIndexInfo(numberOfAttributes, numberOfAttributes,
accessMethodId, NIL, NIL, false, false,
- false, false, amsummarizing);
+ false, false, amsummarizing, false);
typeObjectId = palloc_array(Oid, numberOfAttributes);
collationObjectId = palloc_array(Oid, numberOfAttributes);
classObjectId = palloc_array(Oid, numberOfAttributes);
@@ -244,8 +261,10 @@ CheckIndexCompatible(Oid oldId,
coloptions, attributeList,
exclusionOpNames, relationId,
accessMethodName, accessMethodId,
- amcanorder, isconstraint, InvalidOid, 0, NULL);
+ amcanorder, isconstraint, istemporal, InvalidOid,
+ 0, NULL);
+ ComputeIndexPeriod(indexInfo, relationId, indexPeriodName);
/* Get the soon-obsolete pg_index tuple. */
tuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(oldId));
@@ -265,6 +284,22 @@ CheckIndexCompatible(Oid oldId,
return false;
}
+ /* The two indexes should agree on WITHOUT OVERLAPS. */
+ if (indexInfo->ii_Temporal != istemporal)
+ {
+ ReleaseSysCache(tuple);
+ return false;
+ }
+
+ /* The two indexes should have the same period. */
+ old_periodid = indexForm->indperiod;
+ new_periodid = indexInfo->ii_Period ? ((PeriodDef *) indexInfo->ii_Period)->oid : InvalidOid;
+ if (old_periodid != new_periodid)
+ {
+ ReleaseSysCache(tuple);
+ return false;
+ }
+
/* Any change in operator class or collation breaks compatibility. */
old_natts = indexForm->indnkeyatts;
Assert(old_natts == numberOfAttributes);
@@ -547,6 +582,7 @@ DefineIndex(Oid relationId,
Oid tablespaceId;
Oid createdConstraintId = InvalidOid;
List *indexColNames;
+ char *indexPeriodName;
List *allIndexParams;
Relation rel;
HeapTuple tuple;
@@ -803,6 +839,11 @@ DefineIndex(Oid relationId,
*/
indexColNames = ChooseIndexColumnNames(allIndexParams);
+ /*
+ * Choose the index period name.
+ */
+ indexPeriodName = stmt->period ? stmt->period->periodname : NULL;
+
/*
* Select name for index if caller didn't specify
*/
@@ -847,7 +888,7 @@ DefineIndex(Oid relationId,
pgstat_progress_update_param(PROGRESS_CREATEIDX_ACCESS_METHOD_OID,
accessMethodId);
- if (stmt->unique && !amRoutine->amcanunique)
+ if (stmt->unique && !stmt->istemporal && !amRoutine->amcanunique)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("access method \"%s\" does not support unique indexes",
@@ -903,7 +944,8 @@ DefineIndex(Oid relationId,
stmt->nulls_not_distinct,
!concurrent,
concurrent,
- amissummarizing);
+ amissummarizing,
+ stmt->istemporal);
typeObjectId = palloc_array(Oid, numberOfAttributes);
collationObjectId = palloc_array(Oid, numberOfAttributes);
@@ -914,8 +956,11 @@ DefineIndex(Oid relationId,
coloptions, allIndexParams,
stmt->excludeOpNames, relationId,
accessMethodName, accessMethodId,
- amcanorder, stmt->isconstraint, root_save_userid,
- root_save_sec_context, &root_save_nestlevel);
+ amcanorder, stmt->isconstraint, stmt->istemporal,
+ root_save_userid, root_save_sec_context,
+ &root_save_nestlevel);
+
+ ComputeIndexPeriod(indexInfo, relationId, indexPeriodName);
/*
* Extra checks when creating a PRIMARY KEY index.
@@ -940,6 +985,8 @@ DefineIndex(Oid relationId,
if (stmt->primary)
constraint_type = "PRIMARY KEY";
+ else if (stmt->unique && stmt->istemporal)
+ constraint_type = "temporal UNIQUE";
else if (stmt->unique)
constraint_type = "UNIQUE";
else if (stmt->excludeOpNames != NIL)
@@ -1159,6 +1206,8 @@ DefineIndex(Oid relationId,
constr_flags |= INDEX_CONSTR_CREATE_DEFERRABLE;
if (stmt->initdeferred)
constr_flags |= INDEX_CONSTR_CREATE_INIT_DEFERRED;
+ if (stmt->istemporal)
+ constr_flags |= INDEX_CONSTR_CREATE_TEMPORAL;
indexRelationId =
index_create(rel, indexRelationName, indexRelationId, parentIndexId,
@@ -1811,6 +1860,91 @@ CheckPredicate(Expr *predicate)
errmsg("functions in index predicate must be marked IMMUTABLE")));
}
+/*
+ * get_index_attr_temporal_operator
+ *
+ * Finds an operator for a temporal index attribute.
+ * We need an equality operator for normal keys
+ * and an overlaps operator for the range/PERIOD.
+ * Returns the operator oid and strategy in opid and strat,
+ * respectively.
+ */
+static void
+get_index_attr_temporal_operator(Oid opclass,
+ Oid atttype,
+ bool isoverlaps,
+ Oid *opid,
+ int *strat)
+{
+ Oid opfamily;
+ const char *opname;
+
+ opfamily = get_opclass_family(opclass);
+ /*
+ * If we have a range type, fall back on anyrange.
+ * This seems like a hack
+ * but I can't find any existing lookup function
+ * that knows about pseudotypes.
+ * compatible_oper is close but wants a *name*,
+ * and the point here is to avoid hardcoding a name
+ * (although it should always be = and &&).
+ *
+ * In addition for the normal key elements
+ * try both RTEqualStrategyNumber and BTEqualStrategyNumber.
+ * If you're using btree_gist then you'll need the latter.
+ */
+ if (isoverlaps)
+ {
+ *strat = RTOverlapStrategyNumber;
+ opname = "overlaps";
+ *opid = get_opfamily_member(opfamily, atttype, atttype, *strat);
+ if (!OidIsValid(*opid) && type_is_range(atttype))
+ *opid = get_opfamily_member(opfamily, ANYRANGEOID, ANYRANGEOID, *strat);
+ }
+ else
+ {
+ *strat = RTEqualStrategyNumber;
+ opname = "equality";
+ *opid = get_opfamily_member(opfamily, atttype, atttype, *strat);
+ if (!OidIsValid(*opid) && type_is_range(atttype))
+ *opid = get_opfamily_member(opfamily, ANYRANGEOID, ANYRANGEOID, *strat);
+
+ if (!OidIsValid(*opid))
+ {
+ *strat = BTEqualStrategyNumber;
+ *opid = get_opfamily_member(opfamily, atttype, atttype, *strat);
+ if (!OidIsValid(*opid) && type_is_range(atttype))
+ *opid = get_opfamily_member(opfamily, ANYRANGEOID, ANYRANGEOID, *strat);
+ }
+ }
+
+ if (!OidIsValid(*opid))
+ {
+ HeapTuple opftuple;
+ Form_pg_opfamily opfform;
+
+ /*
+ * attribute->opclass might not explicitly name the opfamily,
+ * so fetch the name of the selected opfamily for use in the
+ * error message.
+ */
+ opftuple = SearchSysCache1(OPFAMILYOID,
+ ObjectIdGetDatum(opfamily));
+ if (!HeapTupleIsValid(opftuple))
+ elog(ERROR, "cache lookup failed for opfamily %u",
+ opfamily);
+ opfform = (Form_pg_opfamily) GETSTRUCT(opftuple);
+
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("no %s operator found for WITHOUT OVERLAPS constraint", opname),
+ errdetail("There must be an %s operator within opfamily \"%s\" for type \"%s\".",
+ opname,
+ NameStr(opfform->opfname),
+ format_type_be(atttype))));
+ }
+}
+
/*
* Compute per-index-column information, including indexed column numbers
* or index expressions, opclasses and their options. Note, all output vectors
@@ -1833,6 +1967,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
Oid accessMethodId,
bool amcanorder,
bool isconstraint,
+ bool istemporal,
Oid ddl_userid,
int ddl_sec_context,
int *ddl_save_nestlevel)
@@ -1856,6 +1991,14 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
else
nextExclOp = NULL;
+ if (istemporal)
+ {
+ Assert(exclusionOpNames == NIL);
+ indexInfo->ii_ExclusionOps = palloc_array(Oid, nkeycols);
+ indexInfo->ii_ExclusionProcs = palloc_array(Oid, nkeycols);
+ indexInfo->ii_ExclusionStrats = palloc_array(uint16, nkeycols);
+ }
+
if (OidIsValid(ddl_userid))
GetUserIdAndSecContext(&save_userid, &save_sec_context);
@@ -2131,6 +2274,19 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
indexInfo->ii_ExclusionStrats[attn] = strat;
nextExclOp = lnext(exclusionOpNames, nextExclOp);
}
+ else if (istemporal)
+ {
+ int strat;
+ Oid opid;
+ get_index_attr_temporal_operator(classOidP[attn],
+ atttype,
+ attn == nkeycols - 1, /* TODO: Don't assume it's last? */
+ &opid,
+ &strat);
+ indexInfo->ii_ExclusionOps[attn] = opid;
+ indexInfo->ii_ExclusionProcs[attn] = get_opcode(opid);
+ indexInfo->ii_ExclusionStrats[attn] = strat;
+ }
/*
* Set up the per-column options (indoption field). For now, this is
@@ -2185,6 +2341,19 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
}
}
+static void
+ComputeIndexPeriod(IndexInfo *indexInfo, Oid relId, const char *periodName)
+{
+ if (periodName == NULL)
+ indexInfo->ii_Period = NULL;
+ else
+ {
+ PeriodDef *p = makeNode(PeriodDef);
+ p->oid = get_period_oid(relId, periodName, true);
+ indexInfo->ii_Period = (Node *) p;
+ }
+}
+
/*
* Resolve possibly-defaulted operator class specification
*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 38cac2afa6..906f0a41a7 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -213,6 +213,7 @@ typedef struct NewConstraint
Oid refrelid; /* PK rel, if FOREIGN */
Oid refindid; /* OID of PK's index, if FOREIGN */
Oid conid; /* OID of pg_constraint entry, if FOREIGN */
+ bool contemporal; /* Whether the new constraint is temporal */
Node *qual; /* Check expr or CONSTR_FOREIGN Constraint */
ExprState *qualstate; /* Execution state for CHECK expr */
} NewConstraint;
@@ -10248,6 +10249,8 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel,
conislocal, /* islocal */
coninhcount, /* inhcount */
connoinherit, /* conNoInherit */
+ false, /* conTemporal */
+ InvalidOid,
false); /* is_internal */
ObjectAddressSet(address, ConstraintRelationId, constrOid);
@@ -10546,6 +10549,8 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel,
false,
1,
false,
+ false, /* conTemporal */
+ InvalidOid,
false);
/*
@@ -11051,6 +11056,8 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
false, /* islocal */
1, /* inhcount */
false, /* conNoInherit */
+ false, /* conTemporal */
+ InvalidOid,
true);
/* Set up partition dependencies for the new constraint */
@@ -11725,6 +11732,7 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
newcon->refrelid = con->confrelid;
newcon->refindid = con->conindid;
newcon->conid = con->oid;
+ newcon->contemporal = con->contemporal;
newcon->qual = (Node *) fkconstraint;
/* Find or create work queue entry for this table */
@@ -11891,10 +11899,12 @@ transformColumnNameList(Oid relId, List *colList,
*
* Look up the names, attnums, and types of the primary key attributes
* for the pkrel. Also return the index OID and index opclasses of the
- * index supporting the primary key.
+ * index supporting the primary key. If this is a temporal primary key,
+ * also set the WITHOUT OVERLAPS attribute name, attnum, and atttypid.
*
* All parameters except pkrel are output parameters. Also, the function
- * return value is the number of attributes in the primary key.
+ * return value is the number of attributes in the primary key,
+ * not including the WITHOUT OVERLAPS if any.
*
* Used when the column list in the REFERENCES specification is omitted.
*/
@@ -14196,7 +14206,9 @@ TryReuseIndex(Oid oldId, IndexStmt *stmt)
if (CheckIndexCompatible(oldId,
stmt->accessMethod,
stmt->indexParams,
- stmt->excludeOpNames))
+ stmt->excludeOpNames,
+ stmt->istemporal,
+ stmt->period ? stmt->period->periodname : NULL))
{
Relation irel = index_open(oldId, NoLock);
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 4b295f8da5..335d31c4f0 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -841,6 +841,8 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
true, /* islocal */
0, /* inhcount */
true, /* noinherit */
+ false, /* contemporal */
+ InvalidOid, /* conperiod */
isInternal); /* is_internal */
}
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 3440dbc440..79511e2a6f 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -3544,6 +3544,8 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
true, /* is local */
0, /* inhcount */
false, /* connoinherit */
+ false, /* contemporal */
+ InvalidOid, /* conperiod */
false); /* is_internal */
if (constrAddr)
ObjectAddressSet(*constrAddr, ConstraintRelationId, ccoid);
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 39e1884cf4..25e8d914f1 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -745,7 +745,7 @@ make_ands_implicit(Expr *clause)
IndexInfo *
makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions,
List *predicates, bool unique, bool nulls_not_distinct,
- bool isready, bool concurrent, bool summarizing)
+ bool isready, bool concurrent, bool summarizing, bool temporal)
{
IndexInfo *n = makeNode(IndexInfo);
@@ -755,6 +755,7 @@ makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions,
Assert(n->ii_NumIndexKeyAttrs <= n->ii_NumIndexAttrs);
n->ii_Unique = unique;
n->ii_NullsNotDistinct = nulls_not_distinct;
+ n->ii_Temporal = temporal;
n->ii_ReadyForInserts = isready;
n->ii_CheckedUnchanged = false;
n->ii_IndexUnchanged = false;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ad1257f51f..70974081a5 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -521,7 +521,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> TableElement TypedTableElement ConstraintElem TableFuncElement
%type <node> columnDef columnOptions
%type <defelt> def_elem reloption_elem old_aggr_elem operator_def_elem
-%type <node> def_arg columnElem where_clause where_or_current_clause
+%type <node> def_arg columnElem withoutOverlapsClause
+ where_clause where_or_current_clause
a_expr b_expr c_expr AexprConst indirection_el opt_slice_bound
columnref in_expr having_clause func_table xmltable array_expr
OptWhereClause operator_def_arg
@@ -3913,6 +3914,7 @@ ColConstraintElem:
n->contype = CONSTR_PRIMARY;
n->location = @1;
n->keys = NULL;
+ n->without_overlaps = NULL;
n->options = $3;
n->indexname = NULL;
n->indexspace = $4;
@@ -4132,7 +4134,7 @@ ConstraintElem:
n->initially_valid = !n->skip_validation;
$$ = (Node *) n;
}
- | UNIQUE opt_unique_null_treatment '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
+ | UNIQUE opt_unique_null_treatment '(' columnList withoutOverlapsClause ')' opt_c_include opt_definition OptConsTableSpace
ConstraintAttributeSpec
{
Constraint *n = makeNode(Constraint);
@@ -4141,11 +4143,12 @@ ConstraintElem:
n->location = @1;
n->nulls_not_distinct = !$2;
n->keys = $4;
- n->including = $6;
- n->options = $7;
+ n->without_overlaps = $5;
+ n->including = $7;
+ n->options = $8;
n->indexname = NULL;
- n->indexspace = $8;
- processCASbits($9, @9, "UNIQUE",
+ n->indexspace = $9;
+ processCASbits($10, @10, "UNIQUE",
&n->deferrable, &n->initdeferred, NULL,
NULL, yyscanner);
$$ = (Node *) n;
@@ -4166,7 +4169,7 @@ ConstraintElem:
NULL, yyscanner);
$$ = (Node *) n;
}
- | PRIMARY KEY '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
+ | PRIMARY KEY '(' columnList withoutOverlapsClause ')' opt_c_include opt_definition OptConsTableSpace
ConstraintAttributeSpec
{
Constraint *n = makeNode(Constraint);
@@ -4174,11 +4177,12 @@ ConstraintElem:
n->contype = CONSTR_PRIMARY;
n->location = @1;
n->keys = $4;
- n->including = $6;
- n->options = $7;
+ n->without_overlaps = $5;
+ n->including = $7;
+ n->options = $8;
n->indexname = NULL;
- n->indexspace = $8;
- processCASbits($9, @9, "PRIMARY KEY",
+ n->indexspace = $9;
+ processCASbits($10, @10, "PRIMARY KEY",
&n->deferrable, &n->initdeferred, NULL,
NULL, yyscanner);
$$ = (Node *) n;
@@ -4190,6 +4194,7 @@ ConstraintElem:
n->contype = CONSTR_PRIMARY;
n->location = @1;
n->keys = NIL;
+ n->without_overlaps = NULL;
n->including = NIL;
n->options = NIL;
n->indexname = $3;
@@ -4256,6 +4261,11 @@ columnList:
| columnList ',' columnElem { $$ = lappend($1, $3); }
;
+withoutOverlapsClause:
+ ',' columnElem WITHOUT OVERLAPS { $$ = $2; }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+
columnElem: ColId
{
$$ = (Node *) makeString($1);
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index b8cf465dcc..21be15fa14 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -37,6 +37,7 @@
#include "catalog/pg_constraint.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_period.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_type.h"
#include "commands/comment.h"
@@ -126,6 +127,8 @@ static List *get_opclass(Oid opclass, Oid actual_datatype);
static void transformIndexConstraints(CreateStmtContext *cxt);
static IndexStmt *transformIndexConstraint(Constraint *constraint,
CreateStmtContext *cxt);
+static bool findNewOrOldColumn(CreateStmtContext *cxt, char *colname, char **typname,
+ Oid *typid);
static void transformExtendedStatistics(CreateStmtContext *cxt);
static void transformFKConstraints(CreateStmtContext *cxt,
bool skipValidation,
@@ -1694,6 +1697,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
index->unique = idxrec->indisunique;
index->nulls_not_distinct = idxrec->indnullsnotdistinct;
index->primary = idxrec->indisprimary;
+ index->istemporal = idxrec->indisprimary && idxrec->indisexclusion;
index->transformed = true; /* don't need transformIndexStmt */
index->concurrent = false;
index->if_not_exists = false;
@@ -1748,7 +1752,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
int nElems;
int i;
- Assert(conrec->contype == CONSTRAINT_EXCLUSION);
+ Assert(conrec->contype == CONSTRAINT_EXCLUSION || conrec->contype == CONSTRAINT_PRIMARY);
/* Extract operator OIDs from the pg_constraint tuple */
datum = SysCacheGetAttrNotNull(CONSTROID, ht_constr,
Anum_pg_constraint_conexclop);
@@ -2290,6 +2294,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
}
index->nulls_not_distinct = constraint->nulls_not_distinct;
index->isconstraint = true;
+ index->istemporal = constraint->without_overlaps != NULL;
index->deferrable = constraint->deferrable;
index->initdeferred = constraint->initdeferred;
@@ -2382,6 +2387,11 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
errmsg("index \"%s\" is not valid", index_name),
parser_errposition(cxt->pstate, constraint->location)));
+ /*
+ * Today we forbid non-unique indexes, but we could permit GiST
+ * indexes whose last entry is a range type and use that to create a
+ * WITHOUT OVERLAPS constraint (i.e. a temporal constraint).
+ */
if (!index_form->indisunique)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -2670,6 +2680,152 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
notnullcmds = lappend(notnullcmds, notnullcmd);
}
}
+
+ /*
+ * Anything in without_overlaps should be included,
+ * but with the overlaps operator (&&) instead of equality.
+ */
+ if (constraint->without_overlaps != NULL) {
+ char *without_overlaps_str = strVal(constraint->without_overlaps);
+ IndexElem *iparam = makeNode(IndexElem);
+ char *typname;
+ Oid typid;
+
+ /*
+ * Iterate through the table's columns
+ * (like just a little bit above).
+ * If we find one whose name is the same as without_overlaps,
+ * validate that it's a range type.
+ *
+ * Otherwise iterate through the table's non-system PERIODs,
+ * and if we find one then use its start/end columns
+ * to construct a range expression.
+ *
+ * Otherwise report an error.
+ */
+
+ if (findNewOrOldColumn(cxt, without_overlaps_str, &typname, &typid))
+ {
+ if (type_is_range(typid))
+ {
+ AlterTableCmd *notnullcmd;
+
+ iparam->name = pstrdup(without_overlaps_str);
+ iparam->expr = NULL;
+
+ /*
+ * Force the column to NOT NULL since it is part of the primary key.
+ */
+ notnullcmd = makeNode(AlterTableCmd);
+
+ notnullcmd->subtype = AT_SetNotNull;
+ notnullcmd->name = pstrdup(without_overlaps_str);
+ notnullcmds = lappend(notnullcmds, notnullcmd);
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("column \"%s\" named in WITHOUT OVERLAPS is not a range type",
+ without_overlaps_str)));
+ }
+ else
+ {
+ /* Look for a PERIOD, first newly-defined */
+ char *startcolname = NULL;
+ char *endcolname = NULL;
+ ListCell *periods = NULL;
+ foreach(periods, cxt->periods)
+ {
+ PeriodDef *period = castNode(PeriodDef, lfirst(periods));
+ if (strcmp(period->periodname, without_overlaps_str) == 0)
+ {
+ startcolname = period->startcolname;
+ endcolname = period->endcolname;
+ /* The period has no oid yet, but transformIndexStmt will look it up */
+ index->period = period;
+ index->period->oid = InvalidOid;
+ index->period->periodname = without_overlaps_str;
+ break;
+ }
+ }
+
+ if (startcolname == NULL && cxt->rel)
+ {
+ /* Look for an already-existing PERIOD */
+ // TODO: locking? releasing?
+ HeapTuple perTuple;
+ Oid relid = RelationGetRelid(cxt->rel);
+ perTuple = SearchSysCache2(PERIODNAME,
+ ObjectIdGetDatum(relid),
+ PointerGetDatum(without_overlaps_str));
+ if (HeapTupleIsValid(perTuple))
+ {
+ Form_pg_period per = (Form_pg_period) GETSTRUCT(perTuple);
+ startcolname = get_attname(relid, per->perstart, false);
+ endcolname = get_attname(relid, per->perend, false);
+ index->period = makeNode(PeriodDef);
+ index->period->oid = per->oid;
+ index->period->periodname = without_overlaps_str;
+
+ ReleaseSysCache(perTuple);
+ }
+ }
+ if (startcolname != NULL)
+ {
+ ColumnRef *start, *end;
+ Oid rngtypid;
+ char *range_type_name;
+
+ if (!findNewOrOldColumn(cxt, startcolname, &typname, &typid))
+ elog(ERROR, "Missing startcol %s for period %s",
+ startcolname, without_overlaps_str);
+ if (!findNewOrOldColumn(cxt, endcolname, &typname, &typid))
+ elog(ERROR, "Missing endcol %s for period %s",
+ endcolname, without_overlaps_str);
+
+ /* Use the start/end columns */
+
+ start = makeNode(ColumnRef);
+ start->fields = list_make1(makeString(startcolname));
+ start->location = constraint->location;
+
+ end = makeNode(ColumnRef);
+ end->fields = list_make1(makeString(endcolname));
+ end->location = constraint->location;
+
+ rngtypid = get_subtype_range(typid);
+ if (rngtypid == InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("PERIOD \"%s\" cannot be used in a constraint without a corresponding range type",
+ without_overlaps_str)));
+
+ range_type_name = get_typname(rngtypid);
+
+ /* Build a range to represent the PERIOD. */
+ iparam->name = NULL;
+ iparam->expr = (Node *) makeFuncCall(SystemFuncName(range_type_name),
+ list_make2(start, end),
+ COERCE_EXPLICIT_CALL,
+ -1);
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("range or PERIOD \"%s\" named in WITHOUT OVERLAPS does not exist",
+ without_overlaps_str)));
+ }
+
+ iparam->indexcolname = NULL;
+ iparam->collation = NIL;
+ iparam->opclass = NIL;
+ iparam->ordering = SORTBY_DEFAULT;
+ iparam->nulls_ordering = SORTBY_NULLS_DEFAULT;
+ index->indexParams = lappend(index->indexParams, iparam);
+
+ index->accessMethod = "gist";
+ constraint->access_method = "gist";
+ }
}
/*
@@ -2789,6 +2945,55 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
return index;
}
+/*
+ * Tries to find a column by name among the existing ones (if it's an ALTER TABLE)
+ * and the new ones. Sets typname and typid if one is found. Returns false if we
+ * couldn't find a match.
+ */
+static bool
+findNewOrOldColumn(CreateStmtContext *cxt, char *colname, char **typname, Oid *typid)
+{
+ /* Check the new columns first in case their type is changing. */
+
+ ColumnDef *column = NULL;
+ ListCell *columns;
+
+ foreach(columns, cxt->columns)
+ {
+ column = lfirst_node(ColumnDef, columns);
+ if (strcmp(column->colname, colname) == 0)
+ {
+ *typid = typenameTypeId(NULL, column->typeName);
+ *typname = TypeNameToString(column->typeName);
+ return true;
+ }
+ }
+
+ // TODO: should I consider DROP COLUMN?
+
+ /* Look up columns on existing table. */
+
+ if (cxt->isalter)
+ {
+ Relation rel = cxt->rel;
+ for (int i = 0; i < rel->rd_att->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(rel->rd_att, i);
+ const char *attname = NameStr(attr->attname);
+ if (strcmp(attname, colname) == 0)
+ {
+ Type type = typeidType(attr->atttypid);
+ *typid = attr->atttypid;
+ *typname = pstrdup(typeTypeName(type));
+ ReleaseSysCache(type);
+ return true;
+ }
+ }
+ }
+
+ return false;
+}
+
/*
* transformExtendedStatistics
* Handle extended statistic objects
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 461735e84f..01ceb6bf81 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -338,8 +338,8 @@ static char *deparse_expression_pretty(Node *expr, List *dpcontext,
static char *pg_get_viewdef_worker(Oid viewoid,
int prettyFlags, int wrapColumn);
static char *pg_get_triggerdef_worker(Oid trigid, bool pretty);
-static int decompile_column_index_array(Datum column_index_array, Oid relId,
- StringInfo buf);
+static int decompile_column_index_array(Datum column_index_array, Oid relId, Oid indexId,
+ bool withoutOverlaps, Oid periodid, StringInfo buf);
static char *pg_get_ruledef_worker(Oid ruleoid, int prettyFlags);
static char *pg_get_indexdef_worker(Oid indexrelid, int colno,
const Oid *excludeOps,
@@ -2229,7 +2229,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
val = SysCacheGetAttrNotNull(CONSTROID, tup,
Anum_pg_constraint_conkey);
- decompile_column_index_array(val, conForm->conrelid, &buf);
+ decompile_column_index_array(val, conForm->conrelid, conForm->conindid, false, InvalidOid, &buf);
/* add foreign relation name */
appendStringInfo(&buf, ") REFERENCES %s(",
@@ -2240,7 +2240,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
val = SysCacheGetAttrNotNull(CONSTROID, tup,
Anum_pg_constraint_confkey);
- decompile_column_index_array(val, conForm->confrelid, &buf);
+ decompile_column_index_array(val, conForm->confrelid, conForm->conindid, false, InvalidOid, &buf);
appendStringInfoChar(&buf, ')');
@@ -2326,7 +2326,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
if (!isnull)
{
appendStringInfoString(&buf, " (");
- decompile_column_index_array(val, conForm->conrelid, &buf);
+ decompile_column_index_array(val, conForm->conrelid, InvalidOid, false, InvalidOid, &buf);
appendStringInfoChar(&buf, ')');
}
@@ -2339,6 +2339,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
Oid indexId;
int keyatts;
HeapTuple indtup;
+ bool isnull;
/* Start off the constraint definition */
if (conForm->contype == CONSTRAINT_PRIMARY)
@@ -2361,7 +2362,14 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
val = SysCacheGetAttrNotNull(CONSTROID, tup,
Anum_pg_constraint_conkey);
- keyatts = decompile_column_index_array(val, conForm->conrelid, &buf);
+ /*
+ * If it has exclusion-style operator OIDs
+ * then it uses WITHOUT OVERLAPS.
+ */
+ indexId = conForm->conindid;
+ SysCacheGetAttr(CONSTROID, tup,
+ Anum_pg_constraint_conexclop, &isnull);
+ keyatts = decompile_column_index_array(val, conForm->conrelid, indexId, !isnull, InvalidOid, &buf);
appendStringInfoChar(&buf, ')');
@@ -2542,8 +2550,8 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
* of keys.
*/
static int
-decompile_column_index_array(Datum column_index_array, Oid relId,
- StringInfo buf)
+decompile_column_index_array(Datum column_index_array, Oid relId, Oid indexId,
+ bool withoutOverlaps, Oid periodid, StringInfo buf)
{
Datum *keys;
int nKeys;
@@ -2556,11 +2564,41 @@ decompile_column_index_array(Datum column_index_array, Oid relId,
for (j = 0; j < nKeys; j++)
{
char *colName;
+ int colid = DatumGetInt16(keys[j]);
- colName = get_attname(relId, DatumGetInt16(keys[j]), false);
+ /* The key might contain a PERIOD instead of an attribute */
+ if (colid == 0)
+ {
+ /* First try the given periodid, then fall back on the index */
+ if (periodid == InvalidOid && indexId != InvalidOid)
+ {
+ HeapTuple indtup;
+ bool isnull;
+ Datum periodidDatum;
+
+ indtup = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexId));
+ if (!HeapTupleIsValid(indtup))
+ elog(ERROR, "cache lookup failed for index %u", indexId);
+
+ periodidDatum = SysCacheGetAttr(INDEXRELID, indtup,
+ Anum_pg_index_indperiod, &isnull);
+ if (isnull)
+ elog(ERROR, "missing period for index %u", indexId);
+
+ periodid = DatumGetObjectId(periodidDatum);
+ ReleaseSysCache(indtup);
+ }
+ colName = get_periodname(periodid, false);
+ }
+ else
+ {
+ colName = get_attname(relId, colid, false);
+ }
if (j == 0)
appendStringInfoString(buf, quote_identifier(colName));
+ else if (withoutOverlaps && j == nKeys - 1)
+ appendStringInfo(buf, ", %s WITHOUT OVERLAPS", quote_identifier(colName));
else
appendStringInfo(buf, ", %s", quote_identifier(colName));
}
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 0e9ddbc2d1..4a3ad4cef2 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -2209,6 +2209,32 @@ get_typisdefined(Oid typid)
return false;
}
+/*
+ * get_typname
+ *
+ * Returns the name of a given type
+ *
+ * Returns a palloc'd copy of the string, or NULL if no such type.
+ */
+char *
+get_typname(Oid typid)
+{
+ HeapTuple tp;
+
+ tp = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid));
+ if (HeapTupleIsValid(tp))
+ {
+ Form_pg_type typtup = (Form_pg_type) GETSTRUCT(tp);
+ char *result;
+
+ result = pstrdup(NameStr(typtup->typname));
+ ReleaseSysCache(tp);
+ return result;
+ }
+ else
+ return NULL;
+}
+
/*
* get_typlen
*
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 40140de958..ea25c16a41 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4792,11 +4792,17 @@ RelationGetIndexList(Relation relation)
* interesting for either oid indexes or replication identity indexes,
* so don't check them.
*/
- if (!index->indisvalid || !index->indisunique ||
- !index->indimmediate ||
+ if (!index->indisvalid || !index->indimmediate ||
!heap_attisnull(htup, Anum_pg_index_indpred, NULL))
continue;
+ /*
+ * Non-unique indexes aren't interesting either,
+ * except when they are temporal primary keys.
+ */
+ if (!index->indisunique && !index->indisprimary)
+ continue;
+
/* remember primary key index if any */
if (index->indisprimary)
pkeyIndex = index->indexrelid;
@@ -5507,8 +5513,9 @@ RelationGetIdentityKeyBitmap(Relation relation)
* RelationGetExclusionInfo -- get info about index's exclusion constraint
*
* This should be called only for an index that is known to have an
- * associated exclusion constraint. It returns arrays (palloc'd in caller's
- * context) of the exclusion operator OIDs, their underlying functions'
+ * associated exclusion constraint or temporal primary key/unique
+ * constraint. It returns arrays (palloc'd in caller's context)
+ * of the exclusion operator OIDs, their underlying functions'
* OIDs, and their strategy numbers in the index's opclasses. We cache
* all this information since it requires a fair amount of work to get.
*/
@@ -5574,7 +5581,10 @@ RelationGetExclusionInfo(Relation indexRelation,
int nelem;
/* We want the exclusion constraint owning the index */
- if (conform->contype != CONSTRAINT_EXCLUSION ||
+ if ((conform->contype != CONSTRAINT_EXCLUSION &&
+ !(conform->contemporal && (
+ conform->contype == CONSTRAINT_PRIMARY
+ || conform->contype == CONSTRAINT_UNIQUE))) ||
conform->conindid != RelationGetRelid(indexRelation))
continue;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 50841f755b..c2fe1094de 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -6990,7 +6990,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_tablespace,
i_indreloptions,
i_indstatcols,
- i_indstatvals;
+ i_indstatvals,
+ i_withoutoverlaps,
+ i_indperiod;
/*
* We want to perform just one query against pg_index. However, we
@@ -7054,21 +7056,30 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"(SELECT pg_catalog.array_agg(attstattarget ORDER BY attnum) "
" FROM pg_catalog.pg_attribute "
" WHERE attrelid = i.indexrelid AND "
- " attstattarget >= 0) AS indstatvals, ");
+ " attstattarget >= 0) AS indstatvals, "
+ "c.conexclop IS NOT NULL AS withoutoverlaps, ");
else
appendPQExpBufferStr(query,
"0 AS parentidx, "
"i.indnatts AS indnkeyatts, "
"i.indnatts AS indnatts, "
"'' AS indstatcols, "
- "'' AS indstatvals, ");
+ "'' AS indstatvals, "
+ "null AS withoutoverlaps, ");
if (fout->remoteVersion >= 150000)
appendPQExpBufferStr(query,
- "i.indnullsnotdistinct ");
+ "i.indnullsnotdistinct, ");
else
appendPQExpBufferStr(query,
- "false AS indnullsnotdistinct ");
+ "false AS indnullsnotdistinct, ");
+
+ if (fout->remoteVersion >= 160000)
+ appendPQExpBufferStr(query,
+ "p.pername AS indperiod ");
+ else
+ appendPQExpBufferStr(query,
+ "null AS indperiod ");
/*
* The point of the messy-looking outer join is to find a constraint that
@@ -7079,7 +7090,27 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
* Note: the check on conrelid is redundant, but useful because that
* column is indexed while conindid is not.
*/
- if (fout->remoteVersion >= 110000)
+ if (fout->remoteVersion >= 160000)
+ {
+ appendPQExpBuffer(query,
+ "FROM unnest('%s'::pg_catalog.oid[]) AS src(tbloid)\n"
+ "JOIN pg_catalog.pg_index i ON (src.tbloid = i.indrelid) "
+ "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
+ "JOIN pg_catalog.pg_class t2 ON (t2.oid = i.indrelid) "
+ "LEFT JOIN pg_catalog.pg_constraint c "
+ "ON (i.indrelid = c.conrelid AND "
+ "i.indexrelid = c.conindid AND "
+ "c.contype IN ('p','u','x')) "
+ "LEFT JOIN pg_catalog.pg_period p "
+ "ON (p.oid = i.indperiod) "
+ "LEFT JOIN pg_catalog.pg_inherits inh "
+ "ON (inh.inhrelid = indexrelid) "
+ "WHERE (i.indisvalid OR t2.relkind = 'p') "
+ "AND i.indisready "
+ "ORDER BY i.indrelid, indexname",
+ tbloids->data);
+ }
+ else if (fout->remoteVersion >= 110000)
{
appendPQExpBuffer(query,
"FROM unnest('%s'::pg_catalog.oid[]) AS src(tbloid)\n"
@@ -7143,6 +7174,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_indreloptions = PQfnumber(res, "indreloptions");
i_indstatcols = PQfnumber(res, "indstatcols");
i_indstatvals = PQfnumber(res, "indstatvals");
+ i_withoutoverlaps = PQfnumber(res, "withoutoverlaps");
+ i_indperiod = PQfnumber(res, "indperiod");
indxinfo = (IndxInfo *) pg_malloc(ntups * sizeof(IndxInfo));
@@ -7245,6 +7278,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
constrinfo->condeferred = *(PQgetvalue(res, j, i_condeferred)) == 't';
constrinfo->conislocal = true;
constrinfo->separate = true;
+ constrinfo->withoutoverlaps = *(PQgetvalue(res, j, i_withoutoverlaps)) == 't';
+ constrinfo->indperiod = pg_strdup(PQgetvalue(res, j, i_indperiod));
indxinfo[j].indexconstraint = constrinfo->dobj.dumpId;
}
@@ -16801,12 +16836,35 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
const char *attname;
if (indkey == InvalidAttrNumber)
- break;
+ {
+ if (coninfo->withoutoverlaps)
+ {
+ /* We have a PERIOD, so there is no attname. */
+ appendPQExpBuffer(q, ", %s WITHOUT OVERLAPS",
+ fmtId(coninfo->indperiod));
+ continue;
+ }
+ else
+ break;
+ }
attname = getAttrName(indkey, tbinfo);
- appendPQExpBuffer(q, "%s%s",
- (k == 0) ? "" : ", ",
- fmtId(attname));
+ if (k == 0)
+ {
+ appendPQExpBuffer(q, "%s",
+ fmtId(attname));
+ }
+ else if (k == indxinfo->indnkeyattrs - 1 &&
+ coninfo->withoutoverlaps)
+ {
+ appendPQExpBuffer(q, ", %s WITHOUT OVERLAPS",
+ fmtId(attname));
+ }
+ else
+ {
+ appendPQExpBuffer(q, ", %s",
+ fmtId(attname));
+ }
}
if (indxinfo->indnkeyattrs < indxinfo->indnattrs)
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index dda1bfac38..2ca74c7833 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -487,6 +487,8 @@ typedef struct _constraintInfo
bool condeferred; /* true if constraint is INITIALLY DEFERRED */
bool conislocal; /* true if constraint has local definition */
bool separate; /* true if must dump as separate item */
+ bool withoutoverlaps; /* true if the last elem is WITHOUT OVERLAPS */
+ char *indperiod; /* the PERIOD used in WITHOUT OVERLAPS */
} ConstraintInfo;
typedef struct _periodInfo
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 93e24d5145..36a5bb2752 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -1020,6 +1020,102 @@ my %tests = (
},
},
+ 'ALTER TABLE ONLY test_table_tpk ADD CONSTRAINT ... PRIMARY KEY (..., ... WITHOUT OVERLAPS)' => {
+ create_sql => 'CREATE TABLE dump_test.test_table_tpk (
+ col1 int4range,
+ col2 tstzrange,
+ CONSTRAINT test_table_tpk_pkey PRIMARY KEY
+ (col1, col2 WITHOUT OVERLAPS));',
+ regexp => qr/^
+ \QALTER TABLE ONLY dump_test.test_table_tpk\E \n^\s+
+ \QADD CONSTRAINT test_table_tpk_pkey PRIMARY KEY (col1, col2 WITHOUT OVERLAPS);\E
+ /xm,
+ like => {
+ %full_runs,
+ %dump_test_schema_runs,
+ section_post_data => 1,
+ exclude_test_table => 1,
+ },
+ unlike => {
+ only_dump_test_table => 1,
+ exclude_dump_test_schema => 1,
+ only_dump_measurement => 1,
+ },
+ },
+
+ 'ALTER TABLE ONLY test_table_tperpk ADD CONSTRAINT ... PRIMARY KEY (..., ... WITHOUT OVERLAPS)' => {
+ create_sql => 'CREATE TABLE dump_test.test_table_tperpk (
+ col1 int4range,
+ ds date,
+ de date,
+ PERIOD FOR p (ds, de),
+ CONSTRAINT test_table_tperpk_pkey PRIMARY KEY
+ (col1, p WITHOUT OVERLAPS));',
+ regexp => qr/^
+ \QALTER TABLE ONLY dump_test.test_table_tperpk\E \n^\s+
+ \QADD CONSTRAINT test_table_tperpk_pkey PRIMARY KEY (col1, p WITHOUT OVERLAPS);\E
+ /xm,
+ like => {
+ %full_runs,
+ %dump_test_schema_runs,
+ section_post_data => 1,
+ exclude_test_table => 1,
+ },
+ unlike => {
+ only_dump_test_table => 1,
+ exclude_dump_test_schema => 1,
+ only_dump_measurement => 1,
+ },
+ },
+
+ 'ALTER TABLE ONLY test_table_tuq ADD CONSTRAINT ... UNIQUE (..., ... WITHOUT OVERLAPS)' => {
+ create_sql => 'CREATE TABLE dump_test.test_table_tuq (
+ col1 int4range,
+ col2 tstzrange,
+ CONSTRAINT test_table_tuq_uq UNIQUE
+ (col1, col2 WITHOUT OVERLAPS));',
+ regexp => qr/^
+ \QALTER TABLE ONLY dump_test.test_table_tuq\E \n^\s+
+ \QADD CONSTRAINT test_table_tuq_uq UNIQUE (col1, col2 WITHOUT OVERLAPS);\E
+ /xm,
+ like => {
+ %full_runs,
+ %dump_test_schema_runs,
+ section_post_data => 1,
+ exclude_test_table => 1,
+ },
+ unlike => {
+ only_dump_test_table => 1,
+ exclude_dump_test_schema => 1,
+ only_dump_measurement => 1,
+ },
+ },
+
+ 'ALTER TABLE ONLY test_table_tperuq ADD CONSTRAINT ... UNIQUE (..., ... WITHOUT OVERLAPS)' => {
+ create_sql => 'CREATE TABLE dump_test.test_table_tperuq (
+ col1 int4range,
+ ds date,
+ de date,
+ PERIOD FOR p (ds, de),
+ CONSTRAINT test_table_tperuq_uq UNIQUE
+ (col1, p WITHOUT OVERLAPS));',
+ regexp => qr/^
+ \QALTER TABLE ONLY dump_test.test_table_tperuq\E \n^\s+
+ \QADD CONSTRAINT test_table_tperuq_uq UNIQUE (col1, p WITHOUT OVERLAPS);\E
+ /xm,
+ like => {
+ %full_runs,
+ %dump_test_schema_runs,
+ section_post_data => 1,
+ exclude_test_table => 1,
+ },
+ unlike => {
+ only_dump_test_table => 1,
+ exclude_dump_test_schema => 1,
+ only_dump_measurement => 1,
+ },
+ },
+
'ALTER TABLE (partitioned) ADD CONSTRAINT ... FOREIGN KEY' => {
create_order => 4,
create_sql => 'CREATE TABLE dump_test.test_table_fk (
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index cb1db5c84f..c76ec837c3 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2418,6 +2418,10 @@ describeOneTableDetails(const char *schemaname,
else
appendPQExpBufferStr(&buf, ", false AS indisreplident");
appendPQExpBufferStr(&buf, ", c2.reltablespace");
+ if (pset.sversion >= 160000)
+ appendPQExpBufferStr(&buf, ", con.contemporal");
+ else
+ appendPQExpBufferStr(&buf, ", false AS contemporal");
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
" LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n"
@@ -2439,8 +2443,12 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf, " \"%s\"",
PQgetvalue(result, i, 0));
- /* If exclusion constraint, print the constraintdef */
- if (strcmp(PQgetvalue(result, i, 7), "x") == 0)
+ /*
+ * If exclusion constraint or temporal PK/UNIQUE constraint,
+ * print the constraintdef
+ */
+ if (strcmp(PQgetvalue(result, i, 7), "x") == 0 ||
+ strcmp(PQgetvalue(result, i, 12), "t") == 0)
{
appendPQExpBuffer(&buf, " %s",
PQgetvalue(result, i, 6));
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index c8532fb97c..7afbc8cfc4 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -91,6 +91,7 @@ extern Oid index_create(Relation heapRelation,
#define INDEX_CONSTR_CREATE_INIT_DEFERRED (1 << 2)
#define INDEX_CONSTR_CREATE_UPDATE_INDEX (1 << 3)
#define INDEX_CONSTR_CREATE_REMOVE_OLD_DEPS (1 << 4)
+#define INDEX_CONSTR_CREATE_TEMPORAL (1 << 5)
extern Oid index_concurrently_create_copy(Relation heapRelation,
Oid oldIndexId,
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 16bf5f5576..dce8da50ca 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -107,6 +107,14 @@ CATALOG(pg_constraint,2606,ConstraintRelationId)
/* Has a local definition and cannot be inherited */
bool connoinherit;
+ /*
+ * For primary and foreign keys, signifies the last column is a range
+ * and should use overlaps instead of equals.
+ */
+ bool contemporal;
+
+ Oid conperiod; /* local PERIOD used in PK/FK constraint */
+
#ifdef CATALOG_VARLEN /* variable-length fields start here */
/*
@@ -146,7 +154,7 @@ CATALOG(pg_constraint,2606,ConstraintRelationId)
/*
* If an exclusion constraint, the OIDs of the exclusion operators for
- * each column of the constraint
+ * each column of the constraint. Also set for temporal primary keys.
*/
Oid conexclop[1] BKI_LOOKUP(pg_operator);
@@ -235,6 +243,8 @@ extern Oid CreateConstraintEntry(const char *constraintName,
bool conIsLocal,
int conInhCount,
bool conNoInherit,
+ bool conTemporal,
+ Oid period,
bool is_internal);
extern void RemoveConstraintById(Oid conId);
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index 478203ed4c..39c14b01fb 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -44,7 +44,9 @@ extern char *ChooseRelationName(const char *name1, const char *name2,
extern bool CheckIndexCompatible(Oid oldId,
const char *accessMethodName,
List *attributeList,
- List *exclusionOpNames);
+ List *exclusionOpNames,
+ bool istemporal,
+ const char *indexPeriodName);
extern Oid GetDefaultOpClass(Oid type_id, Oid am_id);
extern Oid ResolveOpClass(List *opclass, Oid attrType,
const char *accessMethodName, Oid accessMethodId);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 695ff056ba..96143c09b1 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -144,6 +144,7 @@ typedef struct ExprState
* IndexAttrNumbers underlying-rel attribute numbers used as keys
* (zeroes indicate expressions). It also contains
* info about included columns.
+ * Period period used in the index, or NULL if none
* Expressions expr trees for expression entries, or NIL if none
* ExpressionsState exec state for expressions, or NIL if none
* Predicate partial-index predicate, or NIL if none
@@ -155,6 +156,7 @@ typedef struct ExprState
* UniqueProcs
* UniqueStrats
* Unique is it a unique index?
+ * Temporal is it for a temporal constraint?
* OpclassOptions opclass-specific options, or NULL if none
* ReadyForInserts is it valid for inserts?
* CheckedUnchanged IndexUnchanged status determined yet?
@@ -177,6 +179,7 @@ typedef struct IndexInfo
int ii_NumIndexAttrs; /* total number of columns in index */
int ii_NumIndexKeyAttrs; /* number of key columns in index */
AttrNumber ii_IndexAttrNumbers[INDEX_MAX_KEYS];
+ Node *ii_Period; /* period used in the index */
List *ii_Expressions; /* list of Expr */
List *ii_ExpressionsState; /* list of ExprState */
List *ii_Predicate; /* list of Expr */
@@ -190,6 +193,7 @@ typedef struct IndexInfo
Datum *ii_OpclassOptions; /* array with one entry per column */
bool ii_Unique;
bool ii_NullsNotDistinct;
+ bool ii_Temporal;
bool ii_ReadyForInserts;
bool ii_CheckedUnchanged;
bool ii_IndexUnchanged;
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 06d991b725..4ed3af1864 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -98,7 +98,7 @@ extern IndexInfo *makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid,
List *expressions, List *predicates,
bool unique, bool nulls_not_distinct,
bool isready, bool concurrent,
- bool summarizing);
+ bool summarizing, bool temporal);
extern DefElem *makeDefElem(char *name, Node *arg, int location);
extern DefElem *makeDefElemExtended(char *nameSpace, char *name, Node *arg,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 3b523af18e..d6b28d07ec 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2624,6 +2624,9 @@ typedef struct Constraint
Oid old_pktable_oid; /* pg_constraint.confrelid of my former
* self */
+ /* Fields used for temporal PRIMARY KEY and FOREIGN KEY constraints: */
+ Node *without_overlaps; /* String node naming PERIOD or range column */
+
/* Fields used for constraints that allow a NOT VALID specification */
bool skip_validation; /* skip validation of existing rows? */
bool initially_valid; /* mark the new constraint as valid? */
@@ -3226,6 +3229,7 @@ typedef struct IndexStmt
bool nulls_not_distinct; /* null treatment for UNIQUE constraints */
bool primary; /* is index a primary key? */
bool isconstraint; /* is it for a pkey/unique constraint? */
+ bool istemporal; /* is it for a temporal pkey? */
bool deferrable; /* is the constraint DEFERRABLE? */
bool initdeferred; /* is the constraint INITIALLY DEFERRED? */
bool transformed; /* true when transformIndexStmt is finished */
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 88c5c314db..cb37424ac0 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -144,6 +144,7 @@ extern char get_rel_persistence(Oid relid);
extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes);
extern Oid get_transform_tosql(Oid typid, Oid langid, List *trftypes);
extern bool get_typisdefined(Oid typid);
+extern char *get_typname(Oid typid);
extern int16 get_typlen(Oid typid);
extern bool get_typbyval(Oid typid);
extern void get_typlenbyval(Oid typid, int16 *typlen, bool *typbyval);
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
new file mode 100644
index 0000000000..83bd29c98e
--- /dev/null
+++ b/src/test/regress/expected/without_overlaps.out
@@ -0,0 +1,585 @@
+-- Tests for WITHOUT OVERLAPS.
+--
+-- We leave behind several tables to test pg_dump etc:
+-- temporal_rng, temporal_per, temporal_rng2, temporal_per2,
+-- temporal_fk_{rng,per}2{rng,per}.
+--
+-- test input parser
+--
+-- PK with no columns just WITHOUT OVERLAPS:
+CREATE TABLE temporal_rng (
+ valid_at tsrange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (valid_at WITHOUT OVERLAPS)
+);
+ERROR: syntax error at or near "WITHOUT"
+LINE 3: CONSTRAINT temporal_rng_pk PRIMARY KEY (valid_at WITHOUT OV...
+ ^
+-- PK with a range column/PERIOD that isn't there:
+CREATE TABLE temporal_rng (
+ id INTEGER,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+ERROR: range or PERIOD "valid_at" named in WITHOUT OVERLAPS does not exist
+-- PK with a non-range column:
+CREATE TABLE temporal_rng (
+ id INTEGER,
+ valid_at TEXT,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+ERROR: column "valid_at" named in WITHOUT OVERLAPS is not a range type
+-- PK with one column plus a range:
+CREATE TABLE temporal_rng (
+ -- Since we can't depend on having btree_gist here,
+ -- use an int4range instead of an int.
+ -- (The rangetypes regression test uses the same trick.)
+ id int4range,
+ valid_at tsrange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_rng
+ Table "public.temporal_rng"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | not null |
+ valid_at | tsrange | | not null |
+Indexes:
+ "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
+ pg_get_constraintdef
+---------------------------------------------
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+(1 row)
+
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
+ pg_get_indexdef
+-------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at)
+(1 row)
+
+-- PK with two columns plus a range:
+CREATE TABLE temporal_rng2 (
+ id1 int4range,
+ id2 int4range,
+ valid_at tsrange,
+ CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_rng2
+ Table "public.temporal_rng2"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id1 | int4range | | not null |
+ id2 | int4range | | not null |
+ valid_at | tsrange | | not null |
+Indexes:
+ "temporal_rng2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_pk';
+ pg_get_constraintdef
+---------------------------------------------------
+ PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+(1 row)
+
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_pk';
+ pg_get_indexdef
+---------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX temporal_rng2_pk ON temporal_rng2 USING gist (id1, id2, valid_at)
+(1 row)
+
+DROP TABLE temporal_rng2;
+-- PK with one column plus a PERIOD:
+CREATE TABLE temporal_per (
+ id int4range,
+ valid_from timestamp,
+ valid_til timestamp,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ CONSTRAINT temporal_per_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_per
+ Table "public.temporal_per"
+ Column | Type | Collation | Nullable | Default
+------------+-----------------------------+-----------+----------+---------
+ id | int4range | | not null |
+ valid_from | timestamp without time zone | | |
+ valid_til | timestamp without time zone | | |
+Periods:
+ valid_at (valid_from, valid_til)
+Indexes:
+ "temporal_per_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "temporal_per_valid_at_check" CHECK (valid_from < valid_til)
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_per_pk';
+ pg_get_constraintdef
+---------------------------------------------
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+(1 row)
+
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_per_pk';
+ pg_get_indexdef
+-----------------------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX temporal_per_pk ON temporal_per USING gist (id, tsrange(valid_from, valid_til))
+(1 row)
+
+-- PK with two columns plus a PERIOD:
+CREATE TABLE temporal_per2 (
+ id1 int4range,
+ id2 int4range,
+ valid_from timestamp,
+ valid_til timestamp,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ CONSTRAINT temporal_per2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_per2
+ Table "public.temporal_per2"
+ Column | Type | Collation | Nullable | Default
+------------+-----------------------------+-----------+----------+---------
+ id1 | int4range | | not null |
+ id2 | int4range | | not null |
+ valid_from | timestamp without time zone | | |
+ valid_til | timestamp without time zone | | |
+Periods:
+ valid_at (valid_from, valid_til)
+Indexes:
+ "temporal_per2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "temporal_per2_valid_at_check" CHECK (valid_from < valid_til)
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_per2_pk';
+ pg_get_constraintdef
+---------------------------------------------------
+ PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+(1 row)
+
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_per2_pk';
+ pg_get_indexdef
+-------------------------------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX temporal_per2_pk ON temporal_per2 USING gist (id1, id2, tsrange(valid_from, valid_til))
+(1 row)
+
+DROP TABLE temporal_per2;
+-- PK with a custom range type:
+CREATE TYPE textrange2 AS range (subtype=text, collation="C");
+CREATE TABLE temporal_rng2 (
+ id int4range,
+ valid_at textrange2,
+ CONSTRAINT temporal_rng2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+ALTER TABLE temporal_rng2 DROP CONSTRAINT temporal_rng2_pk;
+DROP TABLE temporal_rng2;
+DROP TYPE textrange2;
+-- UNIQUE with no columns just WITHOUT OVERLAPS:
+CREATE TABLE temporal_rng2 (
+ valid_at tsrange,
+ CONSTRAINT temporal_rng2_uq UNIQUE (valid_at WITHOUT OVERLAPS)
+);
+ERROR: syntax error at or near "WITHOUT"
+LINE 3: CONSTRAINT temporal_rng2_uq UNIQUE (valid_at WITHOUT OVERLA...
+ ^
+-- UNIQUE with a range column/PERIOD that isn't there:
+CREATE TABLE temporal_rng2 (
+ id INTEGER,
+ CONSTRAINT temporal_rng2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+ERROR: range or PERIOD "valid_at" named in WITHOUT OVERLAPS does not exist
+-- UNIQUE with a non-range column:
+CREATE TABLE temporal_rng2 (
+ id INTEGER,
+ valid_at TEXT,
+ CONSTRAINT temporal_rng2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+ERROR: column "valid_at" named in WITHOUT OVERLAPS is not a range type
+-- UNIQUE with one column plus a range:
+CREATE TABLE temporal_rng2 (
+ -- Since we can't depend on having btree_gist here,
+ -- use an int4range instead of an int.
+ -- (The rangetypes regression test uses the same trick.)
+ id int4range,
+ valid_at tsrange,
+ CONSTRAINT temporal_rng2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_rng2
+ Table "public.temporal_rng2"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | |
+ valid_at | tsrange | | not null |
+Indexes:
+ "temporal_rng2_uq" UNIQUE (id, valid_at WITHOUT OVERLAPS)
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_uq';
+ pg_get_constraintdef
+----------------------------------------
+ UNIQUE (id, valid_at WITHOUT OVERLAPS)
+(1 row)
+
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_uq';
+ pg_get_indexdef
+---------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX temporal_rng2_uq ON temporal_rng2 USING gist (id, valid_at)
+(1 row)
+
+-- UNIQUE with two columns plus a range:
+CREATE TABLE temporal_rng3 (
+ id1 int4range,
+ id2 int4range,
+ valid_at tsrange,
+ CONSTRAINT temporal_rng3_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_rng3
+ Table "public.temporal_rng3"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id1 | int4range | | |
+ id2 | int4range | | |
+ valid_at | tsrange | | not null |
+Indexes:
+ "temporal_rng3_uq" UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng3_uq';
+ pg_get_constraintdef
+----------------------------------------------
+ UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
+(1 row)
+
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng3_uq';
+ pg_get_indexdef
+---------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX temporal_rng3_uq ON temporal_rng3 USING gist (id1, id2, valid_at)
+(1 row)
+
+DROP TABLE temporal_rng3;
+-- UNIQUE with one column plus a PERIOD:
+CREATE TABLE temporal_per2 (
+ id int4range,
+ valid_from timestamp,
+ valid_til timestamp,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ CONSTRAINT temporal_per2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_per2
+ Table "public.temporal_per2"
+ Column | Type | Collation | Nullable | Default
+------------+-----------------------------+-----------+----------+---------
+ id | int4range | | |
+ valid_from | timestamp without time zone | | |
+ valid_til | timestamp without time zone | | |
+Periods:
+ valid_at (valid_from, valid_til)
+Indexes:
+ "temporal_per2_uq" UNIQUE (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "temporal_per2_valid_at_check" CHECK (valid_from < valid_til)
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_per2_uq';
+ pg_get_constraintdef
+----------------------------------------
+ UNIQUE (id, valid_at WITHOUT OVERLAPS)
+(1 row)
+
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_per2_uq';
+ pg_get_indexdef
+-------------------------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX temporal_per2_uq ON temporal_per2 USING gist (id, tsrange(valid_from, valid_til))
+(1 row)
+
+-- UNIQUE with two columns plus a PERIOD:
+CREATE TABLE temporal_per3 (
+ id1 int4range,
+ id2 int4range,
+ valid_from timestamp,
+ valid_til timestamp,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ CONSTRAINT temporal_per3_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_per3
+ Table "public.temporal_per3"
+ Column | Type | Collation | Nullable | Default
+------------+-----------------------------+-----------+----------+---------
+ id1 | int4range | | |
+ id2 | int4range | | |
+ valid_from | timestamp without time zone | | |
+ valid_til | timestamp without time zone | | |
+Periods:
+ valid_at (valid_from, valid_til)
+Indexes:
+ "temporal_per3_uq" UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "temporal_per3_valid_at_check" CHECK (valid_from < valid_til)
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_per3_uq';
+ pg_get_constraintdef
+----------------------------------------------
+ UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
+(1 row)
+
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_per3_uq';
+ pg_get_indexdef
+-------------------------------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX temporal_per3_uq ON temporal_per3 USING gist (id1, id2, tsrange(valid_from, valid_til))
+(1 row)
+
+DROP TABLE temporal_per3;
+-- UNIQUE with a custom range type:
+CREATE TYPE textrange2 AS range (subtype=text, collation="C");
+CREATE TABLE temporal_per3 (
+ id int4range,
+ valid_at textrange2,
+ CONSTRAINT temporal_per3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+ALTER TABLE temporal_per3 DROP CONSTRAINT temporal_per3_uq;
+DROP TABLE temporal_per3;
+DROP TYPE textrange2;
+--
+-- test ALTER TABLE ADD CONSTRAINT
+--
+DROP TABLE temporal_rng;
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at tsrange
+);
+ALTER TABLE temporal_rng
+ ADD CONSTRAINT temporal_rng_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+-- PK with USING INDEX (not possible):
+CREATE TABLE temporal3 (
+ id int4range,
+ valid_at tsrange
+);
+CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at);
+ALTER TABLE temporal3
+ ADD CONSTRAINT temporal3_pk
+ PRIMARY KEY USING INDEX idx_temporal3_uq;
+ERROR: "idx_temporal3_uq" is not a unique index
+LINE 2: ADD CONSTRAINT temporal3_pk
+ ^
+DETAIL: Cannot create a primary key or unique constraint using such an index.
+DROP TABLE temporal3;
+-- UNIQUE with USING INDEX (not possible):
+CREATE TABLE temporal3 (
+ id int4range,
+ valid_at tsrange
+);
+CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at);
+ALTER TABLE temporal3
+ ADD CONSTRAINT temporal3_uq
+ UNIQUE USING INDEX idx_temporal3_uq;
+ERROR: "idx_temporal3_uq" is not a unique index
+LINE 2: ADD CONSTRAINT temporal3_uq
+ ^
+DETAIL: Cannot create a primary key or unique constraint using such an index.
+DROP TABLE temporal3;
+-- Add range column and the PK at the same time
+CREATE TABLE temporal3 (
+ id int4range
+);
+ALTER TABLE temporal3
+ ADD COLUMN valid_at tsrange,
+ ADD CONSTRAINT temporal3_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal3;
+-- Add PERIOD and the PK at the same time
+CREATE TABLE temporal3 (
+ id int4range,
+ valid_from date,
+ valid_til date
+);
+ALTER TABLE temporal3
+ ADD PERIOD FOR valid_at (valid_from, valid_til),
+ ADD CONSTRAINT temporal3_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal3;
+-- Add range column and UNIQUE constraint at the same time
+CREATE TABLE temporal3 (
+ id int4range
+);
+ALTER TABLE temporal3
+ ADD COLUMN valid_at tsrange,
+ ADD CONSTRAINT temporal3_uq
+ UNIQUE (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal3;
+-- Add PERIOD column and UNIQUE constraint at the same time
+CREATE TABLE temporal3 (
+ id int4range,
+ valid_from date,
+ valid_til date
+);
+ALTER TABLE temporal3
+ ADD PERIOD FOR valid_at (valid_from, valid_til),
+ ADD CONSTRAINT temporal3_uq
+ UNIQUE (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal3;
+-- Add date columns, PERIOD, and the PK at the same time
+CREATE TABLE temporal3 (
+ id int4range
+);
+ALTER TABLE temporal3
+ ADD COLUMN valid_from date,
+ ADD COLUMN valid_til date,
+ ADD PERIOD FOR valid_at (valid_from, valid_til),
+ ADD CONSTRAINT temporal3_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal3;
+-- Add date columns, PERIOD, and UNIQUE constraint at the same time
+CREATE TABLE temporal3 (
+ id int4range
+);
+ALTER TABLE temporal3
+ ADD COLUMN valid_from date,
+ ADD COLUMN valid_til date,
+ ADD PERIOD FOR valid_at (valid_from, valid_til),
+ ADD CONSTRAINT temporal3_uq
+ UNIQUE (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal3;
+--
+-- test PK inserts
+--
+-- okay:
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-03'));
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-03-03', '2018-04-04'));
+INSERT INTO temporal_rng VALUES ('[2,2]', tsrange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng VALUES ('[3,3]', tsrange('2018-01-01', NULL));
+-- should fail:
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-01', '2018-01-05'));
+ERROR: conflicting key value violates exclusion constraint "temporal_rng_pk"
+DETAIL: Key (id, valid_at)=([1,2), ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:00:00 2018")) conflicts with existing key (id, valid_at)=([1,2), ["Tue Jan 02 00:00:00 2018","Sat Feb 03 00:00:00 2018")).
+INSERT INTO temporal_rng VALUES (NULL, tsrange('2018-01-01', '2018-01-05'));
+ERROR: null value in column "id" of relation "temporal_rng" violates not-null constraint
+DETAIL: Failing row contains (null, ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:00:00 2018")).
+INSERT INTO temporal_rng VALUES ('[3,3]', NULL);
+ERROR: null value in column "valid_at" of relation "temporal_rng" violates not-null constraint
+DETAIL: Failing row contains ([3,4), null).
+--
+-- test a range with both a PK and a UNIQUE constraint
+--
+CREATE TABLE temporal3 (
+ id int4range,
+ valid_at tsrange,
+ id2 int8range,
+ name TEXT,
+ CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+ CONSTRAINT temporal3_uniq UNIQUE (id2, valid_at WITHOUT OVERLAPS)
+);
+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')
+;
+UPDATE without_overlaps_test2 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'
+ 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
+(8 rows)
+
+-- conflicting id only:
+INSERT INTO without_overlaps_test2 (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")).
+-- conflicting id2 only:
+INSERT INTO without_overlaps_test2 (id, valid_at, id2, name)
+ VALUES
+ ('[3,3]', tsrange('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;
+--
+-- test a PERIOD with both a PK and a UNIQUE constraint
+--
+CREATE TABLE without_overlaps_test2 (
+ id int4range,
+ valid_from timestamp,
+ valid_til timestamp,
+ 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)
+);
+INSERT INTO without_overlaps_test2 (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'
+ SET name = name || '1';
+UPDATE without_overlaps_test2 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
+(8 rows)
+
+-- conflicting id only:
+INSERT INTO without_overlaps_test2 (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")).
+-- conflicting id2 only:
+INSERT INTO without_overlaps_test2 (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;
+--
+-- test changing the PK's dependencies
+--
+CREATE TABLE without_overlaps_test2 (
+ id int4range,
+ valid_at tsrange,
+ CONSTRAINT without_overlaps2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+ALTER TABLE without_overlaps_test2 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;
+--
+-- test PARTITION BY for ranges
+--
+CREATE TABLE temporal_partitioned (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+) PARTITION BY LIST (id);
+ERROR: cannot match partition key to an index using access method "gist"
+-- TODO: attach some partitions, insert into them, update them with and without FOR PORTION OF, delete them the same way.
+--
+-- test PARTITION BY for PERIODS
+--
+CREATE TABLE temporal_partitioned (
+ id int4range,
+ valid_from TIMESTAMP,
+ valid_til TIMESTAMP,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ CONSTRAINT temporal_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+) PARTITION BY LIST (id);
+ERROR: cannot match partition key to an index using access method "gist"
+-- TODO: attach some partitions, insert into them, update them with and without FOR PORTION OF, delete them the same way.
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 973825f5c7..f36e15a0fe 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -28,7 +28,7 @@ test: strings md5 numerology point lseg line box path polygon circle date time t
# geometry depends on point, lseg, line, box, path, polygon, circle
# horology depends on date, time, timetz, timestamp, timestamptz, interval
# ----------
-test: geometry horology tstypes regex type_sanity opr_sanity misc_sanity comments expressions unicode xid mvcc
+test: geometry horology tstypes regex type_sanity opr_sanity misc_sanity comments expressions unicode xid mvcc without_overlaps
# ----------
# Load huge amounts of data
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
new file mode 100644
index 0000000000..bdfdc50967
--- /dev/null
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -0,0 +1,408 @@
+-- Tests for WITHOUT OVERLAPS.
+--
+-- We leave behind several tables to test pg_dump etc:
+-- temporal_rng, temporal_per, temporal_rng2, temporal_per2,
+-- temporal_fk_{rng,per}2{rng,per}.
+
+--
+-- test input parser
+--
+
+-- PK with no columns just WITHOUT OVERLAPS:
+
+CREATE TABLE temporal_rng (
+ valid_at tsrange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (valid_at WITHOUT OVERLAPS)
+);
+
+-- PK with a range column/PERIOD that isn't there:
+
+CREATE TABLE temporal_rng (
+ id INTEGER,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+
+-- PK with a non-range column:
+
+CREATE TABLE temporal_rng (
+ id INTEGER,
+ valid_at TEXT,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+
+-- PK with one column plus a range:
+
+CREATE TABLE temporal_rng (
+ -- Since we can't depend on having btree_gist here,
+ -- use an int4range instead of an int.
+ -- (The rangetypes regression test uses the same trick.)
+ id int4range,
+ valid_at tsrange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_rng
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
+
+-- PK with two columns plus a range:
+CREATE TABLE temporal_rng2 (
+ id1 int4range,
+ id2 int4range,
+ valid_at tsrange,
+ CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_rng2
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_pk';
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_pk';
+DROP TABLE temporal_rng2;
+
+
+-- PK with one column plus a PERIOD:
+CREATE TABLE temporal_per (
+ id int4range,
+ valid_from timestamp,
+ valid_til timestamp,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ CONSTRAINT temporal_per_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_per
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_per_pk';
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_per_pk';
+
+-- PK with two columns plus a PERIOD:
+CREATE TABLE temporal_per2 (
+ id1 int4range,
+ id2 int4range,
+ valid_from timestamp,
+ valid_til timestamp,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ CONSTRAINT temporal_per2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_per2
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_per2_pk';
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_per2_pk';
+DROP TABLE temporal_per2;
+
+-- PK with a custom range type:
+CREATE TYPE textrange2 AS range (subtype=text, collation="C");
+CREATE TABLE temporal_rng2 (
+ id int4range,
+ valid_at textrange2,
+ CONSTRAINT temporal_rng2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+ALTER TABLE temporal_rng2 DROP CONSTRAINT temporal_rng2_pk;
+DROP TABLE temporal_rng2;
+DROP TYPE textrange2;
+
+-- UNIQUE with no columns just WITHOUT OVERLAPS:
+
+CREATE TABLE temporal_rng2 (
+ valid_at tsrange,
+ CONSTRAINT temporal_rng2_uq UNIQUE (valid_at WITHOUT OVERLAPS)
+);
+
+-- UNIQUE with a range column/PERIOD that isn't there:
+
+CREATE TABLE temporal_rng2 (
+ id INTEGER,
+ CONSTRAINT temporal_rng2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+
+-- UNIQUE with a non-range column:
+
+CREATE TABLE temporal_rng2 (
+ id INTEGER,
+ valid_at TEXT,
+ CONSTRAINT temporal_rng2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+
+-- UNIQUE with one column plus a range:
+
+CREATE TABLE temporal_rng2 (
+ -- Since we can't depend on having btree_gist here,
+ -- use an int4range instead of an int.
+ -- (The rangetypes regression test uses the same trick.)
+ id int4range,
+ valid_at tsrange,
+ CONSTRAINT temporal_rng2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_rng2
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_uq';
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_uq';
+
+-- UNIQUE with two columns plus a range:
+CREATE TABLE temporal_rng3 (
+ id1 int4range,
+ id2 int4range,
+ valid_at tsrange,
+ CONSTRAINT temporal_rng3_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_rng3
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng3_uq';
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng3_uq';
+DROP TABLE temporal_rng3;
+
+-- UNIQUE with one column plus a PERIOD:
+CREATE TABLE temporal_per2 (
+ id int4range,
+ valid_from timestamp,
+ valid_til timestamp,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ CONSTRAINT temporal_per2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_per2
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_per2_uq';
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_per2_uq';
+
+-- UNIQUE with two columns plus a PERIOD:
+CREATE TABLE temporal_per3 (
+ id1 int4range,
+ id2 int4range,
+ valid_from timestamp,
+ valid_til timestamp,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ CONSTRAINT temporal_per3_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_per3
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_per3_uq';
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_per3_uq';
+DROP TABLE temporal_per3;
+
+-- UNIQUE with a custom range type:
+CREATE TYPE textrange2 AS range (subtype=text, collation="C");
+CREATE TABLE temporal_per3 (
+ id int4range,
+ valid_at textrange2,
+ CONSTRAINT temporal_per3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+ALTER TABLE temporal_per3 DROP CONSTRAINT temporal_per3_uq;
+DROP TABLE temporal_per3;
+DROP TYPE textrange2;
+
+--
+-- test ALTER TABLE ADD CONSTRAINT
+--
+
+DROP TABLE temporal_rng;
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at tsrange
+);
+ALTER TABLE temporal_rng
+ ADD CONSTRAINT temporal_rng_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+
+-- PK with USING INDEX (not possible):
+CREATE TABLE temporal3 (
+ id int4range,
+ valid_at tsrange
+);
+CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at);
+ALTER TABLE temporal3
+ ADD CONSTRAINT temporal3_pk
+ PRIMARY KEY USING INDEX idx_temporal3_uq;
+DROP TABLE temporal3;
+
+-- UNIQUE with USING INDEX (not possible):
+CREATE TABLE temporal3 (
+ id int4range,
+ valid_at tsrange
+);
+CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at);
+ALTER TABLE temporal3
+ ADD CONSTRAINT temporal3_uq
+ UNIQUE USING INDEX idx_temporal3_uq;
+DROP TABLE temporal3;
+
+-- Add range column and the PK at the same time
+CREATE TABLE temporal3 (
+ id int4range
+);
+ALTER TABLE temporal3
+ ADD COLUMN valid_at tsrange,
+ ADD CONSTRAINT temporal3_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal3;
+
+-- Add PERIOD and the PK at the same time
+CREATE TABLE temporal3 (
+ id int4range,
+ valid_from date,
+ valid_til date
+);
+ALTER TABLE temporal3
+ ADD PERIOD FOR valid_at (valid_from, valid_til),
+ ADD CONSTRAINT temporal3_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal3;
+
+-- Add range column and UNIQUE constraint at the same time
+CREATE TABLE temporal3 (
+ id int4range
+);
+ALTER TABLE temporal3
+ ADD COLUMN valid_at tsrange,
+ ADD CONSTRAINT temporal3_uq
+ UNIQUE (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal3;
+
+-- Add PERIOD column and UNIQUE constraint at the same time
+CREATE TABLE temporal3 (
+ id int4range,
+ valid_from date,
+ valid_til date
+);
+ALTER TABLE temporal3
+ ADD PERIOD FOR valid_at (valid_from, valid_til),
+ ADD CONSTRAINT temporal3_uq
+ UNIQUE (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal3;
+
+-- Add date columns, PERIOD, and the PK at the same time
+CREATE TABLE temporal3 (
+ id int4range
+);
+ALTER TABLE temporal3
+ ADD COLUMN valid_from date,
+ ADD COLUMN valid_til date,
+ ADD PERIOD FOR valid_at (valid_from, valid_til),
+ ADD CONSTRAINT temporal3_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal3;
+
+-- Add date columns, PERIOD, and UNIQUE constraint at the same time
+CREATE TABLE temporal3 (
+ id int4range
+);
+ALTER TABLE temporal3
+ ADD COLUMN valid_from date,
+ ADD COLUMN valid_til date,
+ ADD PERIOD FOR valid_at (valid_from, valid_til),
+ ADD CONSTRAINT temporal3_uq
+ UNIQUE (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal3;
+
+--
+-- test PK inserts
+--
+
+-- okay:
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-03'));
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-03-03', '2018-04-04'));
+INSERT INTO temporal_rng VALUES ('[2,2]', tsrange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng VALUES ('[3,3]', tsrange('2018-01-01', NULL));
+
+-- should fail:
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng VALUES (NULL, tsrange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng VALUES ('[3,3]', NULL);
+
+--
+-- test a range with both a PK and a UNIQUE constraint
+--
+
+CREATE TABLE temporal3 (
+ id int4range,
+ valid_at tsrange,
+ id2 int8range,
+ name TEXT,
+ CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+ CONSTRAINT temporal3_uniq UNIQUE (id2, valid_at WITHOUT OVERLAPS)
+);
+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')
+;
+UPDATE without_overlaps_test2 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'
+ SET name = name || '2'
+ WHERE id = '[2,2]';
+SELECT * FROM without_overlaps_test2 ORDER BY id, valid_at;
+-- conflicting id only:
+INSERT INTO without_overlaps_test2 (id, valid_at, id2, name)
+ VALUES
+ ('[1,1]', tsrange('2005-01-01', '2006-01-01'), '[8,8]', 'foo3');
+-- conflicting id2 only:
+INSERT INTO without_overlaps_test2 (id, valid_at, id2, name)
+ VALUES
+ ('[3,3]', tsrange('2005-01-01', '2010-01-01'), '[9,9]', 'bar3')
+;
+DROP TABLE without_overlaps_test2;
+
+--
+-- test a PERIOD with both a PK and a UNIQUE constraint
+--
+
+CREATE TABLE without_overlaps_test2 (
+ id int4range,
+ valid_from timestamp,
+ valid_til timestamp,
+ 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)
+);
+INSERT INTO without_overlaps_test2 (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'
+ SET name = name || '1';
+UPDATE without_overlaps_test2 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;
+-- conflicting id only:
+INSERT INTO without_overlaps_test2 (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)
+ VALUES
+ ('[3,3]', '2005-01-01', '2010-01-01', '[9,9]', 'bar3')
+;
+DROP TABLE without_overlaps_test2;
+
+--
+-- test changing the PK's dependencies
+--
+
+CREATE TABLE without_overlaps_test2 (
+ id int4range,
+ valid_at tsrange,
+ CONSTRAINT without_overlaps2_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;
+
+--
+-- test PARTITION BY for ranges
+--
+
+CREATE TABLE temporal_partitioned (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+) PARTITION BY LIST (id);
+-- TODO: attach some partitions, insert into them, update them with and without FOR PORTION OF, delete them the same way.
+
+--
+-- test PARTITION BY for PERIODS
+--
+
+CREATE TABLE temporal_partitioned (
+ id int4range,
+ valid_from TIMESTAMP,
+ valid_til TIMESTAMP,
+ PERIOD FOR valid_at (valid_from, valid_til),
+ CONSTRAINT temporal_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+) PARTITION BY LIST (id);
+-- TODO: attach some partitions, insert into them, update them with and without FOR PORTION OF, delete them the same way.
--
2.25.1