Re: SQL:2011 application time

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              ~{:-)
pj@illuminatedcomputing.com