Re: SQL:2011 application time - Mailing list pgsql-hackers
From | Paul Jungwirth |
---|---|
Subject | Re: SQL:2011 application time |
Date | |
Msg-id | b4363a52-2279-4612-8953-1100fab0c772@illuminatedcomputing.com Whole thread Raw |
In response to | Re: SQL:2011 application time (jian he <jian.universality@gmail.com>) |
Responses |
Re: SQL:2011 application time
|
List | pgsql-hackers |
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
pgsql-hackers by date: