Re: Hot Standby Conflict on pg_attribute

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(at)gmail(dot)com> wrote:
>
>> On Fri, May 10, 2019 at 12:41 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Andres Freund <andres(at)anarazel(dot)de> 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!
> --
> Erik Jones
> mage2k(at)gmail(dot)com