Re: [GENERAL] 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 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 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 高健
> 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 高健
>
>> 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
>>
>>> On Thu, Jun 20, 2013 at 1:27 AM, 高健 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
>>> t
Re: [GENERAL] 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 高健
> 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
>
>> On Thu, Jun 20, 2013 at 1:27 AM, 高健 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
>>
>
>
Re: [GENERAL] Tow kinds of different result while using create index concurrently
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 > On Thu, Jun 20, 2013 at 1:27 AM, 高健 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 >
Re: [GENERAL] Tow kinds of different result while using create index concurrently
On Thu, Jun 20, 2013 at 1:27 AM, 高健 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
