Re: canceling statement coming in slave instance

[Shreeyansh Dba <shreeyansh2014@xxxxxxxxx>]

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.

On Sat, Nov 24, 2018 at 4:35 PM Rohit Arora <arora.leo9@xxxxxxxxx> 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.leo9@xxxxxxxxx> 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