From: Peter Eisentraut <peter@eisentraut.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Add temporal PRIMARY KEY and UNIQUE constraints
Date: Tue, 17 Sep 2024 09:37:07 +0000
Message-ID: <E1sqUdu-001Uag-U2@gemulon.postgresql.org> (raw)
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....
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/fc0438b4e80535419a4e54dba87642cdf84defda
Modified Files
--------------
contrib/btree_gist/Makefile | 2 +-
contrib/btree_gist/expected/without_overlaps.out | 44 +
contrib/btree_gist/meson.build | 1 +
contrib/btree_gist/sql/without_overlaps.sql | 25 +
doc/src/sgml/catalogs.sgml | 10 +
doc/src/sgml/gist.sgml | 11 +-
doc/src/sgml/ref/create_table.sgml | 39 +-
src/backend/access/gist/gistutil.c | 29 +
src/backend/catalog/heap.c | 1 +
src/backend/catalog/index.c | 14 +-
src/backend/catalog/pg_constraint.c | 2 +
src/backend/commands/indexcmds.c | 162 ++-
src/backend/commands/tablecmds.c | 6 +-
src/backend/commands/trigger.c | 1 +
src/backend/commands/typecmds.c | 2 +
src/backend/executor/execIndexing.c | 66 +-
src/backend/nodes/makefuncs.c | 4 +-
src/backend/optimizer/util/plancat.c | 9 +-
src/backend/parser/gram.y | 29 +-
src/backend/parser/parse_utilcmd.c | 80 +-
src/backend/utils/adt/ruleutils.c | 2 +
src/backend/utils/cache/relcache.c | 18 +-
src/bin/pg_dump/pg_dump.c | 16 +-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 36 +
src/bin/psql/describe.c | 12 +-
src/include/access/gist.h | 3 +
src/include/catalog/catversion.h | 2 +-
src/include/catalog/index.h | 1 +
src/include/catalog/pg_constraint.h | 10 +-
src/include/commands/defrem.h | 6 +-
src/include/nodes/execnodes.h | 1 +
src/include/nodes/makefuncs.h | 2 +-
src/include/nodes/parsenodes.h | 2 +
src/test/regress/expected/without_overlaps.out | 1336 ++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/without_overlaps.sql | 923 +++++++++++++++
37 files changed, 2848 insertions(+), 62 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: peter@eisentraut.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Add temporal PRIMARY KEY and UNIQUE constraints
In-Reply-To: <E1sqUdu-001Uag-U2@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox