Message-ID: <b4363a52-2279-4612-8953-1100fab0c772@illuminatedcomputing.com>
Date: Wed, 17 Jul 2024 20:34:42 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SQL:2011 application time
To: jian he <jian.universality@gmail.com>
Cc: Isaac Morland <isaac.morland@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>, Jeff Davis <pgsql@j-davis.com>,
 Peter Eisentraut <peter@eisentraut.org>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
 <b4bc1ef8-4215-4cf6-933c-5ef2a2827efe@illuminatedcomputing.com>
 <85dd3ad0-fa79-46e8-a08b-c426c76825f3@eisentraut.org>
 <a5d8df78-9ff6-4273-8d06-6c85b5520520@illuminatedcomputing.com>
 <49c07eac-18ec-4aff-929c-e81b87df1092@eisentraut.org>
 <CACJufxHsYDfHK62FTHdLsgDenvJd_bEuxCiyH62Lacj7NvzX4w@mail.gmail.com>
 <d579a67c-e8d7-4eb7-ae14-f6fca01489cf@illuminatedcomputing.com>
 <1edb3b79-a4bc-43b1-80aa-2e090472fe32@eisentraut.org>
 <47550967-260b-4180-9791-b224859fe63e@illuminatedcomputing.com>
 <64c2b2ab-7ce9-475e-ac59-3bfec528bada@eisentraut.org>
 <58b6687fe9de002e4c862a412719e4f918bad987.camel@j-davis.com>
 <CA+Tgmob518sSV4Y8F4LN3X-7+7DmExznG1wCbYrOurfPL_xBOg@mail.gmail.com>
 <CAMsGm5duDGbSp4un6JaRn_MvujfTWCNMPSFDzJNnyW9jn-dWcA@mail.gmail.com>
 <f061295a-d972-42b9-bf1b-90a833e3f686@illuminatedcomputing.com>
 <CACJufxHPQaD+q6o_AMg1PG6ETOWC=VqzN_=6QV9C5zbR8j=Tww@mail.gmail.com>
Content-Language: en-US
From: Paul Jungwirth <pj@illuminatedcomputing.com>
In-Reply-To: <CACJufxHPQaD+q6o_AMg1PG6ETOWC=VqzN_=6QV9C5zbR8j=Tww@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/b4363a52-2279-4612-8953-1100fab0c772%40illuminatedcomputing.com>
Precedence: bulk

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