RE: Mat Views and Conflicts

> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: Monday, February 19, 2024 7:36 PM
> To: Zahir Lalani <ZahirLalani@oliver.agency>; pgsql-
> general@lists.postgresql.org
> Subject: Re: Mat Views and Conflicts
> 
> On 2/19/24 09:52, Zahir Lalani wrote:
> > Hi All
> >
> > My understanding and hope was that Mat Views cache their data and that
> > is how they are so fast. But we are experience “cancelling statement
> > due to conflict with recovery” errors on MV’s with large data sets and
> > I thought that this could only happen if we ran the underlying query,
> > not the Mat View?
> 
> 1) When you say '... ran the underlying query ...' are you referring to:
> 
> REFRESH MATERIALIZED VIEW
> https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html
> 
> 2) What is the complete error message from the Postgres log?
> 
> >
> > Have I got it wrong??
> >
> > Z
> >
> 
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

To clarify:

We are not running the REFRESH. We are only running the materialised view. We used to run the non-view query directly
andalways suffered from the conflict with recovery error due to data changes on the replica server during the query
run.We increased the max_standby_streaming_delay quite a lot, but these are very heavy queries which take a long time.
Sothat is when we decided to convert to an MV. We get the conflict a lot less, but still enough to be annoying. This
suggeststhat not all the MV data is cached and it still queries the source tables in some way?
 

Z