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?
It would be an educational/trial-and-error experience for me as well. That you found a difference in pg_rewrite.ev_action probably provides a more fruitful avenue of attack though I'm inexperienced there as well. I do believe that inspecting pg_depend will also highlight whatever difference you are seeing in the ev_action. What tickles my curiosity is why that difference (whatever it is, I haven't looked) isn't manifesting in the \d+ output for the materialized view.