BUG #13878: Hot Standby Query Conflict documentation leaves out a potential source of conflict errors

From: wayne+psql(at)lewisclan(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13878: Hot Standby Query Conflict documentation leaves out a potential source of conflict errors
Date: 2016-01-21 05:05:37
Message-ID: 20160121050537.2738.12242@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13878
Logged by: Wayne Lewis
Email address: wayne+psql(at)lewisclan(dot)org
PostgreSQL version: 9.3.9
Operating system: Ubuntu 14.04
Description:

In 25.5.2 of the documentation the reasons for conflict errors are listed.

The first case is "Access Exclusive" which lists the ways an
AccessExclusiveLock can be taken on master which could conflict with queries
on the standby.

I think it should be noted there that even a lazy vacuum (and specifically
auto vacuum) can take AccessExclusiveLock when truncating free pages from a
relation, and that lock can produce a "User was holding a relation lock for
too long." error on the standby when the standby has long overlapping
queries on that relation.

I didn't realize until researching the source that auto vacuum can take an
AccessExclusiveLock in lazy_truncate_heap() and I suspect most users do not
realize that either.

While documentation to this effect would be very useful, is it possible to
go further and introduce a flag that disables truncation during lazy vacuum
whenever there is a standby? If there is no consequence to leaving those
pages I think users would prefer to control when an AccessExclusiveLock can
sneak into the WAL.

Thanks!
Wayne

Browse pgsql-bugs by date

  From Date Subject
Next Message Praveen Prakash 2016-01-21 05:20:20 Re: BUG #13873: Pg_upgrade problem from 9.4 to 9.5
Previous Message Smith, Travis 2016-01-20 16:12:00 Re: BUG #13875: Error explaining query