Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan

Hello Peter,

22.04.2024 20:59, Peter Geoghegan wrote:
>
> Pushed a fix like that just now.

I'm sorry to bother you again, but I've come across another assertion
failure. Please try the following query (I use a clean "postgres" database,
just after initdb):
EXPLAIN SELECT conname
  FROM pg_constraint WHERE conname IN ('pkey', 'ID')
  ORDER BY conname DESC;

SELECT conname
  FROM pg_constraint WHERE conname IN ('pkey', 'ID')
  ORDER BY conname DESC;

It fails for me as below:
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Index Only Scan Backward using pg_constraint_conname_nsp_index on pg_constraint  (cost=0.14..4.18 rows=2 width=64)
   Index Cond: (conname = ANY ('{pkey,ID}'::name[]))
(2 rows)

server closed the connection unexpectedly
...
with the stack trace:
...
#5  0x000055a49f81148d in ExceptionalCondition (conditionName=0x55a49f8bb540 "ItemIdHasStorage(itemId)",
    fileName=0x55a49f8bb4a8 "../../../../src/include/storage/bufpage.h", lineNumber=355) at assert.c:66
#6  0x000055a49f0f2ddd in PageGetItem (page=0x7f97cbf17000 "", itemId=0x7f97cbf2f064)
    at ../../../../src/include/storage/bufpage.h:355
#7  0x000055a49f0f9367 in _bt_checkkeys_look_ahead (scan=0x55a4a0ac4548, pstate=0x7ffd1a103670, tupnatts=2,
    tupdesc=0x7f97cb5d7be8) at nbtutils.c:4105
#8  0x000055a49f0f8ac3 in _bt_checkkeys (scan=0x55a4a0ac4548, pstate=0x7ffd1a103670, arrayKeys=true,
    tuple=0x7f97cbf18890, tupnatts=2) at nbtutils.c:3612
#9  0x000055a49f0ebb4b in _bt_readpage (scan=0x55a4a0ac4548, dir=BackwardScanDirection, offnum=20, firstPage=true)
    at nbtsearch.c:1863
...
(gdb) f 7
#7  0x000055a49f0f9367 in _bt_checkkeys_look_ahead (scan=0x55a4a0ac4548, pstate=0x7ffd1a103670, tupnatts=2,
    tupdesc=0x7f97cb5d7be8) at nbtutils.c:4105
4105            ahead = (IndexTuple) PageGetItem(pstate->page,
(gdb) p aheadoffnum
$1 = 24596
(gdb) p pstate->offnum
$2 = 20
(gdb) p pstate->targetdistance
$3 = -24576

Best regards,
Alexander