pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Add temporal PRIMARY KEY and UNIQUE constraints 2+ messages / 1 participants [nested] [flat]
* pgsql: Add temporal PRIMARY KEY and UNIQUE constraints @ 2024-01-24 15:43 Peter Eisentraut <peter@eisentraut.org> 0 siblings, 0 replies; 2+ messages in thread From: Peter Eisentraut @ 2024-01-24 15:43 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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.... Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/46a0cd4cefb4d9b462d8cc4df5e7ecdd190bea92 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 | 14 +- doc/src/sgml/ref/create_table.sgml | 45 ++- src/backend/access/gist/gistutil.c | 29 ++ src/backend/catalog/heap.c | 2 + src/backend/catalog/index.c | 4 + src/backend/catalog/pg_constraint.c | 2 + src/backend/commands/indexcmds.c | 143 +++++++- src/backend/commands/tablecmds.c | 6 +- src/backend/commands/trigger.c | 1 + src/backend/commands/typecmds.c | 1 + src/backend/nodes/outfuncs.c | 2 + src/backend/nodes/readfuncs.c | 2 + src/backend/parser/gram.y | 29 +- src/backend/parser/parse_utilcmd.c | 28 +- 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 | 2 + 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/parsenodes.h | 2 + src/test/regress/expected/without_overlaps.out | 395 +++++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/without_overlaps.sql | 290 +++++++++++++++++ 34 files changed, 1135 insertions(+), 52 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Add temporal PRIMARY KEY and UNIQUE constraints @ 2024-09-17 09:37 Peter Eisentraut <peter@eisentraut.org> 0 siblings, 0 replies; 2+ messages in thread From: Peter Eisentraut @ 2024-09-17 09:37 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-09-17 09:37 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-01-24 15:43 pgsql: Add temporal PRIMARY KEY and UNIQUE constraints Peter Eisentraut <peter@eisentraut.org> 2024-09-17 09:37 pgsql: Add temporal PRIMARY KEY and UNIQUE constraints Peter Eisentraut <peter@eisentraut.org>
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