Postgres Pro
Downloads
Home   >   mailing lists

Re: Tow kinds of different result while using create index concurrently - Mailing list pgsql-general

From 高健
Subject Re: Tow kinds of different result while using create index concurrently
Date
Msg-id CAL454F2QMxYhMrCLux1zbtUZP1UswURvLb_cDFN=NoP8AGjbTA@mail.gmail.com
Whole thread Raw
In response to Re: Tow kinds of different result while using create index concurrently  (Jeff Janes <jeff.janes@gmail.com>)
Responses Re: Tow kinds of different result while using create index concurrently  (高健 <luckyjackgao@gmail.com>)
List pgsql-general
Tree view
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.janes@gmail.com>
On Thu, Jun 20, 2013 at 1:27 AM, 高健 <luckyjackgao@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

pgsql-general by date:

Previous
From: James Sewell
Date:
Subject: Re: Snapshot backups
Next
From: Melvin Call
Date:
Subject: Circular references