From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Zahir Lalani <ZahirLalani@oliver.agency>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Mat Views and Conflicts
Date: Tue, 20 Feb 2024 08:10:12 -0800
Message-ID: <b79d1e63-b770-4d8d-984b-77fc3d9c84e7@aklaver.com> (raw)
In-Reply-To: <AS8P251MB01207A05FECF749D81ABC5DDA7502@AS8P251MB0120.EURP251.PROD.OUTLOOK.COM>
References: <AS8P251MB012075726CD81F97C586842CA7512@AS8P251MB0120.EURP251.PROD.OUTLOOK.COM>
<c02bdeda-9ffb-42a5-8b6d-c13b9380f0fe@aklaver.com>
<AS8P251MB01207A05FECF749D81ABC5DDA7502@AS8P251MB0120.EURP251.PROD.OUTLOOK.COM>
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.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 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.klaver@aklaver.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, ZahirLalani@oliver.agency
Subject: Re: Mat Views and Conflicts
In-Reply-To: <b79d1e63-b770-4d8d-984b-77fc3d9c84e7@aklaver.com>
References: <AS8P251MB012075726CD81F97C586842CA7512@AS8P251MB0120.EURP251.PROD.OUTLOOK.COM> <c02bdeda-9ffb-42a5-8b6d-c13b9380f0fe@aklaver.com> <AS8P251MB01207A05FECF749D81ABC5DDA7502@AS8P251MB0120.EURP251.PROD.OUTLOOK.COM> <b79d1e63-b770-4d8d-984b-77fc3d9c84e7@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox