Re: ltree_gist indexes broken after pg_upgrade from 12 to 13

On 3/6/22 08:09, Alexander Korotkov wrote:
> Hi!
>
> Sorry for this terrible oversight by me.
>
> On Sat, Mar 5, 2022 at 10:13 AM Tomas Vondra
> <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>> On 3/4/22 23:09, Nikita Glukhov wrote:
>>> 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.
>
> So, it seems that ltree 1.1 in PG13+ is incompatible with ltree on
> PG12 and ltree 1.2 on PG13+. And there are many scenarios involving.
>
> It seems too difficult to identify all the broken cases in the release
> notes. What about applying a patch and asking all ltree users to
> reindex their indexes?
>

Yeah. I think this is getting so complicated that there's little chance
we'd be able to clearly explain when to reindex.

>> Well, it's quite a mess :-(
>>
>> It very clearly is not just 1.1 -> 1.2 upgrade issue, because you can
>> get a crash with 1.1 after PG12 -> PG13 upgrade, as demonstrated
>> earlier. So just "fixing" the extension upgrade is no enough.
>>
>> But as you showed, 1.1 -> 1.2 upgrade is broken too.
>>
>>>
>>> You can see here another problem: installation of opclass options
>>> procedure does not invalidate relation's opclass options cache.
>>>
>>
>> Seems like that.
>
> I think opclass options procedure shouldn't normally change opclass
> options chache. Before installation of options procedure, there
> should be no options. And options procedure shouldn't change the
> defaults in this case.
>

I should clarify I haven't looked into the details - I merely verified
accessing the index in the same backend works, and after reconnection it
crashes (per Nikita's example). I don't know why that happans.

regards

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