On 11/10/24 11:52, user wrote:
Thank you for an answer!
So reparenting of a constraint required that additional lock.
I made some measurements and can see that even that reparenting (and
additional lock) is required, the time it takes to make the attach is
smaller than when the foreign constraint hasn't been created beforehand.
So, to summarise, there is a tradeoff.
1. Create constraint before attach, but during attach additional tables
will be locked with AccessExculive. The time of an attach will be
minimal (for large tables it is still tens of ms in our db) but there
is a higher chance of deadlocks (as more tables locked with restrictive
locks)
2. Just proceed with attach. The constraint will be created because the
parent table has the constraint in its definition. Because no
reparenting is required, no additional exclusive lock is held. But this
process will take more time to finish as a constraint is created from
scratch.
Are these the only options?
Basically I want to add partitions dynamically to db while app is
running. I want to minimise the duration of "attach" command but also
the amount of locks held on several tables at once (to avoid deadlocks).
Once again, thanks for an answer. It is now clear to me why such
behaviour occurs.
Just to be clear:
1) I had nothing to do with writing this code.
2) I am not a C programmer, so what you got was my creative
interpretation of what I think is going on.
3) Because of 1 & 2, this needs further analysis by someone or someones
more knowledgeable.
Regards
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
