Thread: Index created with PGSQL11 on ubuntu 18.04 corrupt with PGSQL11 on ubuntu 20.04

I'm using postgresql 11 builds from http://apt.postgresql.org/pub/repos/apt/
- I've got a database created under Ubuntu 18.04, and recently updated to
Ubuntu 20.04. These are all docker builds

I've got an index defined as follows

CREATE UNIQUE INDEX entity_settings_wh_unique_rawdata ON wrd.entity_settings
USING btree (attribute, digest(upper((unique_rawdata)::text),
'sha256'::text)) WHERE ((attribute IS NOT NULL) AND ((unique_rawdata)::text
<> ''::text)

And I've got a database which, when started under Ubuntu 18.04, finds one
record for this query:

SELECT * FROM "wrd"."entity_settings" T1 WHERE upper(left(T1."rawdata",
264))=upper(left('UT-MC', 264)) AND (T1."attribute"=3060);

But if I start it with Ubuntu 20.04, with the same postgres version, the
query doesn't find anything.

Switching back to postgres 11 under 18.04 shows the record again, back to 11
under 20.04, and it's gone. Somehow postgres 11 under 20.04 cannot read the
index created by 18.04

select version on 18.04 gives:

 PostgreSQL 11.10 (Ubuntu 11.10-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

select version on 20.04 gives:

 PostgreSQL 11.10 (Ubuntu 11.10-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

so as far as I can tell, these should be almost exactly the same. I can't
find any difference in the environment either, and "SHOW ALL" is identical
for both installations

Any pointers on what I should be looking at why these two installations
might view the same data differently?





--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On Tue, Feb 2, 2021 at 11:20 AM unilynx <unilynx@gmail.com> wrote:
>
> I'm using postgresql 11 builds from http://apt.postgresql.org/pub/repos/apt/
> - I've got a database created under Ubuntu 18.04, and recently updated to
> Ubuntu 20.04. These are all docker builds

This is a known problem when upgrading Ubuntu (and most other Linux
distributions, but it depends on which version of course -- btu for
Ubuntu LTS the problem is triggered when going to 20.04). If you have
any indexes on text-type columns, they need to be reindexed.

See https://wiki.postgresql.org/wiki/Locale_data_changes

Note that this is triggered by the Ubuntu upgrade, not by upgrading
PostgreSQL -- that's why it happend even when you keep using the same
PostgreSQL version.

--
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Thanks, that was quick. Didn't realise the distribution's locale tables would
have been a source of differences too



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html