BUG #15169: create index CONCURRENTLY conflict with other table's COPY
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)
Hello
This works as documented: https://www.postgresql.org/docs/current/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY
PostgreSQL must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate
Before each table scan, the index build must wait for existing transactions that have modified the table to terminate. After the second scan, the index build must wait for any transactions that have a snapshot (see Chapter 13) predating the second scan to terminate. Then finally the index can be marked ready for use, and the CREATE INDEX command terminates
So create index concurrently waiting your transaction.
regards, Sergei
On 2018-04-24 12:29:47 +0000, PG Bug reporting form wrote:
table a's copy will block table b's CONCURRENTLY index creating.
Yes, concurrently waits for other sessions to complete. I don't see a
bug here?
Regards,
Andres
On Tue, Apr 24, 2018 at 8:16 AM, Andres Freund <andres@anarazel.de> wrote:
On 2018-04-24 12:29:47 +0000, PG Bug reporting form wrote:
table a's copy will block table b's CONCURRENTLY index creating.
Yes, concurrently waits for other sessions to complete. I don't see a
bug here?
To be more specific a create index can stop processing as soon as its done
its own work because it prevented any concurrent transactions from
operating at the same time. With concurrently those other transactions can
continue to work and thus the concurrent indexing performed by CREATE INDEX
cannot know it is finished while other transactions hold write locks on the
affected table/index. Instead it waits "to finish" until those
transactions finish. The concurrent index creation has already started and
is in progress - there just isn't any communication to the client as to
progress.
David J.
but, there is no other session get any level lock with table a(creating index.). so if concurrenty create index with table a , at the end of the work, we can get a lock for table a and end the creating , like pg_repack?
在 2018-04-24 23:29:52，"David G. Johnston" <david.g.johnston@gmail.com> 写道：
On Tue, Apr 24, 2018 at 8:16 AM, Andres Freund <andres@anarazel.de> wrote:
On 2018-04-24 12:29:47 +0000, PG Bug reporting form wrote:
table a's copy will block table b's CONCURRENTLY index creating.
Yes, concurrently waits for other sessions to complete. I don't see a
bug here?
To be more specific a create index can stop processing as soon as its done its own work because it prevented any concurrent transactions from operating at the same time. With concurrently those other transactions can continue to work and thus the concurrent indexing performed by CREATE INDEX cannot know it is finished while other transactions hold write locks on the affected table/index. Instead it waits "to finish" until those transactions finish. The concurrent index creation has already started and is in progress - there just isn't any communication to the client as to progress.
David J.
Still not a bug. And no we can't, because concurrent session with older snapshot can subsequently use the index.
If you have a concrete improvement proposal, please start a thread in a suitable list (general or hackers).
On April 24, 2018 6:14:37 PM MDT, "德哥" <digoal@126.com> wrote:
but, there is no other session get any level lock with table a(creating
index.). so if concurrenty create index with table a , at the end of
the work, we can get a lock for table a and end the creating , like
pg_repack?在 2018-04-24 23:29:52，"David G. Johnston" <david.g.johnston@gmail.com>
写道：On Tue, Apr 24, 2018 at 8:16 AM, Andres Freund <andres@anarazel.de>
wrote:On 2018-04-24 12:29:47 +0000, PG Bug reporting form wrote:
table a's copy will block table b's CONCURRENTLY index creating.
Yes, concurrently waits for other sessions to complete. I don't see a
bug here?
To be more specific a create index can stop processing as soon as its
done its own work because it prevented any concurrent transactions from
operating at the same time. With concurrently those other transactions
can continue to work and thus the concurrent indexing performed by
CREATE INDEX cannot know it is finished while other transactions hold
write locks on the affected table/index. Instead it waits "to finish"
until those transactions finish. The concurrent index creation has
already started and is in progress - there just isn't any communication
to the client as to progress.David J.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.