On Thursday, July 2, 2020, Anders Steinlein <and...@e5r.no> wrote: > > >> Thanks for the tip, but I'm having a hard time thinking that's the case, >> seeing as I'm unable to trigger the wrong result no matter how hard I try >> with a new definition/manual query. I've introduced random ordering to the >> first CTE-clause (where the initial citext values comes from, and casing >> thus could differ in some order) which doesn't change the result. >> > > I just wanted to add that we're on Postgres 12.3. This matview has been > with us since 9.4 days, and we have not experienced any such issues before > (could be customers who haven't noticed or reported it to us, of course...). > version > > ------------------------------------------------------------ > --------------------------------------------------------------------- > PostgreSQL 12.3 (Ubuntu 12.3-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 >
I concur that the determinism doesn’t seem like a problem - but not much else does either. As a shot in the dark does pg_depend show any differences between the dependencies for the two views? How did this migrate from 9.4 to 12? It would be helpful if “Explain analyze refresh materialized view” were a thing (is it?) If you can backup and restore the existing database (basebackup is more likely, but pg_dump would be more useful) and still observe the problem then maybe I see hope for digging down into the cause. Otherwise I’d limit my decision to testing for the symptom with the solution being to rebuild any problem views. David J.