public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: Evgeny Morozov <[email protected]>
Cc: PostgreSQL General <[email protected]>
Subject: Re: Querying one partition in a function takes locks on all partitions
Date: Tue, 1 Apr 2025 09:28:00 +1300
Message-ID: <CAApHDvpTGLFvnxrraLPHy-izdeu80+_f4rLFCUcpsTeh0nh=sw@mail.gmail.com> (raw)
In-Reply-To: <01020195ddb28a27-4a576e04-8cd7-4a0b-abc7-acb901700ee7-000000@eu-west-1.amazonses.com>
References: <01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-000000@eu-west-1.amazonses.com>
	<CAApHDvrW2jWNHnYWRZR9cJLKFD97TPMgoXjJUA+U8nBJDzwNhw@mail.gmail.com>
	<01020195ddb28a27-4a576e04-8cd7-4a0b-abc7-acb901700ee7-000000@eu-west-1.amazonses.com>

On Sat, 29 Mar 2025 at 06:00, Evgeny Morozov
<[email protected]> wrote:
>
> On 23/03/2025 2:35 pm, David Rowley wrote:
> >> alter table entity_2 add column new_column text;
> > Is this just an example command?  You can't add a column to a
> > partition directly.
>
> Yes, it was just the simplest way I could think of to take an exclusive
> lock. But on this note: I guess it takes a lock before checking that the
> table is partition because it might otherwise get attached/detached as a
> partition between doing the check and taking a lock, but it would be
> nice if it took a shared lock to do all checks, then upgraded to an
> exclusive lock to actually do the work. Then the user would not have to
> wait for an exclusive lock only for the command to fail.

Unfortunately, that's probably just swapping one problem for another.
Once you have 2 sessions following such a pattern of locking, you're
prone to unnecessary deadlocking.

For example:

-- session1
begin;
lock table t in access share mode; -- gets lock

-- session2
begin;
lock table t in access share mode; -- gets lock
lock table t in access exclusive mode; -- waits

-- session1
lock table t in access exclusive mode; -- deadlock

If you don't bother with the access share lock, there's no deadlock.

David

view thread (3+ messages)

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]
  Subject: Re: Querying one partition in a function takes locks on all partitions
  In-Reply-To: <CAApHDvpTGLFvnxrraLPHy-izdeu80+_f4rLFCUcpsTeh0nh=sw@mail.gmail.com>

* 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