Re: ltree_gist indexes broken after pg_upgrade from 12 to 13

On 04.03.2022 23:28, Tom Lane wrote:

> Tomas Vondra<tomas(dot)vondra(at)enterprisedb(dot)com> writes:
>> On 3/4/22 20:29, Nikita Glukhov wrote:
>>> 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.
> Yeah, exactly. But this opens up an additional consideration we
> have to account for: whatever we do needs to work with either 1.1
> or 1.2 SQL-level versions of the extension.
>
> regards, tom lane

It becomes clear that ltree upgrade 1.1 => 1.2 is broken, the problem
is not so much related to PG12 => PG13+ upgrades.

The following examples crashes PG13+:

CREATE EXTENSION ltree VERSION "1.1";

CREATE TABLE test AS
SELECT i::text::ltree data
FROM generate_series(1, 100000) i;

CREATE INDEX ON test USING gist (data);

ALTER EXTENSION ltree UPDATE TO "1.2";

-- works, cached bytea options is still NULL and siglen is 28
SELECT * FROM test WHERE data = '12345';

\connect

-- crash, siglen = 8
SELECT * FROM test WHERE data = '12345';

You can see here another problem: installation of opclass options
procedure does not invalidate relation's opclass options cache.

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