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