Thread: Re: Bug in nbtree SAOP scans with non-required arrays, truncated high key

On Wed, Dec 18, 2024 at 3:20 PM Peter Geoghegan <pg@bowt.ie> wrote:
> Attached fix addresses the issue by consistently resetting the scan's
> so->scanBehind flag (which might still be set to true from the
> previous page's high key) at the start of _bt_advance_array_keys.

I pushed this fix just now.

I should point out (for the benefit of Tom, or whoever writes the next
set of release notes) that I think that this bug is very unlikely to
occur in practice. Getting wrong answers to queries could only happen
given an index with more than 3 columns, and only for scans with just
the right combination of scan key types. It could also only happen
when the scan advances its array keys using high keys with more than a
single truncated attribute.

Note also that assert-enabled builds would always have seen assertion
failures whenever something like my test case ran. Presumably we'd
have seen a report about such an assertion failure if the underlying
problem was at all common.

--
Peter Geoghegan

On Thu, 2024-12-19 at 11:09 -0500, Peter Geoghegan wrote:
> I should point out (for the benefit of Tom, or whoever writes the next
> set of release notes) that I think that this bug is very unlikely to
> occur in practice. Getting wrong answers to queries could only happen
> given an index with more than 3 columns, and only for scans with just
> the right combination of scan key types. It could also only happen
> when the scan advances its array keys using high keys with more than a
> single truncated attribute.

Just for the record: a customer just ran into this bug.  REINDEX did
not fix the bad query result, but update to 17.3 did.

Yours,
Laurenz Albe

-- 

*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den 
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat 
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte, 
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder 
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich 
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.

*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are 
confidential and may be privileged or otherwise protected from disclosure 
and solely for the use of the person(s) or entity to whom it is intended. 
If you have received this message in error and are not the intended 
recipient, please notify the sender immediately and delete this message and 
any attachment from your system. If you are not the intended recipient, be 
advised that any use of this message is prohibited and may be unlawful, and 
you must not copy this message or attachment or disclose the contents to 
any other person.

On Tue, Feb 18, 2025 at 8:34 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> Just for the record: a customer just ran into this bug.  REINDEX did
> not fix the bad query result, but update to 17.3 did.

That's surprising, since REINDEX definitely will "fix" my test case
when run on an unpatched server with the bug.

The issue that my repro highlighted hinged upon 2 low-order truncated
attributes. nbtsort.c (used by CREATE INDEX/REINDEX) doesn't care
about making suffix truncation as effective as reasonably possible,
unlike retail inserts + page splits, which use the nbtsplitloc.c logic
for this. This was also why index fillfactor was tuned by the test
case. Basically, the test case is very sensitive to various
parameters.

--
Peter Geoghegan