Re: canceling statement coming in slave instance

Hi Rohit,

It seems you are executing a non-correct SQL statement and when you correct
it and try to execute it again you will get this error.

You need to rollback/commit your transaction manually. After
rollback/commit try to execute the correct SQL- statement again.

Hope this helps.

<http://www.shreeyansh.com>

On Sat, Nov 24, 2018 at 4:35 PM Rohit Arora <arora(dot)leo9(at)gmail(dot)com> wrote:

> Dear List,
>
> Please note that we are working on PostgreSQL 9.4.19.
>
> Thanks
> Rohit Arora
>
>
> On Sat, Nov 24, 2018 at 4:29 PM Rohit Arora <arora(dot)leo9(at)gmail(dot)com> wrote:
>
>> Dear List,
>>
>> In few of our Slave PostgreSQL machines.
>>
>> I occasionally encounter below error.
>>
>> *ERROR: canceling statement due to conflict with recovery*
>> *DETAIL: User was holding a relation lock for too long*
>>
>> While investigating online i came to know that this issue can be handled
>> by below configuration parameters
>>
>> "max_standby_archive_delay "
>> "max_standby_streaming_delay"
>>
>> I have increased the value of both the parameters as per below.
>>
>> Original values:
>> "max_standby_archive_delay=30s"
>> "max_standby_streaming_delay=30s"
>>
>> Current values:
>> "max_standby_archive_delay=30s" ----> was increased it to 300s but we did
>> not get any benefit so we rollback it to original value
>> "max_standby_streaming_delay=300s"
>>
>> But still i occasionally encounter the mention issue.
>>
>> Please note that on Master node we have heavy write operations and these
>> Slave nodes are geographically distinct on a WAN connection.
>>
>> Thanks in Advance
>> Rohit Arora
>>
>>
>>