Re: Autovacuum stuck for hours, blocking queries

On Thursday, February 16, 2017, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Tim Bellis <Tim(dot)Bellis(at)metaswitch(dot)com <javascript:;>> writes:
> > Even though this is a read only query, is it also expected to be blocked
> behind the vacuum? Is there a way of getting indexes for a table which
> won't be blocked behind a vacuum?
>
> It's not the vacuum that's blocking your read-only queries. It's the
> ALTER TABLE, which needs an exclusive lock in order to alter the table's
> schema. The ALTER is queued waiting for the vacuum to finish, and lesser
> lock requests queue up behind it. We could let the non-exclusive lock
> requests go ahead of the ALTER, but that would create a severe risk of the
> ALTER *never* getting to run.
>
> I'd kill the ALTER and figure on trying again after the vacuum is done.
>
>
I've been drilled by this and similar lock stacking issues enough times to
make me near 100% sure deferring the ALTER would be the better choice

merlin