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
