On Wed, Feb 22, 2017 at 3:19 PM, Jeff Janes <jeff.janes@xxxxxxxxx> wrote: > On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: >> On Thursday, February 16, 2017, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >>> >>> Tim Bellis <Tim.Bellis@xxxxxxxxxxxxxx> 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 > > This seems like a rather one-sided observation. How could you know how > often the unimplemented behavior also would have "drilled" you, since it is > unimplemented? Well, that I can't really say, but at least in my case ALTER TABLE in the face of concurrent application activity can wait but locking tables for reading for an indefinite period will rapidly destabilize the system. An example of this usage is replacing partitions on a warehouse table. About half of my P1s over the last 12 months ago are relating to locking problem of some kind. So I end up during workarounds such as issuing "LOCK...NOWAIT" in a sleep loop :( or application restructuring, especially minimizing use of TRUNCATE. I do think instrumentation around locking behaviors would be helpful. Allowing (optionally) waiters to leapfrog in if they can clear would be wonderful as would being able to specify maximum wait timeouts inside a transaction. FWIW, I'm not sure this behavior makes sense attached to LOCK, I'd rather see them attached generally to SET TRANSACTION -- my 0.02$ (talk is cheap, etc). merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
