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

Hello:

When I traced the source code of PG more deeply.

I can find the following:

VirtualTransactionId *

GetCurrentVirtualXIDs(TransactionId limitXmin, bool excludeXmin0,

bool allDbs, int excludeVacuum,

int *nvxids)

{

……

for (index = 0; index < arrayP->numProcs; index++)

{

volatile PGPROC *proc = arrayP->procs[index];

……

if (allDbs || proc->databaseId == MyDatabaseId)

{

/* Fetch xmin just once - might change on us */

TransactionId pxmin = proc->xmin;

if (excludeXmin0 && !TransactionIdIsValid(pxmin))

continue;

/*

* InvalidTransactionId precedes all other XIDs, so a proc that

* hasn't set xmin yet will not be rejected by this test.

*/

if (!TransactionIdIsValid(limitXmin) ||

TransactionIdPrecedesOrEquals(pxmin, limitXmin))

{

VirtualTransactionId vxid;

GET_VXID_FROM_PGPROC(vxid, *proc);

if (VirtualTransactionIdIsValid(vxid))

vxids[count++] = vxid;

}

}

}

LWLockRelease(ProcArrayLock);

*nvxids = count;

return vxids;

}

For my first test program, when <if (allDbs || proc->databaseId ==
MyDatabaseId) > condition is met,

The pxmin is 0, so <(excludeXmin0 && !TransactionIdIsValid(pxmin))> is met,
there Is no chance to make count++.

For my second test program, when <if (allDbs || proc->databaseId ==
MyDatabaseId) > condition is met,

The pxmin is not 0, so <(excludeXmin0 && !TransactionIdIsValid(pxmin))> is
not met, The vxids[count++] = vxid is executed.

The pxmin is from proc->xmin.

And I heard that when a process is working, it get transaction id from
system, then use it as xmin when inserting a record.

Why the proc->xmin can be 0 ? Is it a bug?

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

> 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
>>>
>>
>>
>