Thread: BUG #15169: create index CONCURRENTLY conflict with other table's COPY
BUG #15169: create index CONCURRENTLY conflict with other table's COPY
From
PG Bug reporting form
Date:
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)
Re: BUG #15169: create index CONCURRENTLY conflict with other table's COPY
From
Sergei Kornilov
Date:
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 potentiallymodify 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) predatingthe 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
Re: BUG #15169: create index CONCURRENTLY conflict with other table's COPY
From
"David G. Johnston"
Date:
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 tablea , 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 concurrenttransactions from operating at the same time. With concurrently those other transactions can continue to workand thus the concurrent indexing performed by CREATE INDEX cannot know it is finished while other transactions hold writelocks on the affected table/index. Instead it waits "to finish" until those transactions finish. The concurrent indexcreation has already started and is in progress - there just isn't any communication to the client as to progress. David J.
Re:Re: BUG #15169: create index CONCURRENTLY conflict with other table's COPY
From
Andres Freund
Date:
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).
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
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.