From e07de98c4ef77172d8f95f7a2e07142c1949378f Mon Sep 17 00:00:00 2001 From: jian he Date: Sat, 13 Jan 2024 10:47:36 +0800 Subject: [PATCH v1 1/1] minor refactor Add WITHOUT OVERLAPS for PRIMARY KEY and UNIQUE constraints - Added WITHOUT OVERLAPS attribute to PRIMARY KEY and UNIQUE constraint. This is permit only key columns is range data type, for non-range data type, but we added support via btree-gist contrib module. - PRIMARY KEYs and UNIQUE constraints with WITHOUT OVERLAPS are backed by GiST indexes instead of B-tree indexes, since they are essentially exclusion constraints with equality(=) for the (first to the second last) parts of the key and overlaps(&&) for the last key. - Added pg_constraint.conwithoutoverlaps to say whether a constraint is a "without overlaps". - Added docs and tests. - Added pg_dump support. --- contrib/btree_gist/btree_gist--1.7--1.8.sql | 52 +++++++++---------- .../btree_gist/expected/without_overlaps.out | 7 +++ contrib/btree_gist/meson.build | 2 + contrib/btree_gist/sql/without_overlaps.sql | 6 +++ src/backend/commands/indexcmds.c | 27 ++++++++-- src/backend/utils/adt/ruleutils.c | 19 ++++--- 6 files changed, 76 insertions(+), 37 deletions(-) diff --git a/contrib/btree_gist/btree_gist--1.7--1.8.sql b/contrib/btree_gist/btree_gist--1.7--1.8.sql index 12c2e6c1..e67e6cf6 100644 --- a/contrib/btree_gist/btree_gist--1.7--1.8.sql +++ b/contrib/btree_gist/btree_gist--1.7--1.8.sql @@ -4,84 +4,84 @@ \echo Use "ALTER EXTENSION btree_gist UPDATE TO '1.8'" to load this file. \quit ALTER OPERATOR FAMILY gist_oid_ops USING gist ADD - FUNCTION 12 (oid, oid) gist_stratnum_btree (int2) ; + FUNCTION 12 (oid, oid) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_int2_ops USING gist ADD - FUNCTION 12 (int2, int2) gist_stratnum_btree (int2) ; + FUNCTION 12 (int2, int2) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_int4_ops USING gist ADD - FUNCTION 12 (int4, int4) gist_stratnum_btree (int2) ; + FUNCTION 12 (int4, int4) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_int8_ops USING gist ADD - FUNCTION 12 (int8, int8) gist_stratnum_btree (int2) ; + FUNCTION 12 (int8, int8) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_float4_ops USING gist ADD - FUNCTION 12 (float4, float4) gist_stratnum_btree (int2) ; + FUNCTION 12 (float4, float4) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_float8_ops USING gist ADD - FUNCTION 12 (float8, float8) gist_stratnum_btree (int2) ; + FUNCTION 12 (float8, float8) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_timestamp_ops USING gist ADD - FUNCTION 12 (timestamp, timestamp) gist_stratnum_btree (int2) ; + FUNCTION 12 (timestamp, timestamp) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_timestamptz_ops USING gist ADD - FUNCTION 12 (timestamptz, timestamptz) gist_stratnum_btree (int2) ; + FUNCTION 12 (timestamptz, timestamptz) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_time_ops USING gist ADD - FUNCTION 12 (time, time) gist_stratnum_btree (int2) ; + FUNCTION 12 (time, time) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_date_ops USING gist ADD - FUNCTION 12 (date, date) gist_stratnum_btree (int2) ; + FUNCTION 12 (date, date) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_interval_ops USING gist ADD - FUNCTION 12 (interval, interval) gist_stratnum_btree (int2) ; + FUNCTION 12 (interval, interval) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_cash_ops USING gist ADD - FUNCTION 12 (money, money) gist_stratnum_btree (int2) ; + FUNCTION 12 (money, money) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_macaddr_ops USING gist ADD - FUNCTION 12 (macaddr, macaddr) gist_stratnum_btree (int2) ; + FUNCTION 12 (macaddr, macaddr) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_text_ops USING gist ADD - FUNCTION 12 (text, text) gist_stratnum_btree (int2) ; + FUNCTION 12 (text, text) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_bpchar_ops USING gist ADD - FUNCTION 12 (bpchar, bpchar) gist_stratnum_btree (int2) ; + FUNCTION 12 (bpchar, bpchar) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_bytea_ops USING gist ADD - FUNCTION 12 (bytea, bytea) gist_stratnum_btree (int2) ; + FUNCTION 12 (bytea, bytea) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_numeric_ops USING gist ADD - FUNCTION 12 (numeric, numeric) gist_stratnum_btree (int2) ; + FUNCTION 12 (numeric, numeric) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_bit_ops USING gist ADD - FUNCTION 12 (bit, bit) gist_stratnum_btree (int2) ; + FUNCTION 12 (bit, bit) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_vbit_ops USING gist ADD - FUNCTION 12 (varbit, varbit) gist_stratnum_btree (int2) ; + FUNCTION 12 (varbit, varbit) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_inet_ops USING gist ADD - FUNCTION 12 (inet, inet) gist_stratnum_btree (int2) ; + FUNCTION 12 (inet, inet) gist_stratnum_btree (int2); ALTER OPERATOR FAMILY gist_cidr_ops USING gist ADD - FUNCTION 12 (cidr, cidr) gist_stratnum_btree (int2) ; + FUNCTION 12 (cidr, cidr) gist_stratnum_btree (int2); -- added in 1.2: ALTER OPERATOR FAMILY gist_timetz_ops USING gist ADD - FUNCTION 12 (timetz, timetz) gist_stratnum_btree (int2) ; + FUNCTION 12 (timetz, timetz) gist_stratnum_btree (int2); -- added in 1.3: ALTER OPERATOR FAMILY gist_uuid_ops USING gist ADD - FUNCTION 12 (uuid, uuid) gist_stratnum_btree (int2) ; + FUNCTION 12 (uuid, uuid) gist_stratnum_btree (int2); -- added in 1.4: ALTER OPERATOR FAMILY gist_macaddr8_ops USING gist ADD - FUNCTION 12 (macaddr8, macaddr8) gist_stratnum_btree (int2) ; + FUNCTION 12 (macaddr8, macaddr8) gist_stratnum_btree (int2); -- added in 1.5: ALTER OPERATOR FAMILY gist_enum_ops USING gist ADD - FUNCTION 12 (anyenum, anyenum) gist_stratnum_btree (int2) ; + FUNCTION 12 (anyenum, anyenum) gist_stratnum_btree (int2); -- added in 1.7: ALTER OPERATOR FAMILY gist_bool_ops USING gist ADD - FUNCTION 12 (bool, bool) gist_stratnum_btree (int2) ; + FUNCTION 12 (bool, bool) gist_stratnum_btree (int2); diff --git a/contrib/btree_gist/expected/without_overlaps.out b/contrib/btree_gist/expected/without_overlaps.out index 06927268..e6c0d67b 100644 --- a/contrib/btree_gist/expected/without_overlaps.out +++ b/contrib/btree_gist/expected/without_overlaps.out @@ -29,3 +29,10 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te CREATE UNIQUE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at) (1 row) +--OK. +INSERT INTO temporal_rng VALUES (1, '[2022-01-01,2023-01-01]'); +--should fail. +INSERT INTO temporal_rng VALUES (1, '[2022-06-01,2023-01-01]'); +ERROR: conflicting key value violates exclusion constraint "temporal_rng_pk" +DETAIL: Key (id, valid_at)=(1, ["Wed Jun 01 00:00:00 2022","Sun Jan 01 00:00:00 2023"]) conflicts with existing key (id, valid_at)=(1, ["Sat Jan 01 00:00:00 2022","Sun Jan 01 00:00:00 2023"]). +DROP TABLE temporal_rng; diff --git a/contrib/btree_gist/meson.build b/contrib/btree_gist/meson.build index c88a6ac8..15aad864 100644 --- a/contrib/btree_gist/meson.build +++ b/contrib/btree_gist/meson.build @@ -50,6 +50,7 @@ install_data( 'btree_gist--1.4--1.5.sql', 'btree_gist--1.5--1.6.sql', 'btree_gist--1.6--1.7.sql', + 'btree_gist--1.7--1.8.sql', kwargs: contrib_data_args, ) @@ -89,6 +90,7 @@ tests += { 'enum', 'bool', 'partitions', + 'without_overlaps', ], }, } diff --git a/contrib/btree_gist/sql/without_overlaps.sql b/contrib/btree_gist/sql/without_overlaps.sql index 98665dbb..3327969f 100644 --- a/contrib/btree_gist/sql/without_overlaps.sql +++ b/contrib/btree_gist/sql/without_overlaps.sql @@ -11,3 +11,9 @@ CREATE TABLE temporal_rng ( \d temporal_rng SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; + +--OK. +INSERT INTO temporal_rng VALUES (1, '[2022-01-01,2023-01-01]'); +--should fail. +INSERT INTO temporal_rng VALUES (1, '[2022-06-01,2023-01-01]'); +DROP TABLE temporal_rng; \ No newline at end of file diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 30162ee7..dad41a16 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -2471,20 +2471,34 @@ GetOperatorFromCanonicalStrategy(Oid opclass, */ *strat = gistTranslateStratnum(opclass, opstrat); if (!StrategyIsValid(*strat)) + { + char *error_message; + if (strcmp(opname,"overlaps") == 0) + error_message = psprintf("no %s operator found for WITHOUT OVERLAPS constraint", + opname); + else if (strcmp(opname,"equals") == 0) + error_message = psprintf("no %s operator found for equals constraint", opname); + else + error_message = psprintf("no %s operator found", opname); + ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("no %s operator found for WITHOUT OVERLAPS constraint", opname), + errmsg("%s", error_message), errdetail("Could not translate strategy number %u for opclass %d.", - opstrat, opclass), + opstrat, opclass), errhint("Define a stratnum support function for your GiST opclass."))); + } *opid = get_opfamily_member(opfamily, opcintype, opcintype, *strat); } + else + elog(ERROR, "cache lookup failed for operator class %u", opclass); if (!OidIsValid(*opid)) { HeapTuple opftuple; Form_pg_opfamily opfform; + char *error_message; /* * attribute->opclass might not explicitly name the opfamily, @@ -2498,9 +2512,16 @@ GetOperatorFromCanonicalStrategy(Oid opclass, opfamily); opfform = (Form_pg_opfamily) GETSTRUCT(opftuple); + if (strcmp(opname,"overlaps")) + error_message = psprintf("no %s operator found for WITHOUT OVERLAPS constraint", opname); + else if (strcmp(opname,"equals")) + error_message = psprintf("no %s operator found for equals constraint", opname); + else + error_message = psprintf("no %s operator found", opname); + ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("no %s operator found for WITHOUT OVERLAPS constraint", opname), + errmsg("%s", error_message), errdetail("There must be an %s operator within opfamily \"%s\" for type \"%s\".", opname, NameStr(opfform->opfname), diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index d140ab4b..92674909 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -339,7 +339,7 @@ static char *pg_get_viewdef_worker(Oid viewoid, int prettyFlags, int wrapColumn); static char *pg_get_triggerdef_worker(Oid trigid, bool pretty); static int decompile_column_index_array(Datum column_index_array, Oid relId, - bool withoutOverlaps, StringInfo buf); + StringInfo buf); static char *pg_get_ruledef_worker(Oid ruleoid, int prettyFlags); static char *pg_get_indexdef_worker(Oid indexrelid, int colno, const Oid *excludeOps, @@ -2247,7 +2247,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_conkey); - decompile_column_index_array(val, conForm->conrelid, false, &buf); + decompile_column_index_array(val, conForm->conrelid, &buf); /* add foreign relation name */ appendStringInfo(&buf, ") REFERENCES %s(", @@ -2258,7 +2258,9 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_confkey); - decompile_column_index_array(val, conForm->confrelid, false, &buf); + decompile_column_index_array(val, conForm->confrelid, &buf); + if (conForm->conwithoutoverlaps) + appendStringInfoString(&buf, " WITHOUT OVERLAPS"); appendStringInfoChar(&buf, ')'); @@ -2344,7 +2346,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, if (!isnull) { appendStringInfoString(&buf, " ("); - decompile_column_index_array(val, conForm->conrelid, false, &buf); + decompile_column_index_array(val, conForm->conrelid, &buf); appendStringInfoChar(&buf, ')'); } @@ -2379,7 +2381,10 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_conkey); - keyatts = decompile_column_index_array(val, conForm->conrelid, conForm->conwithoutoverlaps, &buf); + keyatts = decompile_column_index_array(val, conForm->conrelid, &buf); + + if (conForm->conwithoutoverlaps) + appendStringInfoString(&buf, " WITHOUT OVERLAPS"); appendStringInfoChar(&buf, ')'); @@ -2575,7 +2580,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, */ static int decompile_column_index_array(Datum column_index_array, Oid relId, - bool withoutOverlaps, StringInfo buf) + StringInfo buf) { Datum *keys; int nKeys; @@ -2596,8 +2601,6 @@ decompile_column_index_array(Datum column_index_array, Oid relId, else appendStringInfo(buf, ", %s", quote_identifier(colName)); } - if (withoutOverlaps) - appendStringInfoString(buf, " WITHOUT OVERLAPS"); return nKeys; } -- 2.34.1