We have set max_standby_streaming_delay to 0 because we have a synchronous standby with synchronous_commit as 'remote_apply'. We want to make sure that queries are cancelled so that it doesn't affect the primary.
Is there any other way to resolve this apart from setting max_standby_streaming_delay to a value greater than zero?
Thanks,
Nikhil
On Tue, Jun 14, 2022 at 12:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Nikhil Shetty <nikhil.dba04@gmail.com> writes: > 2022-06-12 04:30:01 UTC [3324]: [3-1] > user=<user>,db=postgres,app=<app>,client=127.0.0.1ERROR: canceling > statement due to *conflict with recovery* > 04:30:01 UTC [3324]: [4-1] > user=<user>,db=postgres,app=<app>,client=127.0.0.1DETAIL: User was holding > shared buffer pin for too long.
> We have set below parameters in standby > hot_standby_feedback = on > max_standby_streaming_delay = 0
> How can we avoid this error on standby?
Use a larger max_standby_streaming_delay. Setting it to zero means precisely that conflicting queries will be canceled immediately.