On Thursday, July 2, 2020, Anders Steinlein <anders@e5r.no> wrote:
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?
Could be worth checking, yes. Could you give me any guidance as to how to compare this? Never looked at pg_depend before -- which of the columns should have the oid for the matview I want to look up dependencies for?
How did this migrate from 9.4 to 12?
pg_dump and pg_restore. It's been a few months, so unfortunately I can't recall which pg_dump version was used. Another thing to possibly note is that the citext extension has subsequently been updated as well; I'm unsure if the matview has been recreated after that (if that could have any effect).
It would be helpful if “Explain analyze refresh materialized view” were a thing (is it?)
Yeah, I was looking for that too initially when investigating. Unfortunately, "Utility statements have no plan structure" is the response given.
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.
I //think// I have the dump laying around, but if simply rebuilding the view fixes the problem I'm inclined to just do that, although the issue is a bit concerning. If anyone here suspects an actual bug with a possible avenue for further investigation, in which case I would be happy to help.
Best,
-- a