On Thu, Jul 2, 2020 at 2:02 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Anders Steinlein <anders@xxxxxx> 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? 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.
