Re: ltree_gist indexes broken after pg_upgrade from 12 to 13

On 3/4/22 20:29, Nikita Glukhov wrote:
> 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.
>

IIRC pg_upgrade is not expected to upgrade extensions - it keeps the
installed version of the extension, and that's intentional.

Moreover, it's perfectly legal to install older version, so you can do

CREATE EXTENSION ltree VERSION '1.1';

So I don't think we can call this "incomplete upgrade".

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

Does it? When I grep for LTREE_GET_ASIGLEN, I don't get any matches in
pg_trgm.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company