Re: concurrent index builds unneeded lock?

On Jul 11, 2009, at 6:50 AM, Greg Stark wrote:

> On Sat, Jul 11, 2009 at 6:17 AM, Theo Schlossnagle<jesus@omniti.com>  
> wrote:
>>
>>
>> On Jul 11, 2009, at 12:12 AM, Tom Lane wrote:
>>
>>> Theo Schlossnagle <jesus@omniti.com> writes:
>>>>
>>>> I would think it would be txns that would be reading that table,  
>>>> but
>>>> I'm thinking it is a bit to aggressive.  Am I reading the code  
>>>> wrong
>>>> there?  I'm thinking it should be more selective about vxids it
>>>> chooses to block on.  I'd expect it to block on vxids touching the
>>>> same table only.
>>>
>>> There is no way to know whether a currently active vxid will try  
>>> to look
>>> at the other table later.  We can not just ignore this case...
>>>
>>>                        regards, tom lane
>>
>>
>> Can't you know "that" if the other active query in question is a  
>> concurrent
>> index build?
>
> I think so.
>
> Hm. Actually maybe not. What if the index is an expression index and
> the expression includes a function which does an SQL operation? I'm
> not sure how realistic that is since to be a danger that SQL operation
> would have to be an insert, update, or delete which is not just
> bending the rules.
>
> The real problem is that we only added the ability to distinguish
> vacuums relatively recently and adding more special cases of
> transactions which can be ignored for one purpose or another seems
> like a lot of corner cases to worry about.
>
> I wonder whether an earlier more general proposal could have some
> leverage here though: some way to indicate that the transaction has
> taken all the locks it plans to take already. This was originally
> proposed as a way for vacuum to know it can ignore the snapshots of a
> transaction since it isn't going to access the table being vacuumed.
>
> In this case the concurrent index build could mark itself as having
> taken all the locks it plans to take and other concurrent index builds
> could ignore it. They could also ignore any transactions which have
> that flag set through any other mechanisms we might add such as a
> manual SQL command.


While I see that feature being extremely hard for users to leverage  
via SQL, it seems like a very simplistic and useful internal control.

When acquire locks, we simply check if we've have the future-locking  
bit flipped and abort the transaction if it is.  It seems really  
safe.  I'd imagine the various places we try to use that we'll be  
reminded of the incidental locks we'll be needing to grab.  But, after  
looking at the concurrent index code, it seems that it would solve my  
issue at least.  Also much more elegant that adding more exceptions.

--
Theo Schlossnagle
http://omniti.com/is/theo-schlossnagle
p: +1.443.325.1357 x201   f: +1.410.872.4911