Re: concurrent index builds unneeded lock?

On Sat, Jul 11, 2009 at 6:17 AM, Theo Schlossnagle<jesus(at)omniti(dot)com> wrote:
>
>
> On Jul 11, 2009, at 12:12 AM, Tom Lane wrote:
>
>> Theo Schlossnagle <jesus(at)omniti(dot)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.

--
greg
http://mit.edu/~gsstark/resume.pdf