On 7/9/24 00:15, jian he wrote:
**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.
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.
I like this approach a lot, but I'd like to hear what some other people think?
Jian he's &&& operator is similar to what I proposed upthread, but when either operand is an empty
value it simply raises an error. (It should be an ereport, not an elog, and I think
multirange_overlaps_multirange_internal is missing the empty check, but I can clean things up when I
integrate it into the patch series.)
This is much simpler than everything I'm doing: checking for empties in the executor phase, adding a
field to pg_class, setting things in the relcache, and checking for empties in existing rows when
you add an index. This patch uses existing infrastructure to do all the work. It seems like a much
cleaner solution.
Unlike my proposed &&& operator, it doesn't have weird mathematical
consequences.
At first I thought raising an error was not great, but it's the same thing you get when you divide
by zero. It's fine for an operator to have a restricted domain of inputs. And we would only use this
internally for primary keys and unique constraints, where indeed raising an error is just what we want.
If I don't hear objections (or think of something myself :-), I'm inclined to
use this approach.
But what do people think?
Yours,
--
Paul ~{:-)
p...@illuminatedcomputing.com
