> -----Original Message----- > From: Adrian Klaver <adrian.kla...@aklaver.com> > Sent: Monday, February 19, 2024 7:36 PM > To: Zahir Lalani <ZahirLalani@oliver.agency>; pgsql- > gene...@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.kla...@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 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
