Anders Steinlein <anders@e5r.no> writes: > We have a materialized view from which a customer reported some > confusing/invalid results, leading us to inspect the query and not finding > anything wrong. Running the query defining the matview manually, or > creating a new (identical) materialized view returns the correct result. > Obviously, we've done REFRESH MATERIALIZED VIEW just before doing the > comparison, and all runs are in the same schema.
I suspect the query underlying the matviews is less deterministic than you think it is. I did not study that query in any detail, but just from a quick eyeball: the array_agg() calls with no attempt to enforce a particular aggregation order are concerning, and so is grouping by a citext column (where you'll get some case-folding of a common value, but who knows which).
Also not having looked at the query in detail -- but are there concurrent changes in the database?
Yes, the database is in production so changes do occur, however the data involved for this particular customer and the tables involved in this query is not changing while we've looked into the case these days. It's SELECTED quite a bit though.
Because since you're creating your transaction in READ COMMITTED, other transactions finishing in between your two REFRESH commands can alter the data. To make sure that's not what's happening, you may want to try doing the same thing with a BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE instead, and see if the problem still occurs.
Thanks for the suggestion, but the issue remains. The results are the same (that is, invalid for the one matview and correct for new matviews or manual queries) whether I run the queries in the same transaction or separate transactions. After many many many attempts. I'm quite baffled, really...