Re: Different results from identical matviews

On Thu, Jul 2, 2020 at 2:02 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Anders Steinlein <anders(at)e5r(dot)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? 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.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>