Обсуждение: 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
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

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.