public inbox for [email protected]
help / color / mirror / Atom feedFrom: Paul Jungwirth <[email protected]>
To: jian he <[email protected]>
Cc: Isaac Morland <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Jeff Davis <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: SQL:2011 application time
Date: Wed, 17 Jul 2024 20:34:42 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACJufxHPQaD+q6o_AMg1PG6ETOWC=VqzN_=6QV9C5zbR8j=Tww@mail.gmail.com>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CACJufxHsYDfHK62FTHdLsgDenvJd_bEuxCiyH62Lacj7NvzX4w@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CA+Tgmob518sSV4Y8F4LN3X-7+7DmExznG1wCbYrOurfPL_xBOg@mail.gmail.com>
<CAMsGm5duDGbSp4un6JaRn_MvujfTWCNMPSFDzJNnyW9jn-dWcA@mail.gmail.com>
<[email protected]>
<CACJufxHPQaD+q6o_AMg1PG6ETOWC=VqzN_=6QV9C5zbR8j=Tww@mail.gmail.com>
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 ~{:-)
[email protected]
view thread (35+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL:2011 application time
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox