Re: BUG #7546: Backups on hot standby cancelled despite hot_standby_feedback=on

On Wednesday, September 19, 2012 10:06:03 AM Stuart Bishop wrote:
> On Wed, Sep 19, 2012 at 5:45 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> > On Mon, Sep 17, 2012 at 7:28 PM, <stuart(at)stuartbishop(dot)net> wrote:
> >> The following bug has been logged on the website:
> >>
> >> Bug reference: 7546
> >> Logged by: Stuart Bishop
> >> Email address: stuart(at)stuartbishop(dot)net
> >> PostgreSQL version: 9.1.5
> >> Operating system: Ubuntu 12.10
> >> Description:
> >>
> >> I have a primary and a hot standby using streaming replication. The hot
> >> standby specifies 'hot_standby_feedback=on' with other replication
> >> settings set to default.
> >>
> >> If a vacuum occurs on the primary while pg_dump is dumping a large
> >> table, the pg_dump is cancelled, usually with the following error:
> >>
> >> ERROR: canceling statement due to conflict with recovery
> >> DETAIL: User was holding shared buffer pin for too long.
> >
> > Maybe I'm missing something, but ISTM that hot_standby_feedback doesn't
> > eliminate the query cancels caused by buffer pin lock which you
> > encountered. It eliminates only the query cancels caused by cleanup of
> > rows. So you might need to set max_standby_streaming_delay to -1, to
> > avoid query cancels.
I think the above is just a case of the backend waiting a long time to send
data out due to your rate limiting. Why it does that holding a buffer pin may
be worth investigating...

> I have also seen the following (but only on production, not with my test
> case):
>
> ERROR: canceling statement due to conflict with recovery
> DETAIL: User was holding a relation lock for too long.
>
> If you are correct, it seems impossible with 9.1 to have reliable long
> transactions on a hot standby without allowing the hot standby to lag
> behind by the length of the longest transaction.
Uh. I don't think thats a valid chain of thought, especially when it comes due
to conflicts on relation locks. Those primarily (only?) happen if you do
perform an action on the primary that requires an exlusive lock. Like ALTER
TABLE, TRUNCATE, CLUSTER, LOCK TABLE, ... There is not really much the standby
can do if gets a request for an exlusive lock other than either cancelling
everything that blocks acquiring such a lock or waiting.

Is it possible that you perform such commands on tables used on the standby?

> I was hoping when the docs state "this parameter can be used to
> eliminate query cancels caused by cleanup records" that it would cover
> all the background maintenance.
Relation lock conflicts shouldn't be caused by background maintenance.

Greetings,

Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services