Re: ltree_gist indexes broken after pg_upgrade from 12 to 13

On 25.02.2022 00:15, Tomas Vondra wrote:

> Hi,
>
> Victor Yegorov reported a crash related to a GiST index on ltree [1],
> following a pg_upgrade from 12.x to 14.x, with a data set reproducing
> this. I spent some time investigating this, and it seems this is a silly
> bug in commit
>
> commit 911e70207703799605f5a0e8aad9f06cff067c63 (HEAD)
> Author: Alexander Korotkov <akorotkov(at)postgresql(dot)org>
> Date: Mon Mar 30 19:17:11 2020 +0300
>
> Implement operator class parameters
> ...
>
> in PG13, which modified ltree_gist so that siglen is opclass parameter
> (and not hard-coded). But the procedures use LTREE_GET_ASIGLEN macro to
> extract the value, which either extracts the value from the catalog (if
> the index has opclass parameter) or uses a default value - but it always
> uses LTREE_ASIGLEN_DEFAULT, which belongs to _ltree_gist opclass (i.e.
> for array of ltree). And that's 28 instead of the 8, as it should be.

It seems that ltree extension simply was not updated to v1.2 after
pg_upgrade:
  ALTER EXTENSION ltree UPDATE TO '1.2'; -- is missing

Upgrade script ltree--1.1--1.2.sql creates ltree_gist_options() and
registers it in the opclass. ltree_gist_options() initializes bytea
options using the correct SIGLEN_DEFAULT=8.

If ltree_gist_options() is absent, LTREE_GET_ASIGLEN() receives NULL
and wrong LTREE_ASIGLEN_DEFAULT is used. But if ltree_gist_options()
is registered, LTREE_GET_ASIGLEN() receives non-NULL bytea options
with the correct default value.

So, we probably have corrupted indexes that were updated since such
"incomplete" upgrade of ltree.

Also I found that contrib/pg_trgm/trgm_gist.c uses wrongly named macro
LTREE_GET_ASIGLEN().

--
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company