From fc0438b4e80535419a4e54dba87642cdf84defda Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Tue, 17 Sep 2024 10:36:09 +0200 Subject: 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 46a0cd4cefb, reverted by 46a0cd4cefb; 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 Reviewed-by: Peter Eisentraut Reviewed-by: jian he Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com --- doc/src/sgml/catalogs.sgml | 10 ++++++++++ doc/src/sgml/gist.sgml | 11 ++++++++++- doc/src/sgml/ref/create_table.sgml | 39 ++++++++++++++++++++++++++++++-------- 3 files changed, 51 insertions(+), 9 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index b654fae1b2f..1e56edaa70b 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2730,6 +2730,16 @@ SCRAM-SHA-256$<iteration count>:&l + + + conperiod bool + + + This constraint is defined with WITHOUT OVERLAPS + (for primary keys and unique constraints). + + + conkey int2[] diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml index f789824c83b..638d912dc2d 100644 --- a/doc/src/sgml/gist.sgml +++ b/doc/src/sgml/gist.sgml @@ -292,7 +292,8 @@ CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops); The optional twelfth method stratnum is used to translate well-known RT*StrategyNumbers (from src/include/access/stratnum.h) into strategy numbers - used by the operator class. + used by the operator class. This lets the core code look up operators for + temporal constraint indexes. @@ -1179,6 +1180,14 @@ my_sortsupport(PG_FUNCTION_ARGS) operator class has no matching strategy. + + This is used for temporal index constraints (i.e., PRIMARY + KEY and UNIQUE). If the operator class + provides this function and it returns results for + RTEqualStrategyNumber, it can be used in the + non-WITHOUT OVERLAPS part(s) of an index constraint. + + The SQL declaration of the function must look like this: diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 93b3f664f21..9243810c3fe 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -77,8 +77,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI [ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | - UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters | - PRIMARY KEY ( column_name [, ... ] ) index_parameters | + UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | + PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE numeric_literal, REM UNIQUE [ NULLS [ NOT ] DISTINCT ] (column constraint) - UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) + UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) INCLUDE ( column_name [, ...]) (table constraint) @@ -978,6 +978,27 @@ WITH ( MODULUS numeric_literal, REM of these columns. + + If the WITHOUT OVERLAPS option is specified for the + last column, then that column is checked for overlaps instead of + equality. In that case, the other columns of the constraint will allow + duplicates so long as the duplicates don't overlap in the + WITHOUT OVERLAPS column. (This is sometimes called a + temporal key, if the column is a range of dates or timestamps, but + PostgreSQL allows ranges over any base type.) + In effect, such a constraint is enforced with an EXCLUDE + constraint rather than a UNIQUE constraint. So for + example UNIQUE (id, valid_at WITHOUT OVERLAPS) behaves + like EXCLUDE USING GIST (id WITH =, valid_at WITH + &&). The WITHOUT OVERLAPS column + must have a range or multirange type. Empty ranges/multiranges are + not permitted. The non-WITHOUT OVERLAPS columns of + the constraint can be any type that can be compared for equality in a + GiST index. By default, only range types are supported, but you can use + other types by adding the extension (which + is the expected way to use this feature). + + For the purpose of a unique constraint, null values are not considered equal, unless NULLS NOT DISTINCT is @@ -1000,8 +1021,10 @@ WITH ( MODULUS numeric_literal, REM Adding a unique constraint will automatically create a unique btree - index on the column or group of columns used in the constraint. The - created index has the same name as the unique constraint. + index on the column or group of columns used in the constraint. But if + the constraint includes a WITHOUT OVERLAPS clause, it + will use a GiST index. The created index has the same name as the + unique constraint. @@ -1019,7 +1042,7 @@ WITH ( MODULUS numeric_literal, REM PRIMARY KEY (column constraint) - PRIMARY KEY ( column_name [, ... ] ) + PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) INCLUDE ( column_name [, ...]) (table constraint) @@ -1054,8 +1077,8 @@ WITH ( MODULUS numeric_literal, REM Adding a PRIMARY KEY constraint will automatically create a unique btree index on the column or group of columns used in - the constraint. That index has the same name as the primary key - constraint. + the constraint, or GiST if WITHOUT OVERLAPS was + specified. -- cgit v1.2.3