agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Manu <manuelreyesbravo@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Cc: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Cc: syzhong16@gmail.com
Subject: Re: BUG #19641: Unexpected results on an SP-GiST indexed column with a non-deterministic collation
Date: Wed, 23 Sep 2026 16:50:31 -0300
Message-ID: <179019303108.297478.2662804185242466018@gmail.com> (raw)
In-Reply-To: <CAB8bMit5fdcP6X4KrcgxTA20nXDkKWcXu3CNJN4Ro4V4b-A++Q@mail.gmail.com>
References: <CAB8bMit5fdcP6X4KrcgxTA20nXDkKWcXu3CNJN4Ro4V4b-A++Q@mail.gmail.com>
Andrey Rachitskiy <pl0h0yp1@gmail.com> wrote:
> I have a draft of "A" and "B" ready, but I decided not to publish it
> until an agreement on the direction is reached.
>
> Thoughts?
Some data for that choice, all on master (374522aa63a).
First, the scope. With the 340-row table from the report, the column
under the nondeterministic collation, and the same equality query, each
index type against a sequential scan (227 rows):
- SP-GiST text_ops: 113
- btree, hash, BRIN, GiST (btree_gist): 227
Every plan used its index, so SP-GiST's text_ops is the only one of
them that gets this wrong. It also does so at any size, one row
included.
Second, what A does to indexes that already exist. The precedent,
281039631, went in before v12's rc1, when no such index could exist
yet, while an SP-GiST index under a nondeterministic collation has been
accepted since v12. So I built a prototype of A with the same check as
the pattern_ops one in index.c, for SP-GiST text_ops, created such an
index on an unpatched master cluster, and took it to the prototype:
- a pg_dump restored with psql loads the table (340 rows) and skips
the index with one ERROR; psql exits with 0, so a restore script
that does not stop on errors ends up without the index and says
nothing;
- pg_upgrade fails, during the schema restore, with the same error.
So A cannot be back-patched, and in master it would need a pg_upgrade
check that reports these indexes before the upgrade, as pg_upgrade
does for other objects it cannot carry over.
B has neither problem. If I read your description right, it changes
only how the scan uses the tree, not how the tree is built, so an
existing index returns correct results after a minor update, without a
REINDEX. That seems to me the one that can go to all the branches.
Your point that B does not make the index a good accelerator for this
equality stands; that seems like something for the documentation to
say (a btree index serves it better), rather than a reason to break
existing schemas.
If you post the B draft, I am happy to test it on the back branches:
the existing-index case above, and the other text_ops operators under
the same collation.
Regards,
Manu
view thread (3+ messages)
Message-ID: <179019303108.297478.2662804185242466018@gmail.com>
Permalink: ../179019303108.297478.2662804185242466018@gmail.com/
Also on: postgresql.org/message-id/179019303108.297478.2662804185242466018@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: manuelreyesbravo@gmail.com, pgsql-bugs@lists.postgresql.org, pl0h0yp1@gmail.com, syzhong16@gmail.com
Subject: Re: BUG #19641: Unexpected results on an SP-GiST indexed column with a non-deterministic collation
In-Reply-To: <179019303108.297478.2662804185242466018@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox