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)
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)? |