4 ms·
Other long-running operations use transactions... Do the operation on a snapshot of the data, and then evaluate if it can be committed. Why isn't index crea
by londons_explore 2y ago
Other long-running operations use transactions... Do the operation on a snapshot of the data, and then evaluate if it can be committed.
Why isn't index creation done the same way?
- AmericanChopper 2y agoCREATE INDEX CONCURRENTLY is not a transactional statement. If you want to create an index inside a transaction it will block all writes to the table until the index is created, which can take a rather long time on large tables.
- deleted 2y ago[deleted]
- singron 2y agoConcurrent 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.
- paulddraper 2y agoThe entire point of CONCURRENTLY is to not be in a single transaction. So you can interleave writes and (partially complete) indexing. If you want transactional index creation, just omit CONCURRENTLY.