Re: Recovery conflict due to buffer pins

Hi Tom,

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)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.
>
> regards, tom lane
>