BUG #15169: create index CONCURRENTLY conflict with other table's COPY - Mailing list pgsql-bugs

From PG Bug reporting form
Subject BUG #15169: create index CONCURRENTLY conflict with other table's COPY
Date
Msg-id 152457298745.19804.5702434604866048036@wrigleys.postgresql.org
Whole thread Raw
Responses Re: BUG #15169: create index CONCURRENTLY conflict with other table's COPY
Re: BUG #15169: create index CONCURRENTLY conflict with othertable's COPY
List pgsql-bugs
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)


pgsql-bugs by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: BUG #15168: "pg_isready -d" effectively ignores given database name
Next
From: Sergei Kornilov
Date:
Subject: Re: BUG #15169: create index CONCURRENTLY conflict with other table's COPY