Create index concurrently blocked by other transactions in postgresql 9.6

Question

I'm trying to create indices through hikari by "create index concurrently" statement with postgresql 9.6 The create statement is blocked by another transaction which is working on another table and the transaction state is IIT(idle in transaction)

1. The code is creating the indices dynamically through hikari connection pool
2. There is only one connection pool for all the actions, such as select/create index and so on
3. The 2 SQLs are running in the same thread with different db connection with async mode
4. When using "create index" instead of "create index concurrently", all the things are OK
5. postgresql shows the "create index concurrently on B"(active) is blocked by "select * from A"(idle in transaction)
6. I tried to reproduce the issue through command line, all the things worked well, just open 2 window, execute "begin; select * from A;" in first window, and tried to execute "create index concurrently on B;" in second window, the index created as expected, no block happened(I checked that, the first one is in "IIT" state)
7. To use fetch_size on cursor, the select statement will disable autocommit when get connection from pool and set the value back with pool global settings by hikari itself, the default pool setting is autocommit=true
8. 2 statement are working on different tables, no relations between these 2 tables
9. When the "IIT" statement cancelled, the "create" statement continued to work as expected

wait_event_type |  pid  |        state        |    query                                                                                                                                                                              
Lock            | 25707 | active              | CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS "idx_tr-parameters__id_json" ON "tr-parameters" ((info->'_id') ASC)
                | 25701 | idle in transaction | SELECT t.info FROM "configuration-profiles" t

05-29 21:22:53.458 [vert.x-worker-thread-11] DEBUG com.calix.sxa.VertxPGVertice - SELECT t.info FROM "organizations" t HikariProxyConnection@379242839 wrapping org.postgresql.jdbc.PgConnection@645bae4d
05-29 21:22:53.529 [vert.x-worker-thread-11] DEBUG com.zaxxer.hikari.pool.PoolBase - hikari-cp-threads - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@645bae4d
05-29 21:22:53.533 [vert.x-worker-thread-11] DEBUG com.calix.sxa.VertxPGVertice - SELECT t.info FROM "configuration-profiles" t HikariProxyConnection@358392671 wrapping org.postgresql.jdbc.PgConnection@645bae4d
05-29 21:22:53.693 [vert.x-worker-thread-11] DEBUG com.calix.sxa.VertxPGVertice - SELECT t.info FROM "groups" t HikariProxyConnection@269112314 wrapping org.postgresql.jdbc.PgConnection@63822471
05-29 21:22:53.701 [vert.x-worker-thread-11] DEBUG com.zaxxer.hikari.pool.PoolBase - hikari-cp-threads - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@63822471
05-29 21:22:53.701 [vert.x-worker-thread-11] DEBUG com.calix.sxa.VertxPGVertice - SELECT t.info FROM "configuration-profiles" t WHERE COALESCE((t.info->'configurations'->'parameterValues')::jsonb ?? 'OUI_FilterList', false) = true HikariProxyConnection@1431456353 wrapping org.postgresql.jdbc.PgConnection@63822471
05-29 21:22:53.704 [vert.x-worker-thread-11] DEBUG com.zaxxer.hikari.pool.PoolBase - hikari-cp-threads - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@63822471
05-29 21:22:53.712 [vert.x-worker-thread-11] DEBUG com.calix.sxa.VertxPGVertice - CREATE INDEX CONCURRENTLY IF NOT EXISTS "idx_tr-parameters__id_json" ON "tr-parameters" ((info->>'timestamp') ASC) HikariProxyConnection@454316525 wrapping org.postgresql.jdbc.PgConnection@63822471

1. I don't know why the block happened since the tables are totally different and when testing manually with 2 command line windows, all the things worked well
2. How can I fix this issue? Any workaround on it?

Thanks for you kindly help

---

As mentioned by @jjanes, it's blocked by other transactions(transactions on the same table or any transaction with snapshot) during the 2 scans when building index concurrently

The official doc also mentioned, [1]: https://www.postgresql.org/docs/9.1/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

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

In my case,

wait_event_type |  pid  |        state        | backend_xid | backend_xmin | query
----------------+-------+---------------------+-------------+--------------+--------------------------------------------------
                |  5226 | idle in transaction |             |      7973432 | select * from "configuration-profiles"

The backend_xmin is 7973432 of IIT and then the "create index concurrently" is blocked by the IIT with snapshot

BTW, when using command line with isolation level "read committed", the "create index concurrently" is not blocked, but with Java code, the "create" action is also blocked with the same isolation level,

wait_event_type |  pid  |        state        | backend_xid | backend_xmin | query
----------------+-------+---------------------+-------------+--------------+--------------------------------------------------
                |  5226 | idle in transaction |             |      7973432 | select * from "configuration-profiles"
                |  5210 | idle in transaction |             |              | select * from "configuration-profiles";
                |  5455 | idle in transaction |             |      7973432 | declare cur cursor for select * from "configuration-profiles";

As showed above,

1. Using "select * from "configuration-profiles";" in command line, there is no backend_xmin since no cursor opened, all the records should be returned after executed the statement
2. Using "declare cur cursor for select * from "configuration-profiles";" in command line, backend_xmin has value since the cursor opened and waiting for query
3. Using "select * from "configuration-profiles"" through Java, backend_xmin also has value since the lib also uses cursor

Answer 1

PostgreSQL has no way of knowing that that other connection will not want to use the table the index is being built on at some point in the future of its snapshot. Just because it hasn't used the table yet doesn't mean it never will. The way to know that for sure is to wait for that transaction (or snapshot) to finish, which is what it does.

The 2 SQLs are running in the same thread with different db connection with async mode

Why is the other connection IIT? What is it waiting for? (It is waiting in your code, not in the database). Since it is async, it shouldn't be waiting on the CIC. Is it just waiting for you to issue a COMMIT? Since you turned off autocommit, it is your responsibility to issue COMMITs at the appropriate points. If you are using higher isolation levels, even SELECT only statements need to be committed.

I tried to reproduce the issue through command line, all the things worked well, just open 2 window, execute "begin; select * from A;" in first window, and tried to execute "create index concurrently on B;" in second window,

You can reproduce by changing the first one to begin isolation level repeatable read; select * from A;

Workarounds:

1. Don't leave things hanging around in an open transaction
2. Don't use higher isolation levels than you need to.
3. Don't use CIC.