Re: ltree_gist indexes broken after pg_upgrade from 12 to 13

On 04.03.2022 23:28, Tom Lane wrote:

Tomas Vondra <tomas.vondra@enterprisedb.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