Re: Different results from identical matviews - Mailing list pgsql-general

From Magnus Hagander
Subject Re: Different results from identical matviews
Date
Msg-id CABUevEypYt-+JpH0QOK_waFpxYNTjgT3030x8USysaU1bV+zjA@mail.gmail.com
Whole thread Raw
In response to Re: Different results from identical matviews  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Different results from identical matviews
List pgsql-general


On Thu, Jul 2, 2020 at 2:02 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Anders Steinlein <anders@e5r.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.
 
--

pgsql-general by date:

Previous
From: Virendra Kumar
Date:
Subject: Re: Connecting Powerbuilder / EAserver to PostgreSQL server 11 via ODBC or JDBC
Next
From: Brajendra Pratap Singh
Date:
Subject: restore_command for postgresql streaming replication