| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Anders Steinlein <anders(at)e5r(dot)no> |
| Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Different results from identical matviews |
| Date: | 2020-07-02 00:02:17 |
| Message-ID: | 815598.1593648137@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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).
regards, tom lane