MIME-Version: 1.0
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>
In-Reply-To: <01020195ddb28a27-4a576e04-8cd7-4a0b-abc7-acb901700ee7-000000@eu-west-1.amazonses.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Tue, 1 Apr 2025 09:28:00 +1300
Message-ID: <CAApHDvpTGLFvnxrraLPHy-izdeu80+_f4rLFCUcpsTeh0nh=sw@mail.gmail.com>
Subject: Re: Querying one partition in a function takes locks on all partitions
To: Evgeny Morozov <postgresql4@realityexists.net>
Cc: PostgreSQL General <pgsql-general@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvpTGLFvnxrraLPHy-izdeu80%2B_f4rLFCUcpsTeh0nh%3Dsw%40mail.gmail.com>
Precedence: bulk

On Sat, 29 Mar 2025 at 06:00, Evgeny Morozov
<postgresql4@realityexists.net> 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