BUG #15169: create index CONCURRENTLY conflict with other table's COPY

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: digoal(at)126(dot)com
Subject: BUG #15169: create index CONCURRENTLY conflict with other table's COPY
Date: 2018-04-24 12:29:47
Message-ID: 152457298745.19804.5702434604866048036@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15169
Logged by: Zhou Digoal
Email address: digoal(at)126(dot)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)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Sergei Kornilov 2018-04-24 12:38:16 Re: BUG #15169: create index CONCURRENTLY conflict with other table's COPY
Previous Message Kyotaro HORIGUCHI 2018-04-24 10:57:12 Re: [HACKERS] [BUGS] Bug in Physical Replication Slots (at least 9.5)?