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@illuminatedcomputing.com