Re: Hot Standby Conflict on pg_attribute

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