On Wed, Feb 22, 2017 at 3:19 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Thursday, February 16, 2017, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>
>>> Tim Bellis <Tim.Bellis@metaswitch.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.
>>
>> 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