4 ms·
Concurrent index creation internally uses multiple transactions, so it can't itself be wrapped in a transaction. The docs explain the mechanics further: https:
by singron 2y ago
Concurrent index creation internally uses multiple transactions, so it can't itself be wrapped in a transaction.
The docs explain the mechanics further: https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY https://www.postgresql.org/docs/current/sql-createindex.html...
- zelphirkalt 2y agoThat immediately brings up the next question: Why are transactions not composable?
- londons_explore 2y agoIgnoring everything but the outer transaction would be technically correct - wonder why it isn't supported?
- deleted 2y ago[deleted]
- paulddraper 2y agoThey are? Your transaction isolation mode defines how they compose. Unless you are asking something different.
- jeltz 2y agoThey are. Just not in a way which would help. If a subtransaction commits the changes are still not visible to other connections until the top level transaction commits. The subtransactions are exposed to SQL as savepoints but my point still holds. I am not sure what would be needed to implement concurrent index build but subtransactions would not help.
- singron 2y agoTransactions cannot compose because nested transactions cannot simultaneously (1) ensure committed transactions are visible and (2) uncommitted transactions are not visible. If you don't care about visibility until the end and just want nested atomicity, you can use savepoints as a kind of nested transaction, but they won't be visible to other sessions until the top level transaction is committed. CREATE INDEX CONCURRENTLY has steps where it needs to make changes visible and wait until no transactions are open from before that point, so it can't use savepoints.