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.
