Skip to content

Commit 1a9a3c4

Browse files
peterepull[bot]
petere
authored and
pull[bot]
committed
Add temporal PRIMARY KEY and UNIQUE constraints
Add WITHOUT OVERLAPS clause to PRIMARY KEY and UNIQUE constraints. These 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. Author: Paul A. Jungwirth <pj@illuminatedcomputing.com> Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Reviewed-by: jian he <jian.universality@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com
1 parent 2a4b5d7 commit 1a9a3c4

File tree

34 files changed

+1135
-52
lines changed

34 files changed

+1135
-52
lines changed

‎contrib/btree_gist/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -40,7 +40,7 @@ PGFILEDESC = "btree_gist - B-tree equivalent GiST operator classes"
4040
REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz \
4141
time timetz date interval macaddr macaddr8 inet cidr text varchar char \
4242
bytea bit varbit numeric uuid not_equal enum bool partitions \
43-
stratnum
43+
stratnum without_overlaps
4444

4545
SHLIB_LINK += $(filter -lm, $(LIBS))
4646

‎contrib/btree_gist/expected/without_overlaps.out

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
-- Core must test WITHOUT OVERLAPS
2+
-- with an int4range + daterange,
3+
-- so here we do some simple tests
4+
-- to make sure int + daterange works too,
5+
-- since that is the expected use-case.
6+
CREATE TABLE temporal_rng (
7+
id integer,
8+
valid_at daterange,
9+
CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
10+
);
11+
\d temporal_rng
12+
Table "public.temporal_rng"
13+
Column | Type | Collation | Nullable | Default
14+
----------+-----------+-----------+----------+---------
15+
id | integer | | not null |
16+
valid_at | daterange | | not null |
17+
Indexes:
18+
"temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
19+
20+
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
21+
pg_get_constraintdef
22+
---------------------------------------------
23+
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
24+
(1 row)
25+
26+
SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
27+
pg_get_indexdef
28+
-------------------------------------------------------------------------------
29+
CREATE UNIQUE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at)
30+
(1 row)
31+
32+
INSERT INTO temporal_rng VALUES
33+
(1, '[2000-01-01,2001-01-01)');
34+
-- same key, doesn't overlap:
35+
INSERT INTO temporal_rng VALUES
36+
(1, '[2001-01-01,2002-01-01)');
37+
-- overlaps but different key:
38+
INSERT INTO temporal_rng VALUES
39+
(2, '[2000-01-01,2001-01-01)');
40+
-- should fail:
41+
INSERT INTO temporal_rng VALUES
42+
(1, '[2000-06-01,2001-01-01)');
43+
ERROR: conflicting key value violates exclusion constraint "temporal_rng_pk"
44+
DETAIL: Key (id, valid_at)=(1, [06-01-2000,01-01-2001)) conflicts with existing key (id, valid_at)=(1, [01-01-2000,01-01-2001)).

‎contrib/btree_gist/meson.build

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,7 @@ tests += {
9191
'bool',
9292
'partitions',
9393
'stratnum',
94+
'without_overlaps',
9495
],
9596
},
9697
}

‎contrib/btree_gist/sql/without_overlaps.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
-- Core must test WITHOUT OVERLAPS
2+
-- with an int4range + daterange,
3+
-- so here we do some simple tests
4+
-- to make sure int + daterange works too,
5+
-- since that is the expected use-case.
6+
CREATE TABLE temporal_rng (
7+
id integer,
8+
valid_at daterange,
9+
CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
10+
);
11+
\d temporal_rng
12+
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
13+
SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
14+
15+
INSERT INTO temporal_rng VALUES
16+
(1, '[2000-01-01,2001-01-01)');
17+
-- same key, doesn't overlap:
18+
INSERT INTO temporal_rng VALUES
19+
(1, '[2001-01-01,2002-01-01)');
20+
-- overlaps but different key:
21+
INSERT INTO temporal_rng VALUES
22+
(2, '[2000-01-01,2001-01-01)');
23+
-- should fail:
24+
INSERT INTO temporal_rng VALUES
25+
(1, '[2000-06-01,2001-01-01)');

‎doc/src/sgml/catalogs.sgml

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2709,6 +2709,16 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
27092709
</para></entry>
27102710
</row>
27112711

2712+
<row>
2713+
<entry role="catalog_table_entry"><para role="column_definition">
2714+
<structfield>conwithoutoverlaps</structfield> <type>bool</type>
2715+
</para>
2716+
<para>
2717+
This constraint is defined with <literal>WITHOUT OVERLAPS</literal>
2718+
(for primary keys and unique constraints).
2719+
</para></entry>
2720+
</row>
2721+
27122722
<row>
27132723
<entry role="catalog_table_entry"><para role="column_definition">
27142724
<structfield>conkey</structfield> <type>int2[]</type>

‎doc/src/sgml/gist.sgml

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -298,7 +298,8 @@ CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops);
298298
The optional twelfth method <function>stratnum</function> is used to
299299
translate well-known <literal>RT*StrategyNumber</literal>s (from
300300
<filename>src/include/access/stratnum.h</filename>) into strategy numbers
301-
used by the operator class.
301+
used by the operator class. This lets the core code look up operators for
302+
temporal constraint indexes.
302303
</para>
303304

304305
<variablelist>
@@ -1185,6 +1186,17 @@ my_sortsupport(PG_FUNCTION_ARGS)
11851186
operator class has no matching strategy.
11861187
</para>
11871188

1189+
<para>
1190+
This is used for temporal index constraints (i.e., <literal>PRIMARY
1191+
KEY</literal> and <literal>UNIQUE</literal>). If the operator class
1192+
provides this function and it returns results for
1193+
<literal>RTEqualStrategyNumber</literal>, it can be used in the
1194+
non-<literal>WITHOUT OVERLAPS</literal> part(s) of an index constraint.
1195+
If it returns results for <literal>RTOverlapStrategyNumber</literal>,
1196+
the operator class can be used in the <literal>WITHOUT
1197+
OVERLAPS</literal> part of an index constraint.
1198+
</para>
1199+
11881200
<para>
11891201
The <acronym>SQL</acronym> declaration of the function must look like
11901202
this:

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 36 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -78,8 +78,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
7878
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
7979
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
8080
NOT NULL <replaceable class="parameter">column_name</replaceable> [ NO INHERIT ] |
81-
UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
82-
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
81+
UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> |
82+
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> |
8383
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> ) ] |
8484
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
8585
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable
@@ -965,7 +965,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
965965

966966
<varlistentry id="sql-createtable-parms-unique">
967967
<term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ]</literal> (column constraint)</term>
968-
<term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
968+
<term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] )</literal>
969969
<optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term>
970970

971971
<listitem>
@@ -979,6 +979,30 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
979979
of these columns.
980980
</para>
981981

982+
<para>
983+
If the <literal>WITHOUT OVERLAPS</literal> option is specified for the
984+
last column, then that column is checked for overlaps instead of
985+
equality. In that case, the other columns of the constraint will allow
986+
duplicates so long as the duplicates don't overlap in the
987+
<literal>WITHOUT OVERLAPS</literal> column. (This is sometimes called a
988+
temporal key, if the column is a range of dates or timestamps, but
989+
PostgreSQL allows ranges over any base type.) In effect, such a
990+
constraint is enforced with an <literal>EXCLUDE</literal> constraint
991+
rather than a <literal>UNIQUE</literal> constraint. So for example
992+
<literal>UNIQUE (id, valid_at WITHOUT OVERLAPS)</literal> behaves like
993+
<literal>EXCLUDE USING GIST (id WITH =, valid_at WITH
994+
&amp;&amp;)</literal>. The <literal>WITHOUT OVERLAPS</literal> column
995+
must have a range or multirange type. (Technically, any type is allowed
996+
whose default GiST opclass includes an overlaps operator. See the
997+
<literal>stratnum</literal> support function under <xref
998+
linkend="gist-extensibility"/> for details.) The non-<literal>WITHOUT
999+
OVERLAPS</literal> columns of the constraint can be any type that can be
1000+
compared for equality in a GiST index. By default, only range types are
1001+
supported, but you can use other types by adding the <xref
1002+
linkend="btree-gist"/> extension (which is the expected way to use this
1003+
feature).
1004+
</para>
1005+
9821006
<para>
9831007
For the purpose of a unique constraint, null values are not
9841008
considered equal, unless <literal>NULLS NOT DISTINCT</literal> is
@@ -1000,8 +1024,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
10001024
</para>
10011025

10021026
<para>
1003-
Adding a unique constraint will automatically create a unique btree
1004-
index on the column or group of columns used in the constraint.
1027+
Adding a unique constraint will automatically create a unique B-tree
1028+
index on the column or group of columns used in the constraint. But if
1029+
the constraint includes a <literal>WITHOUT OVERLAPS</literal> clause, it
1030+
will use a GiST index.
10051031
</para>
10061032

10071033
<para>
@@ -1019,7 +1045,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
10191045

10201046
<varlistentry id="sql-createtable-parms-primary-key">
10211047
<term><literal>PRIMARY KEY</literal> (column constraint)</term>
1022-
<term><literal>PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
1048+
<term><literal>PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] )</literal>
10231049
<optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term>
10241050
<listitem>
10251051
<para>
@@ -1052,9 +1078,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
10521078
</para>
10531079

10541080
<para>
1055-
Adding a <literal>PRIMARY KEY</literal> constraint will automatically
1056-
create a unique btree index on the column or group of columns used in the
1057-
constraint.
1081+
As with a <literal>UNIQUE</literal> constraint, adding a
1082+
<literal>PRIMARY KEY</literal> constraint will automatically create a
1083+
unique B-tree index, or GiST if <literal>WITHOUT OVERLAPS</literal> was
1084+
specified, on the column or group of columns used in the constraint.
10581085
</para>
10591086

10601087
<para>

‎src/backend/access/gist/gistutil.c

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1070,3 +1070,32 @@ gist_stratnum_identity(PG_FUNCTION_ARGS)
10701070

10711071
PG_RETURN_UINT16(strat);
10721072
}
1073+
1074+
/*
1075+
* Returns the opclass's private stratnum used for the given strategy.
1076+
*
1077+
* Calls the opclass's GIST_STRATNUM_PROC support function, if any,
1078+
* and returns the result.
1079+
* Returns InvalidStrategy if the function is not defined.
1080+
*/
1081+
StrategyNumber
1082+
GistTranslateStratnum(Oid opclass, StrategyNumber strat)
1083+
{
1084+
Oid opfamily;
1085+
Oid opcintype;
1086+
Oid funcid;
1087+
Datum result;
1088+
1089+
/* Look up the opclass family and input datatype. */
1090+
if (!get_opclass_opfamily_and_input_type(opclass, &opfamily, &opcintype))
1091+
return InvalidStrategy;
1092+
1093+
/* Check whether the function is provided. */
1094+
funcid = get_opfamily_proc(opfamily, opcintype, opcintype, GIST_STRATNUM_PROC);
1095+
if (!OidIsValid(funcid))
1096+
return InvalidStrategy;
1097+
1098+
/* Ask the translation function */
1099+
result = OidFunctionCall1Coll(funcid, InvalidOid, UInt16GetDatum(strat));
1100+
return DatumGetUInt16(result);
1101+
}

‎src/backend/catalog/heap.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2141,6 +2141,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
21412141
is_local, /* conislocal */
21422142
inhcount, /* coninhcount */
21432143
is_no_inherit, /* connoinherit */
2144+
false, /* conwithoutoverlaps */
21442145
is_internal); /* internally constructed? */
21452146

21462147
pfree(ccbin);
@@ -2191,6 +2192,7 @@ StoreRelNotNull(Relation rel, const char *nnname, AttrNumber attnum,
21912192
is_local,
21922193
inhcount,
21932194
is_no_inherit,
2195+
false, /* conwithoutoverlaps */
21942196
false);
21952197
return constrOid;
21962198
}

‎src/backend/catalog/index.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1904,6 +1904,7 @@ index_concurrently_set_dead(Oid heapId, Oid indexId)
19041904
* INDEX_CONSTR_CREATE_UPDATE_INDEX: update the pg_index row
19051905
* INDEX_CONSTR_CREATE_REMOVE_OLD_DEPS: remove existing dependencies
19061906
* of index on table's columns
1907+
* INDEX_CONSTR_CREATE_WITHOUT_OVERLAPS: constraint uses WITHOUT OVERLAPS
19071908
* allow_system_table_mods: allow table to be a system catalog
19081909
* is_internal: index is constructed due to internal process
19091910
*/
@@ -1927,11 +1928,13 @@ index_constraint_create(Relation heapRelation,
19271928
bool mark_as_primary;
19281929
bool islocal;
19291930
bool noinherit;
1931+
bool is_without_overlaps;
19301932
int inhcount;
19311933

19321934
deferrable = (constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) != 0;
19331935
initdeferred = (constr_flags & INDEX_CONSTR_CREATE_INIT_DEFERRED) != 0;
19341936
mark_as_primary = (constr_flags & INDEX_CONSTR_CREATE_MARK_AS_PRIMARY) != 0;
1937+
is_without_overlaps = (constr_flags & INDEX_CONSTR_CREATE_WITHOUT_OVERLAPS) != 0;
19351938

19361939
/* constraint creation support doesn't work while bootstrapping */
19371940
Assert(!IsBootstrapProcessingMode());
@@ -2008,6 +2011,7 @@ index_constraint_create(Relation heapRelation,
20082011
islocal,
20092012
inhcount,
20102013
noinherit,
2014+
is_without_overlaps,
20112015
is_internal);
20122016

20132017
/*

‎src/backend/catalog/pg_constraint.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,7 @@ CreateConstraintEntry(const char *constraintName,
7878
bool conIsLocal,
7979
int conInhCount,
8080
bool conNoInherit,
81+
bool conWithoutOverlaps,
8182
bool is_internal)
8283
{
8384
Relation conDesc;
@@ -193,6 +194,7 @@ CreateConstraintEntry(const char *constraintName,
193194
values[Anum_pg_constraint_conislocal - 1] = BoolGetDatum(conIsLocal);
194195
values[Anum_pg_constraint_coninhcount - 1] = Int16GetDatum(conInhCount);
195196
values[Anum_pg_constraint_connoinherit - 1] = BoolGetDatum(conNoInherit);
197+
values[Anum_pg_constraint_conwithoutoverlaps - 1] = BoolGetDatum(conWithoutOverlaps);
196198

197199
if (conkeyArray)
198200
values[Anum_pg_constraint_conkey - 1] = PointerGetDatum(conkeyArray);

0 commit comments

Comments
 (0)