Re: Different results from identical matviews

On Thu, Jul 2, 2020 at 12:12 PM Magnus Hagander <magnus(at)hagander(dot)net> wrote:

>
> 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?
>

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

Best,
-- a.

>