CREATE INDEX CONCURRENTLY is not supported in this fix due to complexity of multiple commits in the same transaction. In this simple example, we’ll just use the inspect module to view the columns and verify our table was successfully created: Worst case, some nodes would have the indexes created and some, not, but this won't affect database operations. > > > - toast relations are reindexed non-concurrently when table reindex is > done > > and that this table has toast relations > Why that restriction? The thing is: CREATE INDEX is a single transaction – CREATE INDEX CONCURRENTLY is not – and therefore it cannot be run inside a normal transaction block. Recreate all indexes within the current database. Indexes on user tables are not processed. to run your migration without a transaction: class AddIndexOnBatchIdToFundTrades < ActiveRecord::Migration[5.0] disable_ddl_transaction! That might be added later.) > > - REINDEX CONCURRENTLY cannot run inside a transaction block. block cannot run yet, as it is dependent on one or more blocks to complete; (ii) enqueued , meaning that any thread that beco mes idle can steal and run it; (iii) running , at some thread T i . Example: postgres=> begin; BEGIN postgres=> CREATE RESOURCE QUEUE test_q WITH (ACTIVE_STATEMENTS=3, PRIORITY=MAX); ERROR: CREATE RESOURCE QUEUE cannot run inside a transaction block > This is the state of the current version of the patch. Indexes on shared system catalogs are included. concurrent transaction log activity while database system is shutting down ... @1@ cannot run inside a transaction block ... cannot create indexes on temporary tables of other sessions substituting access method "gist" for obsolete method "rtree" Recreate all indexes on system catalogs within the current database. There is a way to avoid the write-lock though. Some commands like VACUUM, CREATE INDEX CONCURRENTLY or CREATE DATABASE cannot run inside a transaction block, so they are not allowed in functions. RuntimeError: ERROR C25001 MVACUUM cannot run inside a transaction block Fxact. c L2649 RPreventTransactionChain: VACUUM ... {CREATE INDEX CONCURRENTLY index_reservations_subscription_id ON reservations (subscription_id);} end def down execute %{DROP INDEX index_reservations_subscription_id} end end. That's because any migration by default is executed inside a transaction. With the engine created, we now need to use the .create_all() method of our metadata object and pass the engine connection to it, which will automatically cause SQLAlchemy to generate our table for us, as seen above.. With that complete, we can use the table as we see fit. It raises an exception "CREATE INDEX CONCURRENTLY cannot run inside a transaction block". This form of REINDEX cannot be executed inside a transaction block. Thankfully there is a way to pass it through - use disable_ddl_transaction! Well known fact is that PostgreSQL and many other RDBMS lock write access on the table while the index is being created. Starting in MongoDB 4.4, you can create collections in transactions … SYSTEM. You can create the index concurrently. I attempted to create the index in various ways with transactions disabled but could not find a way. Write operations that result in document inserts (e.g. You cannot create a resource queue within a transaction block. In MongoDB 4.2 and earlier, you cannot create collections in transactions. Indexes on shared system catalogs are also processed. And not what the final version should do. (Neither in SQL procedures, yet, as of Postgres 11. insert or update operations with upsert: true) must be on existing collections if run inside transactions. It is not acceptable when your project is large enough to allow a downtime for such the small adjustment like a new index.