The following bug has been logged on the website:
Bug reference: 15169
Logged by: Zhou Digoal
Email address: digoal@126.com
PostgreSQL version: 10.3
Operating system: CentOS 7.x x64
Description:
table a's copy will block table b's CONCURRENTLY index creating.
postgres=# create table a(id int);
CREATE TABLE
postgres=# create table b(id int);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# copy a from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>>
postgres=# create index idx_b_1 on b (id);
CREATE INDEX
postgres=# create index CONCURRENTLY idx_b_2 on b (id);
hang
postgres=# select * from pg_locks where granted is not true;
locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction | pid |
mode | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------+---------+----------
virtualxid | | | | | 3/171 |
| | | | 61/53 | 18690 | ShareLock | f
| f
(1 row)
postgres=# select * from pg_locks where virtualxid='3/171';
locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction | pid |
mode | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------+---------+----------
virtualxid | | | | | 3/171 |
| | | | 61/53 | 18690 | ShareLock |
f | f
virtualxid | | | | | 3/171 |
| | | | 3/171 | 55384 | ExclusiveLock |
t | f
(2 rows)
postgres=# select * from pg_stat_activity where pid=55384;
datid | datname | pid | usesysid | usename | application_name |
client_addr | client_hostname | client_port | backend_start
| xact_start | query_start |
state_change
| wait_event_type | wait_event | state | backend_xid | backend_xmin |
query | backend_type
-------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------------------------
----+-----------------+------------+--------+-------------+--------------+--------------------+----------------
13220 | postgres | 55384 | 10 | postgres | psql |
| | -1 | 2018-04-24 14:56:47.972008+08 |
2018-04-24 20:21:48.355287+08 | 2018-04-24 20:21:51.625286+08 | 2018-04-24
20:21:51.625288
+08 | Client | ClientRead | active | | 36570 |
copy a from stdin; | client backend
(1 row)
