Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed

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@aklaver.com