From 2f32839278b255a8a71dacfa510c78fa21c994d9 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Thu, 18 Jul 2024 09:52:56 -0700 Subject: [PATCH v36 3/3] WIP: Use nonempty_overlaps to forbid empty ranges/multiranges Tests are failing. We get the "range cannot be empty" error even when inserting a non-empty range, after a previous failure from an empty range. `SELECT *` says the table has no rows. That is concerning. Is the ereport causing the index to get corrupted? --- src/backend/commands/indexcmds.c | 8 ++-- src/backend/parser/parse_utilcmd.c | 3 +- src/backend/utils/adt/multirangetypes.c | 48 +++++++++++++++++++ src/backend/utils/adt/rangetypes.c | 23 +++++++++ src/backend/utils/adt/rangetypes_gist.c | 8 ++++ src/include/access/stratnum.h | 1 + src/include/catalog/pg_amop.dat | 6 +++ src/include/catalog/pg_operator.dat | 11 +++++ src/include/catalog/pg_proc.dat | 11 +++++ src/include/utils/multirangetypes.h | 6 +++ src/include/utils/rangetypes.h | 3 ++ src/test/regress/expected/opr_sanity.out | 13 +++-- .../regress/expected/without_overlaps.out | 26 ++++++++++ src/test/regress/sql/without_overlaps.sql | 18 +++++++ 14 files changed, 175 insertions(+), 10 deletions(-) diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 47e139b3828..a73456a03b5 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -2186,7 +2186,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo, Oid opid; if (attn == nkeycols - 1) - strat = RTOverlapStrategyNumber; + strat = RTNonemptyOverlapStrategyNumber; else strat = RTEqualStrategyNumber; GetOperatorFromWellKnownStrategy(opclassOids[attn], atttype, @@ -2449,7 +2449,7 @@ GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype, Oid opcintype; StrategyNumber instrat = *strat; - Assert(instrat == RTEqualStrategyNumber || instrat == RTOverlapStrategyNumber); + Assert(instrat == RTEqualStrategyNumber || instrat == RTNonemptyOverlapStrategyNumber); *opid = InvalidOid; @@ -2474,7 +2474,7 @@ GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype, errcode(ERRCODE_UNDEFINED_OBJECT), instrat == RTEqualStrategyNumber ? errmsg("could not identify an equality operator for type %s", format_type_be(atttype)) : - errmsg("could not identify an overlaps operator for type %s", format_type_be(atttype)), + errmsg("could not identify a nonempty overlaps operator for type %s", format_type_be(atttype)), errdetail("Could not translate strategy number %d for operator class \"%s\" for access method \"%s\".", instrat, NameStr(((Form_pg_opclass) GETSTRUCT(tuple))->opcname), "gist")); @@ -2496,7 +2496,7 @@ GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype, errcode(ERRCODE_UNDEFINED_OBJECT), instrat == RTEqualStrategyNumber ? errmsg("could not identify an equality operator for type %s", format_type_be(atttype)) : - errmsg("could not identify an overlaps operator for type %s", format_type_be(atttype)), + errmsg("could not identify a nonempty overlaps operator for type %s", format_type_be(atttype)), errdetail("There is no suitable operator in operator family \"%s\" for access method \"%s\".", NameStr(((Form_pg_opfamily) GETSTRUCT(tuple))->opfname), "gist")); } diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 53492c644a9..b6a84a7b43b 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -2397,7 +2397,8 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) * For UNIQUE and PRIMARY KEY, we just have a list of column names. * * Make sure referenced keys exist. If we are making a PRIMARY KEY index, - * also make sure they are NOT NULL. + * also make sure they are NOT NULL. For WITHOUT OVERLAPS constraints, + * we make sure the last part is a range or multirange. */ else { diff --git a/src/backend/utils/adt/multirangetypes.c b/src/backend/utils/adt/multirangetypes.c index 558c6c18c34..942f6d26de3 100644 --- a/src/backend/utils/adt/multirangetypes.c +++ b/src/backend/utils/adt/multirangetypes.c @@ -1968,6 +1968,30 @@ multirange_overlaps_multirange(PG_FUNCTION_ARGS) PG_RETURN_BOOL(multirange_overlaps_multirange_internal(typcache->rngtype, mr1, mr2)); } +Datum +range_nonempty_overlaps_multirange(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + PG_RETURN_BOOL(range_nonempty_overlaps_multirange_internal(typcache->rngtype, r, mr)); +} + +Datum +multirange_nonempty_overlaps_multirange(PG_FUNCTION_ARGS) +{ + MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0); + MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr1)); + + PG_RETURN_BOOL(multirange_nonempty_overlaps_multirange_internal(typcache->rngtype, mr1, mr2)); +} + /* * Comparison function for checking if any range of multirange overlaps given * key range using binary search. @@ -2068,6 +2092,30 @@ multirange_overlaps_multirange_internal(TypeCacheEntry *rangetyp, return false; } +bool +range_nonempty_overlaps_multirange_internal(TypeCacheEntry *typcache, + const RangeType *r, + const MultirangeType *mr) +{ + if (RangeIsEmpty(r)) + ereport(ERROR, (errmsg("range cannot be empty"))); + if (MultirangeIsEmpty(mr)) + ereport(ERROR, (errmsg("multirange cannot be empty"))); + + return range_overlaps_multirange_internal(typcache, r, mr); +} + +bool +multirange_nonempty_overlaps_multirange_internal(TypeCacheEntry *typcache, + const MultirangeType *mr1, + const MultirangeType *mr2) +{ + if (MultirangeIsEmpty(mr1) || MultirangeIsEmpty(mr2)) + ereport(ERROR, (errmsg("multirange cannot be empty"))); + + return multirange_overlaps_multirange_internal(typcache, mr1, mr2); +} + /* does not extend to right of? */ bool range_overleft_multirange_internal(TypeCacheEntry *rangetyp, diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c index 2d94a6b8774..6dad038037e 100644 --- a/src/backend/utils/adt/rangetypes.c +++ b/src/backend/utils/adt/rangetypes.c @@ -869,6 +869,16 @@ range_overlaps_internal(TypeCacheEntry *typcache, const RangeType *r1, const Ran return false; } +/* overlaps and neither empty? (internal version) */ +bool +range_nonempty_overlaps_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2) +{ + if (RangeIsEmpty(r1) || RangeIsEmpty(r2)) + ereport(ERROR, (errmsg("range cannot be empty"))); + + return range_overlaps_internal(typcache, r1, r2); +} + /* overlaps? */ Datum range_overlaps(PG_FUNCTION_ARGS) @@ -882,6 +892,19 @@ range_overlaps(PG_FUNCTION_ARGS) PG_RETURN_BOOL(range_overlaps_internal(typcache, r1, r2)); } +/* overlaps and neither empty? */ +Datum +range_nonempty_overlaps(PG_FUNCTION_ARGS) +{ + RangeType *r1 = PG_GETARG_RANGE_P(0); + RangeType *r2 = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); + + PG_RETURN_BOOL(range_nonempty_overlaps_internal(typcache, r1, r2)); +} + /* does not extend to right of? (internal version) */ bool range_overleft_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2) diff --git a/src/backend/utils/adt/rangetypes_gist.c b/src/backend/utils/adt/rangetypes_gist.c index cb28e9859ab..39de28cd163 100644 --- a/src/backend/utils/adt/rangetypes_gist.c +++ b/src/backend/utils/adt/rangetypes_gist.c @@ -929,6 +929,8 @@ range_gist_consistent_int_range(TypeCacheEntry *typcache, return (!range_after_internal(typcache, key, query)); case RANGESTRAT_OVERLAPS: return range_overlaps_internal(typcache, key, query); + case RANGESTRAT_NONEMPTY_OVERLAPS: + return range_nonempty_overlaps_internal(typcache, key, query); case RANGESTRAT_OVERRIGHT: if (RangeIsEmpty(key) || RangeIsEmpty(query)) return false; @@ -991,6 +993,8 @@ range_gist_consistent_int_multirange(TypeCacheEntry *typcache, return (!range_after_multirange_internal(typcache, key, query)); case RANGESTRAT_OVERLAPS: return range_overlaps_multirange_internal(typcache, key, query); + case RANGESTRAT_NONEMPTY_OVERLAPS: + return range_nonempty_overlaps_multirange_internal(typcache, key, query); case RANGESTRAT_OVERRIGHT: if (RangeIsEmpty(key) || MultirangeIsEmpty(query)) return false; @@ -1068,6 +1072,8 @@ range_gist_consistent_leaf_range(TypeCacheEntry *typcache, return range_overleft_internal(typcache, key, query); case RANGESTRAT_OVERLAPS: return range_overlaps_internal(typcache, key, query); + case RANGESTRAT_NONEMPTY_OVERLAPS: + return range_nonempty_overlaps_internal(typcache, key, query); case RANGESTRAT_OVERRIGHT: return range_overright_internal(typcache, key, query); case RANGESTRAT_AFTER: @@ -1103,6 +1109,8 @@ range_gist_consistent_leaf_multirange(TypeCacheEntry *typcache, return range_overleft_multirange_internal(typcache, key, query); case RANGESTRAT_OVERLAPS: return range_overlaps_multirange_internal(typcache, key, query); + case RANGESTRAT_NONEMPTY_OVERLAPS: + return range_nonempty_overlaps_multirange_internal(typcache, key, query); case RANGESTRAT_OVERRIGHT: return range_overright_multirange_internal(typcache, key, query); case RANGESTRAT_AFTER: diff --git a/src/include/access/stratnum.h b/src/include/access/stratnum.h index 8a47d3c9ec8..58e8a40c0f0 100644 --- a/src/include/access/stratnum.h +++ b/src/include/access/stratnum.h @@ -78,6 +78,7 @@ typedef uint16 StrategyNumber; #define RTPrefixStrategyNumber 28 /* for text ^@ */ #define RTOldBelowStrategyNumber 29 /* for old spelling of <<| */ #define RTOldAboveStrategyNumber 30 /* for old spelling of |>> */ +#define RTNonemptyOverlapStrategyNumber 31 /* for &&& */ #define RTMaxStrategyNumber 30 diff --git a/src/include/catalog/pg_amop.dat b/src/include/catalog/pg_amop.dat index d8a05214b11..b8434915fdd 100644 --- a/src/include/catalog/pg_amop.dat +++ b/src/include/catalog/pg_amop.dat @@ -1361,6 +1361,9 @@ { amopfamily => 'gist/range_ops', amoplefttype => 'anyrange', amoprighttype => 'anymultirange', amopstrategy => '3', amopopr => '&&(anyrange,anymultirange)', amopmethod => 'gist' }, +{ amopfamily => 'gist/range_ops', amoplefttype => 'anyrange', + amoprighttype => 'anyrange', amopstrategy => '31', + amopopr => '&&&(anyrange,anyrange)', amopmethod => 'gist' }, { amopfamily => 'gist/range_ops', amoplefttype => 'anyrange', amoprighttype => 'anyrange', amopstrategy => '4', amopopr => '&>(anyrange,anyrange)', amopmethod => 'gist' }, @@ -1417,6 +1420,9 @@ { amopfamily => 'gist/multirange_ops', amoplefttype => 'anymultirange', amoprighttype => 'anyrange', amopstrategy => '3', amopopr => '&&(anymultirange,anyrange)', amopmethod => 'gist' }, +{ amopfamily => 'gist/multirange_ops', amoplefttype => 'anymultirange', + amoprighttype => 'anymultirange', amopstrategy => '31', + amopopr => '&&&(anymultirange,anymultirange)', amopmethod => 'gist' }, { amopfamily => 'gist/multirange_ops', amoplefttype => 'anymultirange', amoprighttype => 'anymultirange', amopstrategy => '4', amopopr => '&>(anymultirange,anymultirange)', amopmethod => 'gist' }, diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat index 0e7511dde1c..d64a5a2c9e7 100644 --- a/src/include/catalog/pg_operator.dat +++ b/src/include/catalog/pg_operator.dat @@ -3095,6 +3095,11 @@ oprresult => 'bool', oprcom => '&&(anyrange,anyrange)', oprcode => 'range_overlaps', oprrest => 'rangesel', oprjoin => 'areajoinsel' }, +{ oid => '4552', oid_symbol => 'OID_RANGE_NONEMPTY_OVERLAP_OP', descr => 'nonempty overlaps', + oprname => '&&&', oprleft => 'anyrange', oprright => 'anyrange', + oprresult => 'bool', oprcom => '&&&(anyrange,anyrange)', + oprcode => 'range_nonempty_overlaps', oprrest => 'rangesel', + oprjoin => 'areajoinsel' }, { oid => '3889', oid_symbol => 'OID_RANGE_CONTAINS_ELEM_OP', descr => 'contains', oprname => '@>', oprleft => 'anyrange', oprright => 'anyelement', @@ -3314,6 +3319,12 @@ oprresult => 'bool', oprcom => '&&(anymultirange,anymultirange)', oprcode => 'multirange_overlaps_multirange', oprrest => 'multirangesel', oprjoin => 'areajoinsel' }, +{ oid => '4553', oid_symbol => 'OID_MULTIRANGE_NONEMPTY_OVERLAPS_MULTIRANGE_OP', + descr => 'nonempty overlaps', + oprname => '&&&', oprleft => 'anymultirange', oprright => 'anymultirange', + oprresult => 'bool', oprcom => '&&&(anymultirange,anymultirange)', + oprcode => 'multirange_nonempty_overlaps_multirange', oprrest => 'multirangesel', + oprjoin => 'areajoinsel' }, { oid => '2869', oid_symbol => 'OID_MULTIRANGE_CONTAINS_ELEM_OP', descr => 'contains', oprname => '@>', oprleft => 'anymultirange', oprright => 'anyelement', diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 7ea6e0fcff5..dcdcde81a07 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10552,6 +10552,9 @@ { oid => '3857', proname => 'range_overlaps', prorettype => 'bool', proargtypes => 'anyrange anyrange', prosrc => 'range_overlaps' }, +{ oid => '4551', + proname => 'range_nonempty_overlaps', prorettype => 'bool', + proargtypes => 'anyrange anyrange', prosrc => 'range_nonempty_overlaps' }, { oid => '3858', proname => 'range_contains_elem', prosupport => 'range_contains_elem_support', prorettype => 'bool', proargtypes => 'anyrange anyelement', @@ -10795,6 +10798,14 @@ proname => 'multirange_overlaps_multirange', prorettype => 'bool', proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_overlaps_multirange' }, +{ oid => '4555', + proname => 'range_nonempty_overlaps_multirange', prorettype => 'bool', + proargtypes => 'anyrange anymultirange', + prosrc => 'range_nonempty_overlaps_multirange' }, +{ oid => '4554', + proname => 'multirange_nonempty_overlaps_multirange', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', + prosrc => 'multirange_nonempty_overlaps_multirange' }, { oid => '4249', proname => 'multirange_contains_elem', prorettype => 'bool', proargtypes => 'anymultirange anyelement', diff --git a/src/include/utils/multirangetypes.h b/src/include/utils/multirangetypes.h index 845254395b6..23371108a46 100644 --- a/src/include/utils/multirangetypes.h +++ b/src/include/utils/multirangetypes.h @@ -92,9 +92,15 @@ extern bool multirange_contains_multirange_internal(TypeCacheEntry *rangetyp, extern bool range_overlaps_multirange_internal(TypeCacheEntry *rangetyp, const RangeType *r, const MultirangeType *mr); +extern bool range_nonempty_overlaps_multirange_internal(TypeCacheEntry *rangetyp, + const RangeType *r, + const MultirangeType *mr); extern bool multirange_overlaps_multirange_internal(TypeCacheEntry *rangetyp, const MultirangeType *mr1, const MultirangeType *mr2); +extern bool multirange_nonempty_overlaps_multirange_internal(TypeCacheEntry *rangetyp, + const MultirangeType *mr1, + const MultirangeType *mr2); extern bool range_overleft_multirange_internal(TypeCacheEntry *rangetyp, const RangeType *r, const MultirangeType *mr); diff --git a/src/include/utils/rangetypes.h b/src/include/utils/rangetypes.h index 2b574873cef..a9ac10e131d 100644 --- a/src/include/utils/rangetypes.h +++ b/src/include/utils/rangetypes.h @@ -97,6 +97,7 @@ RangeTypePGetDatum(const RangeType *X) #define RANGESTRAT_BEFORE RTLeftStrategyNumber #define RANGESTRAT_OVERLEFT RTOverLeftStrategyNumber #define RANGESTRAT_OVERLAPS RTOverlapStrategyNumber +#define RANGESTRAT_NONEMPTY_OVERLAPS RTNonemptyOverlapStrategyNumber #define RANGESTRAT_OVERRIGHT RTOverRightStrategyNumber #define RANGESTRAT_AFTER RTRightStrategyNumber #define RANGESTRAT_ADJACENT RTSameStrategyNumber @@ -128,6 +129,8 @@ extern bool range_adjacent_internal(TypeCacheEntry *typcache, const RangeType *r const RangeType *r2); extern bool range_overlaps_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2); +extern bool range_nonempty_overlaps_internal(TypeCacheEntry *typcache, const RangeType *r1, + const RangeType *r2); extern bool range_overleft_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2); extern bool range_overright_internal(TypeCacheEntry *typcache, const RangeType *r1, diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 9d047b21b88..d8059a72702 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -518,9 +518,10 @@ SELECT p1.oid, p1.proname FROM pg_proc as p1 LEFT JOIN pg_description as d ON p1.tableoid = d.classoid and p1.oid = d.objoid and d.objsubid = 0 WHERE d.classoid IS NULL AND p1.oid <= 9999; - oid | proname ------+--------- -(0 rows) + oid | proname +------+------------------------------------ + 4555 | range_nonempty_overlaps_multirange +(1 row) -- List of built-in leakproof functions -- @@ -1149,6 +1150,7 @@ ORDER BY 1, 2; # | # & | & && | && + &&& | &&& * | * *< | *> *<= | *>= @@ -1175,7 +1177,7 @@ ORDER BY 1, 2; ~<=~ | ~>=~ ~<~ | ~>~ ~= | ~= -(29 rows) +(30 rows) -- Likewise for negator pairs. SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2 @@ -2010,6 +2012,7 @@ ORDER BY 1, 2, 3; 783 | 28 | <@ 783 | 29 | <^ 783 | 30 | >^ + 783 | 31 | &&& 783 | 48 | <@ 783 | 68 | <@ 2742 | 1 | && @@ -2090,7 +2093,7 @@ ORDER BY 1, 2, 3; 4000 | 28 | ^@ 4000 | 29 | <^ 4000 | 30 | >^ -(124 rows) +(125 rows) -- Check that all opclass search operators have selectivity estimators. -- This is not absolutely required, but it seems a reasonable thing diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index 94234758598..04038a83fba 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -379,6 +379,11 @@ DROP TABLE temporal3; -- range PK: test with existing rows -- ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk; +-- no empties allowed, even the first row: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', 'empty'); +ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ERROR: range cannot be empty +DELETE FROM temporal_rng; -- okay: INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03')); INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04')); @@ -398,6 +403,14 @@ DELETE FROM temporal_rng; -- -- range PK: test inserts -- +-- should fail: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', 'empty'); +ERROR: range cannot be empty +SELECT * FROM temporal_rng; + id | valid_at +----+---------- +(0 rows) + -- okay: INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03')); INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04')); @@ -592,6 +605,11 @@ DROP TABLE temporal_rng3; -- multirange PK: test with existing rows -- ALTER TABLE temporal_mltrng DROP CONSTRAINT temporal_mltrng_pk; +-- no empties allowed, even the first row: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', '{}'); +ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ERROR: range cannot be empty +DELETE FROM temporal_mltrng; -- okay: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03'))); INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04'))); @@ -611,6 +629,14 @@ DELETE FROM temporal_mltrng; -- -- multirange PK: test inserts -- +-- should fail: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', '{}'); +ERROR: multirange cannot be empty +SELECT * FROM temporal_mltrng; + id | valid_at +----+---------- +(0 rows) + -- okay: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03'))); INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04'))); diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index 36b3e6dc02a..cf4a22345f9 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -251,6 +251,11 @@ DROP TABLE temporal3; ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk; +-- no empties allowed, even the first row: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', 'empty'); +ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DELETE FROM temporal_rng; + -- okay: INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03')); INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04')); @@ -271,6 +276,10 @@ DELETE FROM temporal_rng; -- range PK: test inserts -- +-- should fail: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', 'empty'); +SELECT * FROM temporal_rng; + -- okay: INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03')); INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04')); @@ -408,6 +417,11 @@ DROP TABLE temporal_rng3; ALTER TABLE temporal_mltrng DROP CONSTRAINT temporal_mltrng_pk; +-- no empties allowed, even the first row: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', '{}'); +ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DELETE FROM temporal_mltrng; + -- okay: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03'))); INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04'))); @@ -428,6 +442,10 @@ DELETE FROM temporal_mltrng; -- multirange PK: test inserts -- +-- should fail: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', '{}'); +SELECT * FROM temporal_mltrng; + -- okay: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03'))); INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04'))); -- 2.42.0