diff options
| author | Peter Eisentraut <peter@eisentraut.org> | 2024-09-17 10:36:09 +0200 |
|---|---|---|
| committer | Peter Eisentraut <peter@eisentraut.org> | 2024-09-17 11:29:30 +0200 |
| commit | fc0438b4e80535419a4e54dba87642cdf84defda (patch) | |
| tree | b63dcc505ae98c2ef3b8143f6f38d2c0ca9b892b /src/include/catalog | |
| parent | 7406ab623fee1addcb21c881afecbe638a0d56e9 (diff) | |
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 <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
Diffstat (limited to 'src/include/catalog')
| -rw-r--r-- | src/include/catalog/catversion.h | 2 | ||||
| -rw-r--r-- | src/include/catalog/index.h | 1 | ||||
| -rw-r--r-- | src/include/catalog/pg_constraint.h | 10 |
3 files changed, 11 insertions, 2 deletions
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index d46a9efe908..fe92cdd632f 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202409171 +#define CATALOG_VERSION_NO 202409172 #endif diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index 7d434f8e653..2dea96f47c3 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -93,6 +93,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_WITHOUT_OVERLAPS (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 7a8017f15b5..96c00624b15 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -107,6 +107,12 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) /* Has a local definition and cannot be inherited */ bool connoinherit; + /* + * For primary keys and unique constraints, signifies the last column uses + * overlaps instead of equals. + */ + bool conperiod; + #ifdef CATALOG_VARLEN /* variable-length fields start here */ /* @@ -146,7 +152,8 @@ 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 unique constraints/primary + * keys using WITHOUT OVERLAPS. */ Oid conexclop[1] BKI_LOOKUP(pg_operator); @@ -238,6 +245,7 @@ extern Oid CreateConstraintEntry(const char *constraintName, bool conIsLocal, int conInhCount, bool conNoInherit, + bool conPeriod, bool is_internal); extern bool ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId, |