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(dot)klaver(at)aklaver(dot)com