Re: SQL:2011 application time

On 7/17/24 20:34, Paul Jungwirth wrote:
> 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.)

I thought of a possible problem: this operator works great if there are already rows in the table,
but what if the *first row you insert* has an empty range? Then there is nothing to compare against,
so the operator will never be used. Right?

Except when I test it, it still works! After running `make installcheck`, I did this:

regression=# truncate temporal_rng cascade;
NOTICE: truncate cascades to table "temporal_fk_rng2rng"
TRUNCATE TABLE
regression=# insert into temporal_rng values ('[1,2)', 'empty');
ERROR: range cannot be empty

My mental model must be wrong. Can anyone explain what is happening there? Is it something we can
depend on?

So I swapped in the &&& patch, cleaned it up, and added tests. But something is wrong. After I get
one failure from an empty, I keep getting failures, even though the table is empty:

regression=# truncate temporal_rng cascade;
NOTICE: truncate cascades to table "temporal_fk_rng2rng"
TRUNCATE TABLE
regression=# insert into temporal_rng values ('[1,2)', '[2000-01-01,2010-01-01)'); -- ok so far
INSERT 0 1
regression=# insert into temporal_rng values ('[1,2)', 'empty'); -- should fail and does
ERROR: range cannot be empty
regression=# insert into temporal_rng values ('[1,2)', '[2010-01-01,2020-01-01)'); -- uh oh
ERROR: range cannot be empty
regression=# truncate temporal_rng cascade;
NOTICE: truncate cascades to table "temporal_fk_rng2rng"
TRUNCATE TABLE
regression=# insert into temporal_rng values ('[1,2)', '[2000-01-01,2010-01-01)'); -- ok so far
INSERT 0 1
regression=# insert into temporal_rng values ('[1,2)', '[2010-01-01,2020-01-01)'); -- ok now
INSERT 0 1

It looks like the index is getting corrupted. Continuing from the above:

regression=# create extension pageinspect;
CREATE EXTENSION
regression=# select gist_page_items(get_raw_page('temporal_rng_pk', 0), 'temporal_rng_pk');
gist_page_items
----------------------------------------------------------------------------
(1,"(0,1)",40,f,"(id, valid_at)=(""[1,2)"", ""[2000-01-01,2010-01-01)"")")
(2,"(0,2)",40,f,"(id, valid_at)=(""[1,2)"", ""[2010-01-01,2020-01-01)"")")
(2 rows)

regression=# insert into temporal_rng values ('[1,2)', 'empty');
ERROR: range cannot be empty
regression=# select gist_page_items(get_raw_page('temporal_rng_pk', 0), 'temporal_rng_pk');
gist_page_items
----------------------------------------------------------------------------
(1,"(0,1)",40,f,"(id, valid_at)=(""[1,2)"", ""[2000-01-01,2010-01-01)"")")
(2,"(0,2)",40,f,"(id, valid_at)=(""[1,2)"", ""[2010-01-01,2020-01-01)"")")
(3,"(0,3)",32,f,"(id, valid_at)=(""[1,2)"", empty)")
(3 rows)

So maybe this is a bad place to ereport? Or is this a deeper bug with GiST? Here is where we're
doing it:

#0 range_nonempty_overlaps_internal (typcache=0x635a7fbf67f0, r1=0x635a7fc11f20, r2=0x635a7fc11f40)
at rangetypes.c:876
#1 0x0000635a7f06175d in range_gist_consistent_leaf_range (typcache=0x635a7fbf67f0, strategy=31,
key=0x635a7fc11f20, query=0x635a7fc11f40)
at rangetypes_gist.c:1076
#2 0x0000635a7f05fc9a in range_gist_consistent (fcinfo=0x7ffcd20f9f60) at rangetypes_gist.c:216
#3 0x0000635a7f12d780 in FunctionCall5Coll (flinfo=0x635a7fb44eb8, collation=0,
arg1=140723832725648, arg2=109240340727454, arg3=31, arg4=0,
arg5=140723832725567) at fmgr.c:1242
#4 0x0000635a7e999af6 in gistindex_keytest (scan=0x635a7fb44d50, tuple=0x7d155c0a3fd0,
page=0x7d155c0a2000 "", offset=1, recheck_p=0x7ffcd20fa129,
recheck_distances_p=0x7ffcd20fa12a) at gistget.c:221
#5 0x0000635a7e99a109 in gistScanPage (scan=0x635a7fb44d50, pageItem=0x7ffcd20fa1e0,
myDistances=0x0, tbm=0x0, ntids=0x0) at gistget.c:436
#6 0x0000635a7e99a797 in gistgettuple (scan=0x635a7fb44d50, dir=ForwardScanDirection) at gistget.c:637
#7 0x0000635a7e9e4d38 in index_getnext_tid (scan=0x635a7fb44d50, direction=ForwardScanDirection) at
indexam.c:590
#8 0x0000635a7e9e4f7d in index_getnext_slot (scan=0x635a7fb44d50, direction=ForwardScanDirection,
slot=0x635a7fb44950) at indexam.c:682
#9 0x0000635a7ec5690b in check_exclusion_or_unique_constraint (heap=0x7d1560cea348,
index=0x7d1560cedd98, indexInfo=0x635a7fb44c40, tupleid=0x635a7fb44580,
values=0x7ffcd20faf00, isnull=0x7ffcd20faee0, estate=0x635a7fb434a0, newIndex=false,
waitMode=CEOUC_WAIT, violationOK=false, conflictTid=0x0)
at execIndexing.c:780
#10 0x0000635a7ec55c58 in ExecInsertIndexTuples (resultRelInfo=0x635a7fb43930, slot=0x635a7fb44550,
estate=0x635a7fb434a0, update=false, noDupErr=false,
specConflict=0x0, arbiterIndexes=0x0, onlySummarizing=false) at execIndexing.c:483
#11 0x0000635a7eca38a2 in ExecInsert (context=0x7ffcd20fb1b0, resultRelInfo=0x635a7fb43930,
slot=0x635a7fb44550, canSetTag=true, inserted_tuple=0x0,
insert_destrel=0x0) at nodeModifyTable.c:1145

Is there anything I can do to save this &&& idea? I've attached the patches I'm working with,
rebased to cd85ae1114.

If ereport just won't work, then I might explore other definitions of a &&& operator. It was really
nice to have such a clean solution.

Yours,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com