| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Tim Bellis <Tim(dot)Bellis(at)metaswitch(dot)com> |
| Cc: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
| Subject: | Re: Autovacuum stuck for hours, blocking queries |
| Date: | 2017-02-16 22:39:31 |
| Message-ID: | 14709.1487284771@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Tim Bellis <Tim(dot)Bellis(at)metaswitch(dot)com> 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.
Also you might want to look into how you got into a situation where
you have an anti-wraparound vacuum that's taking so long to run.
You didn't do something silly like disable autovacuum did you?
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Alvaro Herrera | 2017-02-16 22:49:32 | Re: Autovacuum stuck for hours, blocking queries |
| Previous Message | David Hinkle | 2017-02-16 20:22:29 | Re: Bad planning data resulting in OOM killing of postgres |