Search Postgresql Archives

Re: Hot Standby Conflict on pg_attribute

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Just a quick footnote: If autovac truncations are frequently causing replica lag, and if this is a problem for you, IIUC one way you can stop autovac from doing the truncations even on older versions is setting old_snapshot_threshold to any value at all besides zero.  (On 12+ you can directly control the truncation behavior.)

-Jeremy

Sent from my TI-83

On May 10, 2019, at 12:46, Erik Jones <mage2k@xxxxxxxxx> wrote:

On Fri, May 10, 2019 at 12:41 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Andres Freund <andres@xxxxxxxxxxx> writes:
> On 2019-05-09 13:03:50 -0700, Erik Jones wrote:
>> The question then is: Why would these user queries be waiting on an
>> AccessShare lock on pg_attribute?

> Queries that access a table for the *first* time after DDL happened
> (including truncating the relation), need an AccessShareLock on
> pg_attribute (and pg_class, pg_index, ...) for a short time.

Also, it seems likely that what's really triggering the issue is
autovacuum on pg_attribute trying to truncate off empty pages
in pg_attribute (after a bunch of dead rows were generated there
by DDL activity).  That requires exclusive lock on pg_attribute,
which would propagate down to the standby.

                        regards, tom lane

Right, that part I understood after checking out pg_attribute's insert/delete counts in pg_stat_sys_tables before and after some REFRESH MATERIALIZED VIEW runs on an otherwise idle server.  With them running 2k+ refreshes per day autovac is regularly working on their catalog tables.

Thanks!
--

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux