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

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@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@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@127.0.0.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@server soft]#

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

***My Second Program:

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

[root@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@127.0.0.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@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@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@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@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@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.