RE: Mat Views and Conflicts

> -----Original Message-----
> From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> Sent: Monday, February 19, 2024 7:36 PM
> To: Zahir Lalani <ZahirLalani(at)oliver(dot)agency>; pgsql-
> general(at)lists(dot)postgresql(dot)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(dot)klaver(at)aklaver(dot)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 and always 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. So that is when we decided to convert to an MV. We get the conflict a lot less, but still enough to be annoying. This suggests that not all the MV data is cached and it still queries the source tables in some way?

Z