Re: Tow kinds of different result while using create index concurrently

Hello:

Sorry for disturbing again.

I traced source code of PG, and found that:

When the 「create index concurrently 」statement is called,The following
calling relationship is there:

PortalRunMulti--> PortalRunUtility-->Standard_ProcessUtility-->DefineIndex

Here I omit some code of DefineIndex function in order to say my point
clearly:

{

…

old_snapshots = GetCurrentVirtualXIDs(snapshot->xmin, true, false,

PROC_IS_AUTOVACUUM | PROC_IN_VACUUM,
&n_old_snapshots);

for (i = 0; i < n_old_snapshots; i++)

{

…

if (VirtualTransactionIdIsValid(old_snapshots[i]))

VirtualXactLockTableWait(old_snapshots[i]);

}

…

}

For my first test program (mainly select * from tab02), After
GetCurrentVirtualXIDs function run,n_old_snapshots is 0 ,The for (i = 0; i
< n_old_snapshots; i++) loop will not be executed,

So index creation is not blocked and succeeded.

For my second test program(mainly select * from tab02 where cd=14), After
GetCurrentVirtualXIDs function run,n_old_snapshots is 1, The for (i = 0; i
< n_old_snapshots; i++) loop will be executed,

Then Because of VirtualXactLockTableWait(old_snapshots[i]) running, index
creation is blocked.

For the similar sql statement, the source code running logic differs, I
think that there might be something wrong in the source code.

2013/6/21 高健 <luckyjackgao(at)gmail(dot)com>

> Thanks Jeff
>
> But What I can't understand is:
> In My first test, the "create index concurrently" works well.
> In My second test, the "create index concurrently" can not work.
>
> The difference is only on ecpg's select statement :
> One use host variable of char (its value is of integer 14) in select
> statement,
> While the other is just a simple select.
>
> If the transaction will potentially the index, it should be same on my
> first test and second test.
>
> My customer want to use PG on their 7x24 environment, while rebuilding
> index periodically.
> If I can't do it on PG, it really confused me...
>
> sincerely yours
> Jian
>
>
> 2013/6/21 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
>
>> On Thu, Jun 20, 2013 at 1:27 AM, 高健 <luckyjackgao(at)gmail(dot)com> wrote:
>>
>>> Hello:
>>>
>>>
>>>
>>> I have question about PG's "create index concurrently". I think it is a
>>> bug perhaps.
>>>
>>>
>>>
>>> I make two tables tab01 and tab02, they have no relationships.
>>>
>>> I think "create index concurrently " on tab02 will not be influenced by
>>> transaction on tab01.
>>>
>>> But the result differs:
>>>
>>
>> This is expected. In order to not interfere with "normal" activity, a
>> concurrent index build has to volunteer to be blocked by such activity
>> instead. From the doc: "When this option is used, PostgreSQL must
>> perform two scans of the table, and in addition it must wait for all
>> existing transactions that could potentially use the index to terminate."
>>
>> Now in your case, perhaps the argument could be made that the transaction
>> hosting the 1st concurrent build could not potentially use the 2nd-building
>> index, but there is no convenient way for PostgreSQL to detect that fact.
>>
>> Cheers,
>>
>> Jeff
>>
>
>