Re: Mat Views and Conflicts

On 2/20/24 04:20, Zahir Lalani wrote:

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

2) What is the complete error message from the Postgres log?

>
> Z

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com