Feedback-ID: i76984098:Fastmail
Message-ID: <19dbfe75-8b98-49af-bf03-0b7d72aa02e8@aklaver.com>
Date: Sun, 10 Nov 2024 14:16:46 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on
 different tables depending on how attaching is performed
To: user@pidu.dev
Cc: pgsql-general@lists.postgresql.org
References: <CAPDhG9aVAt6ed7Lji=G3XhcGRFz0FxHkGiuxmRu=hZgWW9D0OA@mail.gmail.com>
 <254c31c2-ff15-4720-a4fc-f0969d677d72@aklaver.com>
 <CAPDhG9aZMS_nocLJ86bFHHqd1t=QOdztcR0Uy8BC5xpFrdes3A@mail.gmail.com>
 <CAPDhG9bvTyiCG_Z0pmuD_57z7xCbSn2ExkCvAbcgtkmMws9u8A@mail.gmail.com>
 <4ebbbcf5-9c35-4711-bd7c-06a56a31aeff@aklaver.com>
 <CAPDhG9YM2B-P4ozEznSKXas+LGe6yDW4-QE=eKcxtvnis9eOvQ@mail.gmail.com>
 <CAPDhG9YqwVzXFFZ+19XggHYsYaRVROGxoTn=E6GS_J=t5tpAdQ@mail.gmail.com>
 <a38e4e1b-36ae-423a-8b06-0023a7e2630a@aklaver.com>
 <CAPDhG9YaMonzBGb5PTG7_a_n2-hfGvZfiLszEdvQ3oBFJx+5kA@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAPDhG9YaMonzBGb5PTG7_a_n2-hfGvZfiLszEdvQ3oBFJx+5kA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/19dbfe75-8b98-49af-bf03-0b7d72aa02e8%40aklaver.com>
Precedence: bulk

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