Обсуждение: Can concurrent create index concurrently block each other?
One our customer complains that he spawned two `create index concurrently` for two different tables and both stuck in "waiting for old snapshots".
I wonder if two CIC can really block each other in `WaitForOlderSnapshots`?
I found the similar question in hacker archive:
https://www.postgresql.org/message-id/flat/MWHPR20MB1421AEC7CEC67B159AC188F6A19A0%40MWHPR20MB1421.namprd20.prod.outlook.com
but it is quite old (2016). Was the problem fixed since that time? And if not, why there it is not mentioned in CIC documentation that performing several CIC in parallel can cause "deadlock"?
Thanks in advance,
Konstantin
One our customer complains that he spawned two `create index concurrently` for two different tables and both stuck in "waiting for old snapshots".
I wonder if two CIC can really block each other in `WaitForOlderSnapshots`?
I found the similar question in hacker archive:https://www.postgresql.org/message-id/flat/MWHPR20MB1421AEC7CEC67B159AC188F6A19A0%40MWHPR20MB1421.namprd20.prod.outlook.com
but it is quite old (2016). Was the problem fixed since that time? And if not, why there it is not mentioned in CIC documentation that performing several CIC in parallel can cause "deadlock"?
Thanks in advance,
Konstantin
Avinash Vallarapu
Konstantin Knizhnik <knizhnik@garret.ru> writes: > One our customer complains that he spawned two `create index > concurrently` for two different tables and both stuck in"waiting for old > snapshots". > I wonder if two CIC can really block each other in `WaitForOlderSnapshots`? Since v14, we won't wait for another CIC unless it is processing a partial or expressional index. (According to the comments for WaitForOlderSnapshots, anyway.) What PG version is this, and what kind of indexes are being rebuilt? In any case, if they were blocking each other that would be reported as a deadlock, since they'd use VirtualXactLock() which relies on the heavyweight lock manager. What seems more likely is that your customer had some other old transaction sitting idle and blocking both of them. Looking into pg_locks would provide more definitive evidence about what they are waiting for. regards, tom lane
On 15/10/2023 10:59 pm, Tom Lane wrote: > Konstantin Knizhnik <knizhnik@garret.ru> writes: >> One our customer complains that he spawned two `create index >> concurrently` for two different tables and both stuck in"waiting for old >> snapshots". >> I wonder if two CIC can really block each other in `WaitForOlderSnapshots`? > Since v14, we won't wait for another CIC unless it is processing a > partial or expressional index. (According to the comments for > WaitForOlderSnapshots, anyway.) What PG version is this, and what > kind of indexes are being rebuilt? > > In any case, if they were blocking each other that would be reported > as a deadlock, since they'd use VirtualXactLock() which relies on > the heavyweight lock manager. What seems more likely is that your > customer had some other old transaction sitting idle and blocking both > of them. Looking into pg_locks would provide more definitive evidence > about what they are waiting for. Sorry, for false alarm. We have found long running truncation which actually blocks CIC in this case. I have asked this question because customer has wrote that there was no other long living active transactions, but it was not true.