Tow kinds of different result while using create index concurrently

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:

My first program: transaction via ecpg(with host variable as where
condition), psql's "create index concurrently" succeeded.

My second program: transaction via ecpg, psql's "create index
concurrently" is blocked until ecpg program disconnect.

My third Test: transaction via psql, another psql's "create
index concurrently" succeeded.

My fourth Test: transaction via psql(with pg_sleep), another
psql's "create index concurrently" is blocked until psql transaction
done(commit).

I am using PostgreSQL9.1.2. And on PostgreSQL9.2.4, the result is same.

My data:

--------------------------------------------------------------------

[postgres(at)server bin]$ ./psql -U tester -d tester

psql (9.1.2)

Type "help" for help.

tester=> \d tab01;

Table "public.tab01"

Column | Type | Modifiers

--------+----------------------+-----------

id | integer |

cd | character varying(4) |

tester=> \d tab02;

Table "public.tab02"

Column | Type | Modifiers

--------+---------+-----------

id | integer |

value | integer |

tester=> select * from tab01;

id | cd

----+----

1 | 14

2 | 15

3 | 14

(3 rows)

tester=> select * from tab02;

id | value

----+-------

1 | 100

2 | 200

3 | 300

(3 rows)

tester=>

---------------------------------------------------------------------

My testing method for First program and Second program:

While my ecpg program is sleeping,

I open a terminal connect PG with psql,

then send "create index concurrently idx_tab02_id_new on tab02(id)"

For my first program, I can build index successfully.

For my second program, I can not build index, the sql statement is blocked
until ecpg program disconnect from PG.

My table tab01 and tab02 has no relationships.

And I don't think that my ecpg program will potentially use the index of
tab02.

In fact , If I look into the c program created by ecpg-- test02.c

I can find this:

------------------

{ ECPGdo(__LINE__, 0, 1, "db_conn", 0, ECPGst_normal, "select count ( *
) from tab01 where cd = $1 ",

ECPGt_char,(vcd),(long)4 + 1,(long)1,(4 + 1)*sizeof(char),

ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT,

ECPGt_int,&(vCount),(long)1,(long)1,sizeof(int),

ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);}

------------------

If I quoted the $1 manually and then compile it, then I can "create index
concurrently" while my ecpg program running:

------------------

{ ECPGdo(__LINE__, 0, 1, "db_conn", 0, ECPGst_normal, "select count ( *
) from tab01 where cd = '$1' ",

ECPGt_char,(vcd),(long)4 + 1,(long)1,(4 + 1)*sizeof(char),

ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT,

ECPGt_int,&(vCount),(long)1,(long)1,sizeof(int),

ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);}

------------------

Here is my program 's source:

***My first program:

-------------------------------------

[root(at)server soft]# cat ./test01/test01.pc

int main()

{

EXEC SQL BEGIN DECLARE SECTION;

int vCount;

char vcd[4+1];

EXEC SQL END DECLARE SECTION;

EXEC SQL CONNECT TO 'tester(at)127(dot)0(dot)0(dot)1:5432' AS db_conn

USER tester IDENTIFIED BY tester;

EXEC SQL AT db_conn SELECT COUNT(*)

INTO :vCount FROM tab01;

fprintf(stderr,"count is:%d\n",vCount);

fprintf(stderr,"Before disconnect,sleep for 500 seconds\n");

sleep(500);

EXEC SQL DISCONNECT db_conn;

fprintf(stderr,"After disconnect,sleep for 600 seconds\n");

sleep(600);

return 0;

}

[root(at)server soft]#

--------------------------------------------------------

***My Second Program:

--------------------------------------------------------

[root(at)server soft]# cat ./test02/test02.pc

int main()

{

EXEC SQL BEGIN DECLARE SECTION;

int vCount;

char vcd[4+1];

EXEC SQL END DECLARE SECTION;

EXEC SQL CONNECT TO 'tester(at)127(dot)0(dot)0(dot)1:5432' AS db_conn

USER tester IDENTIFIED BY tester;

char *pCd="14";

memset(vcd,'\0',5);

strncpy(vcd, pCd,4);

EXEC SQL AT db_conn SELECT COUNT(*)

INTO :vCount FROM tab01 WHERE cd = :vcd;

fprintf(stderr,"count is:%d\n",vCount);

fprintf(stderr,"Before disconnect,sleep for 500 seconds\n");

sleep(500);

EXEC SQL DISCONNECT db_conn;

fprintf(stderr,"After disconnect,sleep for 600 seconds\n");

sleep(600);

return 0;

}

[root(at)server soft]#

--------------------------------------------------------

And also, I can find another strange phenomenon via psql about "create
index concurrently":

This time I use two psql client:

***My Third Test:

----------------------------------------------------------------------

Client 1:

[postgres(at)server pgsql]$ ./bin/psql -d tester -U tester

psql (9.1.2)

Type "help" for help.

tester=> begin;

BEGIN

tester=> select * from tab01 where cd = '14';

id | cd

----+----

1 | 14

3 | 14

(2 rows)

tester=>

Client 2:

After Client 1 make a select,it does:

[postgres(at)server pgsql]$ ./bin/psql -d tester -U tester

psql (9.1.2)

Type "help" for help.

tester=> create index concurrently idx_tab02_id_new on tab02(id);

And then quickly succeeded.

----------------------------------------------------------------------

***My Fourth Test:

----------------------------------------------------------------------

Client 1:

[postgres(at)server pgsql]$ ./bin/psql -d tester -U tester

psql (9.1.2)

Type "help" for help.

tester=> begin;

BEGIN

tester=> select * from tab01 where cd = '14';

id | cd

----+----

1 | 14

3 | 14

(2 rows)

tester=> select pg_sleep(500);

pg_sleep

----------

(1 row)

tester=>

Client 2:

During client1's pg_sleep, or even after pg_sleep,

As far as client1 don’t finish transaction. The "create index concurrently
will not succeed":

[postgres(at)server pgsql]$ ./bin/psql -d tester -U tester

psql (9.1.2)

Type "help" for help.

tester=> create index concurrently idx_tab02_id_new on tab02(id);

----------------------------------------------------------------------

Thanks in advance.