v1-0001-minor-refactor.no-cfbot
application/octet-stream
Filename: v1-0001-minor-refactor.no-cfbot
Type: application/octet-stream
Part: 0
Message:
Re: SQL:2011 application time
From e07de98c4ef77172d8f95f7a2e07142c1949378f Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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