Re: Different results from identical matviews

[Anders Steinlein <anders@xxxxxx>]

On Thu, Jul 2, 2020 at 1:26 PM Anders Steinlein <anders@xxxxxx> wrote:

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).

Thanks for the tip, but I'm having a hard time thinking that's the case, seeing as I'm unable to trigger the wrong result no matter how hard I try with a new definition/manual query. I've introduced random ordering to the first CTE-clause (where the initial citext values comes from, and casing thus could differ in some order) which doesn't change the result.

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

Best,

-- a.