public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed
Date: Sun, 10 Nov 2024 14:16:46 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPDhG9YaMonzBGb5PTG7_a_n2-hfGvZfiLszEdvQ3oBFJx+5kA@mail.gmail.com>
References: <CAPDhG9aVAt6ed7Lji=G3XhcGRFz0FxHkGiuxmRu=hZgWW9D0OA@mail.gmail.com>
	<[email protected]>
	<CAPDhG9aZMS_nocLJ86bFHHqd1t=QOdztcR0Uy8BC5xpFrdes3A@mail.gmail.com>
	<CAPDhG9bvTyiCG_Z0pmuD_57z7xCbSn2ExkCvAbcgtkmMws9u8A@mail.gmail.com>
	<[email protected]>
	<CAPDhG9YM2B-P4ozEznSKXas+LGe6yDW4-QE=eKcxtvnis9eOvQ@mail.gmail.com>
	<CAPDhG9YqwVzXFFZ+19XggHYsYaRVROGxoTn=E6GS_J=t5tpAdQ@mail.gmail.com>
	<[email protected]>
	<CAPDhG9YaMonzBGb5PTG7_a_n2-hfGvZfiLszEdvQ3oBFJx+5kA@mail.gmail.com>

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
[email protected]

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox