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

On Mon, Aug 26, 2024 at 10:00 AM Alexander Lakhin <exclusion(at)gmail(dot)com> wrote:
> I'm sorry to bother you again, but I've come across another assertion
> failure.

You've found a real bug. I should be the one apologizing - not you.

> 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;

The problem is that _bt_checkkeys_look_ahead didn't quite get
everything right with sanitizing the page offset number it uses to
check if a later tuple is before the recently advanced array scan
keys. The page offset itself was checked, but in a way that was faulty
in cases where the int16 we use could overflow.

I can fix the bug by making sure that pstate->targetdistance (an int16
variable) can only be doubled when it actually makes sense. That way
there can never be an int16 overflow, and so the final offnum cannot
underflow to a value that's much higher than the page's max offset
number.

This approach works:

/*
* The look ahead distance starts small, and ramps up as each call here
* allows _bt_readpage to skip over more tuples
*/
if (!pstate->targetdistance)
pstate->targetdistance = LOOK_AHEAD_DEFAULT_DISTANCE;
- else
+ else if (pstate->targetdistance < MaxIndexTuplesPerPage / 2)
pstate->targetdistance *= 2;

I'll push a fix along these lines shortly.

Thanks for the report!
--
Peter Geoghegan