Skip to content

Commit fc0438b

Browse files
peterepetere
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. (previously committed as 46a0cd4, reverted by 46a0cd4; the new part is this:) Because 'empty' && 'empty' is false, the temporal PK/UQ constraint allowed duplicates, which is confusing to users and breaks internal expectations. For instance, when GROUP BY checks functional dependencies on the PK, it allows selecting other columns from the table, but in the presence of duplicate keys you could get the value from any of their rows. So we need to forbid empties. This all means that at the moment we can only support ranges and multiranges for temporal PK/UQs, unlike the original patch (above). Documentation and tests for this are added. But this could conceivably be extended by introducing some more general support for the notion of "empty" for other types. 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 7406ab6 commit fc0438b

37 files changed

+2848
-62
lines changed

‎contrib/btree_gist/Makefile

+1-1
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

+44
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

+1
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

+25
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

+10
Original file line numberDiff line numberDiff line change
@@ -2730,6 +2730,16 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
27302730
</para></entry>
27312731
</row>
27322732

2733+
<row>
2734+
<entry role="catalog_table_entry"><para role="column_definition">
2735+
<structfield>conperiod</structfield> <type>bool</type>
2736+
</para>
2737+
<para>
2738+
This constraint is defined with <literal>WITHOUT OVERLAPS</literal>
2739+
(for primary keys and unique constraints).
2740+
</para></entry>
2741+
</row>
2742+
27332743
<row>
27342744
<entry role="catalog_table_entry"><para role="column_definition">
27352745
<structfield>conkey</structfield> <type>int2[]</type>

‎doc/src/sgml/gist.sgml

+10-1
Original file line numberDiff line numberDiff line change
@@ -292,7 +292,8 @@ CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops);
292292
The optional twelfth method <function>stratnum</function> is used to
293293
translate well-known <literal>RT*StrategyNumber</literal>s (from
294294
<filename>src/include/access/stratnum.h</filename>) into strategy numbers
295-
used by the operator class.
295+
used by the operator class. This lets the core code look up operators for
296+
temporal constraint indexes.
296297
</para>
297298

298299
<variablelist>
@@ -1179,6 +1180,14 @@ my_sortsupport(PG_FUNCTION_ARGS)
11791180
operator class has no matching strategy.
11801181
</para>
11811182

1183+
<para>
1184+
This is used for temporal index constraints (i.e., <literal>PRIMARY
1185+
KEY</literal> and <literal>UNIQUE</literal>). If the operator class
1186+
provides this function and it returns results for
1187+
<literal>RTEqualStrategyNumber</literal>, it can be used in the
1188+
non-<literal>WITHOUT OVERLAPS</literal> part(s) of an index constraint.
1189+
</para>
1190+
11821191
<para>
11831192
The <acronym>SQL</acronym> declaration of the function must look like
11841193
this:

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

+31-8
Original file line numberDiff line numberDiff line change
@@ -77,8 +77,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
7777

7878
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
7979
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
80-
UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
81-
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
80+
UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> |
81+
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> |
8282
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> ) ] |
8383
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
8484
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable
@@ -964,7 +964,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
964964

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

970970
<listitem>
@@ -978,6 +978,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
978978
of these columns.
979979
</para>
980980

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

10011022
<para>
10021023
Adding a unique constraint will automatically create a unique btree
1003-
index on the column or group of columns used in the constraint. The
1004-
created index has the same name as the unique constraint.
1024+
index on the column or group of columns used in the constraint. But if
1025+
the constraint includes a <literal>WITHOUT OVERLAPS</literal> clause, it
1026+
will use a GiST index. The created index has the same name as the
1027+
unique constraint.
10051028
</para>
10061029

10071030
<para>
@@ -1019,7 +1042,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
10191042

10201043
<varlistentry id="sql-createtable-parms-primary-key">
10211044
<term><literal>PRIMARY KEY</literal> (column constraint)</term>
1022-
<term><literal>PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
1045+
<term><literal>PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] )</literal>
10231046
<optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term>
10241047
<listitem>
10251048
<para>
@@ -1054,8 +1077,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
10541077
<para>
10551078
Adding a <literal>PRIMARY KEY</literal> constraint will automatically
10561079
create a unique btree index on the column or group of columns used in
1057-
the constraint. That index has the same name as the primary key
1058-
constraint.
1080+
the constraint, or GiST if <literal>WITHOUT OVERLAPS</literal> was
1081+
specified.
10591082
</para>
10601083

10611084
<para>

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

+29
Original file line numberDiff line numberDiff line change
@@ -1069,3 +1069,32 @@ gist_stratnum_identity(PG_FUNCTION_ARGS)
10691069

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

‎src/backend/catalog/heap.c

+1
Original file line numberDiff line numberDiff line change
@@ -2163,6 +2163,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
21632163
is_local, /* conislocal */
21642164
inhcount, /* coninhcount */
21652165
is_no_inherit, /* connoinherit */
2166+
false, /* conperiod */
21662167
is_internal); /* internally constructed? */
21672168

21682169
pfree(ccbin);

‎src/backend/catalog/index.c

+10-4
Original file line numberDiff line numberDiff line change
@@ -1394,7 +1394,8 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
13941394
oldInfo->ii_NullsNotDistinct,
13951395
false, /* not ready for inserts */
13961396
true,
1397-
indexRelation->rd_indam->amsummarizing);
1397+
indexRelation->rd_indam->amsummarizing,
1398+
oldInfo->ii_WithoutOverlaps);
13981399

13991400
/*
14001401
* Extract the list of column names and the column numbers for the new
@@ -1874,6 +1875,7 @@ index_concurrently_set_dead(Oid heapId, Oid indexId)
18741875
* INDEX_CONSTR_CREATE_UPDATE_INDEX: update the pg_index row
18751876
* INDEX_CONSTR_CREATE_REMOVE_OLD_DEPS: remove existing dependencies
18761877
* of index on table's columns
1878+
* INDEX_CONSTR_CREATE_WITHOUT_OVERLAPS: constraint uses WITHOUT OVERLAPS
18771879
* allow_system_table_mods: allow table to be a system catalog
18781880
* is_internal: index is constructed due to internal process
18791881
*/
@@ -1897,11 +1899,13 @@ index_constraint_create(Relation heapRelation,
18971899
bool mark_as_primary;
18981900
bool islocal;
18991901
bool noinherit;
1902+
bool is_without_overlaps;
19001903
int inhcount;
19011904

19021905
deferrable = (constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) != 0;
19031906
initdeferred = (constr_flags & INDEX_CONSTR_CREATE_INIT_DEFERRED) != 0;
19041907
mark_as_primary = (constr_flags & INDEX_CONSTR_CREATE_MARK_AS_PRIMARY) != 0;
1908+
is_without_overlaps = (constr_flags & INDEX_CONSTR_CREATE_WITHOUT_OVERLAPS) != 0;
19051909

19061910
/* constraint creation support doesn't work while bootstrapping */
19071911
Assert(!IsBootstrapProcessingMode());
@@ -1978,6 +1982,7 @@ index_constraint_create(Relation heapRelation,
19781982
islocal,
19791983
inhcount,
19801984
noinherit,
1985+
is_without_overlaps,
19811986
is_internal);
19821987

19831988
/*
@@ -2427,7 +2432,8 @@ BuildIndexInfo(Relation index)
24272432
indexStruct->indnullsnotdistinct,
24282433
indexStruct->indisready,
24292434
false,
2430-
index->rd_indam->amsummarizing);
2435+
index->rd_indam->amsummarizing,
2436+
indexStruct->indisexclusion && indexStruct->indisunique);
24312437

24322438
/* fill in attribute numbers */
24332439
for (i = 0; i < numAtts; i++)
@@ -2486,7 +2492,8 @@ BuildDummyIndexInfo(Relation index)
24862492
indexStruct->indnullsnotdistinct,
24872493
indexStruct->indisready,
24882494
false,
2489-
index->rd_indam->amsummarizing);
2495+
index->rd_indam->amsummarizing,
2496+
indexStruct->indisexclusion && indexStruct->indisunique);
24902497

24912498
/* fill in attribute numbers */
24922499
for (i = 0; i < numAtts; i++)
@@ -3224,7 +3231,6 @@ IndexCheckExclusion(Relation heapRelation,
32243231
indexInfo->ii_PredicateState = NULL;
32253232
}
32263233

3227-
32283234
/*
32293235
* validate_index - support code for concurrent index builds
32303236
*

‎src/backend/catalog/pg_constraint.c

+2
Original file line numberDiff line numberDiff line change
@@ -75,6 +75,7 @@ CreateConstraintEntry(const char *constraintName,
7575
bool conIsLocal,
7676
int conInhCount,
7777
bool conNoInherit,
78+
bool conPeriod,
7879
bool is_internal)
7980
{
8081
Relation conDesc;
@@ -190,6 +191,7 @@ CreateConstraintEntry(const char *constraintName,
190191
values[Anum_pg_constraint_conislocal - 1] = BoolGetDatum(conIsLocal);
191192
values[Anum_pg_constraint_coninhcount - 1] = Int16GetDatum(conInhCount);
192193
values[Anum_pg_constraint_connoinherit - 1] = BoolGetDatum(conNoInherit);
194+
values[Anum_pg_constraint_conperiod - 1] = BoolGetDatum(conPeriod);
193195

194196
if (conkeyArray)
195197
values[Anum_pg_constraint_conkey - 1] = PointerGetDatum(conkeyArray);

0 commit comments

Comments
 (0)