On Thu, Jun 6, 2024 at 4:56 AM Paul Jungwirth
<p...@illuminatedcomputing.com> wrote:
>
> On 5/21/24 11:27, Isaac Morland wrote:
> > On Tue, 21 May 2024 at 13:57, Robert Haas <robertmh...@gmail.com
> > <mailto:robertmh...@gmail.com>> wrote:
> >
> > What I think is less clear is what that means for temporal primary
> > keys. As Paul pointed out upthread, in every other case, a temporal
> > primary key is at least as unique as a regular primary key, but in
> > this case, it isn't. And someone might reasonably think that a
> > temporal primary key should exclude empty ranges just as all primary
> > keys exclude nulls. Or they might think the opposite.
> >
> >
> > Fascinating. I think you're absolutely right that it's clear that two empty
> > intervals don't
> > conflict. If somebody wants to claim two intervals conflict, they need to
> > point to at least one
> > instant in time that is common between them.
> >
> > But a major point of a primary key, it seems to me, is that it uniquely
> > identifies a row. If items
> > are identified by a time range, non-overlapping or not, then the empty
> > range can only identify one
> > item (per value of whatever other columns are in the primary key). I think
> > for a unique key the
> > non-overlapping restriction has to be considered an additional restriction
> > on top of the usual
> > uniqueness restriction.
> >
> > I suspect in many applications there will be a non-empty constraint; for
> > example, it seems quite
> > reasonable to me for a meeting booking system to forbid empty meetings. But
> > when they are allowed
> > they should behave in the mathematically appropriate way.
>
> Finding a way forward for temporal PKs got a lot of discussion at pgconf.dev
> (thanks especially to
> Peter Eisentraut and Jeff Davis!), so I wanted to summarize some options and
> describe what I think
> is the best approach.
>
> First the problem: empty ranges! A temporal PK/UNIQUE constraint is basically
> an exclusion
> constraint that is `(id WITH =, valid_at WITH &&)`. But the special 'empty'
> value never overlaps
> anything, *including itself*. (Note it has no "position": [3,3) is the same
> as [4,4).) Since the
> exclusion constraint forbids overlapping ranges, and empties never overlap,
> your table can have
> duplicates. (I'm talking about "literal uniqueness" as discussed in [1].) For
> instance:
>
> CREATE EXTENSION btree_gist;
> CREATE TABLE t (id int, valid_at daterange, name text);
> ALTER TABLE t ADD CONSTRAINT tpk PRIMARY KEY (id, valid_at WITHOUT
> OVERLAPS);
> INSERT INTO t VALUES (1, 'empty', 'foo');
> INSERT INTO t VALUES (1, 'empty', 'bar');
>
> Multiranges have the same problem. So what do we do about that?
>
> **Option 0**: Allow it but document it. It shouldn't happen in practice:
> there is no reason for an
> empty range to get into a temporal table, and it arguably doesn't mean
> anything. The record is true
> at no time? But of course it will happen anyway. It's a footgun and will
> break expectations for at
> least some.
>
> It causes problems for us too. If you say `SELECT name FROM t GROUP BY id,
> valid_at`, we recognize
> that `name` is a functional dependency on the PK, so we allow it and give you
> the first row matching
> each key. You might get "foo" or you might get "bar". Also the planner uses
> not-nullable uniqueness
> to take many shortcuts. I couldn't create any concrete breakage there, but I
> bet someone else could.
> PKs that are not literally unique seems like something that would cause
> headaches for years.
>
> **Option 1**: Temporal PKs should automatically create a CHECK constraint
> that forbids empty ranges.
> Should UNIQUE constraints too? I'm tempted to say no, since sometimes users
> surprise us by coming up
> with new ways to use things. For instance one way to use empty ranges is to
> reference a temporal
> table from a non-temporal table, since `'empty' <@ anything` is always true
> (though this has
> questionable meaning or practical use). But probably we should forbid empties
> for UNIQUE constraints
> too. Forbidding them is more aligned with the SQL standard, which says that
> when you have a PERIOD,
> startcol < endcol (not <=). And it feels more consistent to treat both
> constraints the same way.
> Finally, if UNIQUEs do allow empties, we still risk confusing our planner.
>
> My last patch created these CHECK constraints for PKs (but not UNIQUEs) as
> INTERNAL dependencies.
> It's pretty clunky. There are lots of cases to handle, e.g. `ALTER COLUMN c
> TYPE` may reuse the PK
> index or may generate a new one. And what if the user already created the
> same constraint? Seeing
> all the trouble giving PKs automatic (cataloged) NOT NULL constraints makes
> me wary about this
> approach. It's not as bad, since there is no legacy, but it's still more
> annoying than I expected.
>
> Finally, hanging the CHECK constraint off the PK sets us up for problems when
> we add true PERIODs.
> Under 11.27 of SQL/Foundation, General Rules 2b says that defining a PERIOD
> should automatically add
> a CHECK constraint that startcol < endcol. That is already part of my last
> patch in this series. But
> that would be redundant with the constraint from the PK. And attaching the
> constraint to the PERIOD
> is a lot simpler than attaching it to the PK.
>
> **Option 2**: Add a new operator, called &&&, that works like && except an
> empty range *does*
> overlap another empty range. Empty ranges should still not overlap anything
> else. This would fix the
> exclusion constraint. You could add `(5, 'empty')` once but not twice. This
> would allow empties to
> people who want to use them. (We would still forbid them if you define a
> PERIOD, because those come
> with the CHECK constraint mentioned above.)
> And there is almost nothing to code. But it is mathematically suspect to say
> an empty range overlaps
> something small (something with zero width) but not something big. Surely if
> a && b and b <@ c, then
> a && c? So this feels like the kind of elegant hack that you eventually
> regret.
>
> **Option 3**: Forbid empties, not as a reified CHECK constraint, but just
> with some code in the
> executor. Again we could do just PKs or PKs and UNIQUEs. Let's do both, for
> all the reasons above.
> Not creating a CHECK constraint is much less clunky. There is no catalog
> entry to create/drop. Users
> don't wonder where it came from when they say `\d t`. It can't conflict with
> constraints of their
> own. We would enforce this in ExecConstraints, where we enforce NOT NULL and
> CHECK constraints, for
> any table with constraints where conperiod is true. We'd also need to do this
> check on existing rows
> when you create a temporal PK/UQ. This option also requires a new field in
> pg_class: just as we have
> relchecks, relhasrules, relhastriggers, etc. to let us skip work in the
> relcache, I assume we'd want
> relperiods.
>
> **Option 4**: Teach GiST indexes to enforce uniqueness. We didn't discuss
> this at pgconf, at least
> not in reference to the empties problem. But I was thinking about this
> request from Matthias for
> temporal PKs & UQs to support `USING INDEX idx`.[2] It is confusing that a
> temporal index has
> indisunique, but if you try to create a unique GiST index directly we say
> they don't support UNIQUE
> indexes! Similarly `pg_indexam_has_property(783, 'can_unique')` returns
> false. There is something
> muddled about all that. So how about we give the GiST AM handler amcanunique?
>
I think we can Forbid empties,not not mess with pg_class.
to make the communication smooth, i've set the base commit to
46a0cd4cefb4d9b462d8cc4df5e7ecdd190bea92
{Add temporal PRIMARY KEY and UNIQUE constraints}
https://git.postgresql.org/cgit/postgresql.git/commit/?id=46a0cd4cefb4d9b462d8cc4df5e7ecdd190bea92
you can git reset --hard 46a0cd4cefb4d9b462d8cc4df5e7ecdd190bea92
then apply the attached patch.
I hope I understand it correctly.
previously revert is only because the special value: empty.
i tried to use the operator &&&, new gist strategy number, pg_amop
entry to solve the problem.
Now with the applied patch, if the range column is specified WITHOUT OVERLAPS,
then this column is not allowed to have any empty range value.
logic work through:
* duplicate logic of range_overlaps but disallow empty value. also
have the operator &&&, (almost equivalent to &&)
* add new gist strategy number
* thanks to add stratnum GiST support function
(https://git.postgresql.org/cgit/postgresql.git/commit/?id=6db4598fcb82a87a683c4572707e522504830a2b)
now we can set the strategy number to the mapped new function
(equivalent to range_overlaps, but error out empty value)
* in ComputeIndexAttrs, set the strategy number to the newly created
StrategyNumber in "else if (iswithoutoverlaps)" block.
* Similarly refactor src/backend/utils/adt/rangetypes_gist.c make the
index value validation using newly created function.
function name, error message maybe not great now, but it works.
------full demo, also see the comments.
DROP TABLE if exists temporal_rng;
CREATE TABLE temporal_rng (id int4range, valid_at tsrange);
ALTER TABLE temporal_rng
ADD CONSTRAINT temporal_rng_pk
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
--should be fine.
INSERT INTO temporal_rng VALUES ('empty', '[2022-01-01,2022-01-02]');
--will error out, period column, empty range not allowed
INSERT INTO temporal_rng VALUES ('[3,3]', 'empty');
ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk;
--period constraint dropped, now should be fine.
INSERT INTO temporal_rng VALUES ('[3,3]', 'empty');
--reinstall constraint, should error out
--because existing one row has empty value.
ALTER TABLE temporal_rng
ADD CONSTRAINT temporal_rng_pk
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
delete from temporal_rng where id = '[3,3]';
--reinstall constraint, should be fine, because empty value removed.
ALTER TABLE temporal_rng
ADD CONSTRAINT temporal_rng_pk
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
From d8721a13a2575f2e74841168b12dc018c90a0c66 Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Tue, 9 Jul 2024 15:14:10 +0800
Subject: [PATCH v1 1/1] forbid empty range value for period column.
if one column have WITHOUT OVERLAPS attribute,
then forbid this column to have 'empty' range value.
---
src/backend/commands/indexcmds.c | 4 +-
src/backend/utils/adt/multirangetypes.c | 35 ++++++++++++++
src/backend/utils/adt/rangetypes.c | 46 +++++++++++++++++++
src/backend/utils/adt/rangetypes_gist.c | 8 ++++
src/include/access/stratnum.h | 3 +-
src/include/catalog/pg_amop.dat | 6 +++
src/include/catalog/pg_operator.dat | 11 +++++
src/include/catalog/pg_proc.dat | 8 ++++
src/include/utils/multirangetypes.h | 4 ++
src/include/utils/rangetypes.h | 3 ++
src/test/regress/expected/opr_sanity.out | 6 ++-
.../regress/expected/without_overlaps.out | 2 +
src/test/regress/sql/without_overlaps.sql | 2 +
13 files changed, 133 insertions(+), 5 deletions(-)
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 7a87626f..acce7378 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -2178,7 +2178,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
Oid opid;
if (attn == nkeycols - 1)
- strat = RTOverlapStrategyNumber;
+ strat = RTPeriodOverlapStrategyNumber;
else
strat = RTEqualStrategyNumber;
GetOperatorFromWellKnownStrategy(opclassOids[attn], atttype,
@@ -2441,7 +2441,7 @@ GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype,
Oid opcintype;
StrategyNumber instrat = *strat;
- Assert(instrat == RTEqualStrategyNumber || instrat == RTOverlapStrategyNumber);
+ Assert(instrat == RTEqualStrategyNumber || instrat == RTOverlapStrategyNumber || instrat == RTPeriodOverlapStrategyNumber);
*opid = InvalidOid;
diff --git a/src/backend/utils/adt/multirangetypes.c b/src/backend/utils/adt/multirangetypes.c
index f82e6f42..d30d6291 100644
--- a/src/backend/utils/adt/multirangetypes.c
+++ b/src/backend/utils/adt/multirangetypes.c
@@ -1969,6 +1969,19 @@ multirange_overlaps_multirange(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(multirange_overlaps_multirange_internal(typcache->rngtype, mr1, mr2));
}
+
+Datum
+period_multirange_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_overlaps_multirange_internal(typcache->rngtype, mr1, mr2));
+}
+
/*
* Comparison function for checking if any range of multirange overlaps given
* key range using binary search.
@@ -1990,6 +2003,28 @@ multirange_range_overlaps_bsearch_comparison(TypeCacheEntry *typcache,
return 0;
}
+bool
+period_overlaps_multirange_internal(TypeCacheEntry *rangetyp,
+ const RangeType *r,
+ const MultirangeType *mr)
+{
+ RangeBound bounds[2];
+ bool empty;
+
+ /*
+ * Empties never overlap, even with empties. (This seems strange since
+ * they *do* contain each other, but we want to follow how ranges work.)
+ */
+ if (RangeIsEmpty(r) || MultirangeIsEmpty(mr))
+ elog(ERROR, "multirange types cannot be empty in this case");
+
+ range_deserialize(rangetyp, r, &bounds[0], &bounds[1], &empty);
+ Assert(!empty);
+
+ return multirange_bsearch_match(rangetyp, mr, bounds,
+ multirange_range_overlaps_bsearch_comparison);
+}
+
bool
range_overlaps_multirange_internal(TypeCacheEntry *rangetyp,
const RangeType *r,
diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index 2d94a6b8..42f72341 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -869,6 +869,39 @@ range_overlaps_internal(TypeCacheEntry *typcache, const RangeType *r1, const Ran
return false;
}
+/* overlaps? (internal version) */
+bool
+period_overlaps_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2)
+{
+ RangeBound lower1,
+ lower2;
+ RangeBound upper1,
+ upper2;
+ bool empty1,
+ empty2;
+
+ /* Different types should be prevented by ANYRANGE matching rules */
+ if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2))
+ elog(ERROR, "range types do not match");
+
+ range_deserialize(typcache, r1, &lower1, &upper1, &empty1);
+ range_deserialize(typcache, r2, &lower2, &upper2, &empty2);
+
+ /* An empty range does not overlap any other range */
+ if (empty1 || empty2)
+ elog(ERROR, "period cannot be empty");
+
+ if (range_cmp_bounds(typcache, &lower1, &lower2) >= 0 &&
+ range_cmp_bounds(typcache, &lower1, &upper2) <= 0)
+ return true;
+
+ if (range_cmp_bounds(typcache, &lower2, &lower1) >= 0 &&
+ range_cmp_bounds(typcache, &lower2, &upper1) <= 0)
+ return true;
+
+ return false;
+}
+
/* overlaps? */
Datum
range_overlaps(PG_FUNCTION_ARGS)
@@ -882,6 +915,19 @@ range_overlaps(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(range_overlaps_internal(typcache, r1, r2));
}
+/* period (range that does have empty range value) overlaps? */
+Datum
+period_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(period_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 cb28e985..ac60def4 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 PERIODSTRAT_OVERLAPS:
+ return period_overlaps_internal(typcache, key, query);
case RANGESTRAT_OVERRIGHT:
if (RangeIsEmpty(key) || RangeIsEmpty(query))
return false;
@@ -989,6 +991,8 @@ range_gist_consistent_int_multirange(TypeCacheEntry *typcache,
if (RangeIsEmpty(key) || MultirangeIsEmpty(query))
return false;
return (!range_after_multirange_internal(typcache, key, query));
+ case PERIODSTRAT_OVERLAPS:
+ return period_overlaps_multirange_internal(typcache, key, query);
case RANGESTRAT_OVERLAPS:
return range_overlaps_multirange_internal(typcache, key, query);
case RANGESTRAT_OVERRIGHT:
@@ -1066,6 +1070,8 @@ range_gist_consistent_leaf_range(TypeCacheEntry *typcache,
return range_before_internal(typcache, key, query);
case RANGESTRAT_OVERLEFT:
return range_overleft_internal(typcache, key, query);
+ case PERIODSTRAT_OVERLAPS:
+ return period_overlaps_internal(typcache, key, query);
case RANGESTRAT_OVERLAPS:
return range_overlaps_internal(typcache, key, query);
case RANGESTRAT_OVERRIGHT:
@@ -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 PERIODSTRAT_OVERLAPS:
+ return period_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 8a47d3c9..94737af4 100644
--- a/src/include/access/stratnum.h
+++ b/src/include/access/stratnum.h
@@ -78,8 +78,9 @@ typedef uint16 StrategyNumber;
#define RTPrefixStrategyNumber 28 /* for text ^@ */
#define RTOldBelowStrategyNumber 29 /* for old spelling of <<| */
#define RTOldAboveStrategyNumber 30 /* for old spelling of |>> */
+#define RTPeriodOverlapStrategyNumber 31 /* for special gist &&& */
-#define RTMaxStrategyNumber 30
+#define RTMaxStrategyNumber 31
#endif /* STRATNUM_H */
diff --git a/src/include/catalog/pg_amop.dat b/src/include/catalog/pg_amop.dat
index d8a05214..96590c32 100644
--- a/src/include/catalog/pg_amop.dat
+++ b/src/include/catalog/pg_amop.dat
@@ -1358,6 +1358,9 @@
{ amopfamily => 'gist/range_ops', amoplefttype => 'anyrange',
amoprighttype => 'anyrange', amopstrategy => '3',
amopopr => '&&(anyrange,anyrange)', amopmethod => 'gist' },
+{ amopfamily => 'gist/range_ops', amoplefttype => 'anyrange',
+ amoprighttype => 'anyrange', amopstrategy => '31',
+ amopopr => '&&&(anyrange,anyrange)', amopmethod => 'gist' },
{ amopfamily => 'gist/range_ops', amoplefttype => 'anyrange',
amoprighttype => 'anymultirange', amopstrategy => '3',
amopopr => '&&(anyrange,anymultirange)', amopmethod => 'gist' },
@@ -1414,6 +1417,9 @@
{ amopfamily => 'gist/multirange_ops', amoplefttype => 'anymultirange',
amoprighttype => 'anymultirange', amopstrategy => '3',
amopopr => '&&(anymultirange,anymultirange)', amopmethod => 'gist' },
+{ amopfamily => 'gist/multirange_ops', amoplefttype => 'anymultirange',
+ amoprighttype => 'anymultirange', amopstrategy => '31',
+ amopopr => '&&&(anymultirange,anymultirange)', amopmethod => 'gist' },
{ amopfamily => 'gist/multirange_ops', amoplefttype => 'anymultirange',
amoprighttype => 'anyrange', amopstrategy => '3',
amopopr => '&&(anymultirange,anyrange)', amopmethod => 'gist' },
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index 0e7511dd..4022b9f7 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_PERIOD_OVERLAP_OP', descr => 'period overlaps',
+ oprname => '&&&', oprleft => 'anyrange', oprright => 'anyrange',
+ oprresult => 'bool', oprcom => '&&&(anyrange,anyrange)',
+ oprcode => 'period_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 => 'PERIOD_OID_MULTIRANGE_OVERLAPS_MULTIRANGE_OP',
+ descr => 'overlaps',
+ oprname => '&&&', oprleft => 'anymultirange', oprright => 'anymultirange',
+ oprresult => 'bool', oprcom => '&&&(anymultirange,anymultirange)',
+ oprcode => 'period_multirange_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 e4115cd0..a876842f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10506,6 +10506,10 @@
{ oid => '3857',
proname => 'range_overlaps', prorettype => 'bool',
proargtypes => 'anyrange anyrange', prosrc => 'range_overlaps' },
+{ oid => '4551',
+ proname => 'period_overlaps', prorettype => 'bool',
+ proargtypes => 'anyrange anyrange', prosrc => 'period_overlaps' },
+
{ oid => '3858',
proname => 'range_contains_elem', prosupport => 'range_contains_elem_support',
prorettype => 'bool', proargtypes => 'anyrange anyelement',
@@ -10749,6 +10753,10 @@
proname => 'multirange_overlaps_multirange', prorettype => 'bool',
proargtypes => 'anymultirange anymultirange',
prosrc => 'multirange_overlaps_multirange' },
+{ oid => '4554',
+ proname => 'period_multirange_overlaps_multirange', prorettype => 'bool',
+ proargtypes => 'anymultirange anymultirange',
+ prosrc => 'period_multirange_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 84525439..3c6032fa 100644
--- a/src/include/utils/multirangetypes.h
+++ b/src/include/utils/multirangetypes.h
@@ -89,6 +89,10 @@ extern bool range_contains_multirange_internal(TypeCacheEntry *rangetyp,
extern bool multirange_contains_multirange_internal(TypeCacheEntry *rangetyp,
const MultirangeType *mr1,
const MultirangeType *mr2);
+extern bool period_overlaps_multirange_internal(TypeCacheEntry *rangetyp,
+ const RangeType *r,
+ const MultirangeType *mr);
+
extern bool range_overlaps_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 2b574873..7817abf3 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 PERIODSTRAT_OVERLAPS RTPeriodOverlapStrategyNumber
#define RANGESTRAT_OVERRIGHT RTOverRightStrategyNumber
#define RANGESTRAT_AFTER RTRightStrategyNumber
#define RANGESTRAT_ADJACENT RTSameStrategyNumber
@@ -126,6 +127,8 @@ extern bool range_after_internal(TypeCacheEntry *typcache, const RangeType *r1,
const RangeType *r2);
extern bool range_adjacent_internal(TypeCacheEntry *typcache, const RangeType *r1,
const RangeType *r2);
+extern bool period_overlaps_internal(TypeCacheEntry *typcache, const RangeType *r1,
+ const RangeType *r2);
extern bool range_overlaps_internal(TypeCacheEntry *typcache, const RangeType *r1,
const RangeType *r2);
extern bool range_overleft_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 7610b011..8217ad0a 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -1147,6 +1147,7 @@ ORDER BY 1, 2;
# | #
& | &
&& | &&
+ &&& | &&&
* | *
*< | *>
*<= | *>=
@@ -1173,7 +1174,7 @@ ORDER BY 1, 2;
~<=~ | ~>=~
~<~ | ~>~
~= | ~=
-(29 rows)
+(30 rows)
-- Likewise for negator pairs.
SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2
@@ -2008,6 +2009,7 @@ ORDER BY 1, 2, 3;
783 | 28 | <@
783 | 29 | <^
783 | 30 | >^
+ 783 | 31 | &&&
783 | 48 | <@
783 | 68 | <@
2742 | 1 | &&
@@ -2088,7 +2090,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 726e9410..ac9cb412 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -289,6 +289,8 @@ DETAIL: Failing row contains (null, ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:
INSERT INTO temporal_rng VALUES ('[3,3]', NULL);
ERROR: null value in column "valid_at" of relation "temporal_rng" violates not-null constraint
DETAIL: Failing row contains ([3,4), null).
+INSERT INTO temporal_rng VALUES ('[3,3]', 'empty');
+ERROR: period cannot be empty
--
-- test a range with both a PK and a UNIQUE constraint
--
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index c8e8ab99..658fa5fb 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -214,6 +214,8 @@ INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-01', '2018-01-05'));
INSERT INTO temporal_rng VALUES (NULL, tsrange('2018-01-01', '2018-01-05'));
INSERT INTO temporal_rng VALUES ('[3,3]', NULL);
+INSERT INTO temporal_rng VALUES ('[3,3]', 'empty');
+
--
-- test a range with both a PK and a UNIQUE constraint
--
--
2.34.1
