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 高健 <luckyjack...@gmail.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 高健 <luckyjack...@gmail.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.ja...@gmail.com>
>>
>>> On Thu, Jun 20, 2013 at 1:27 AM, 高健 <luckyjack...@gmail.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
>>>
>>
>>
>
