Re: Locks in creating a partition in CREATE TABLE vs ALTER TABLE

Right, I see I was unclear - I did know it was a new feature in Postgres
12, I just thought it applied to both cases, since the documentation makes
no such distinction. Wanted to make sure I wasn't missing something basic
before using the ALTER TABLE thing as a workaround.

Thanks!

On Tue, Mar 2, 2021 at 5:29 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
wrote:

> On 2021-Mar-02, Asaf Flescher wrote:
>
> > I'm not sure if this is a bug or I'm missing something regarding how
> > partitioning is supposed to work but I've noticed (in Postgres 12.6) that
> > if I have a partitioned table, and then try to add a partition to it via
> > CREATE TABLE ... PARTITION OF, the statement will grab an AccessExclusive
> > lock on the partitioned table. Meanwhile, if I create that same table
> > normally, then attach it to the partitioned table via ALTER table - no
> > AccessExclusive lock.
>
> It's a new feature in Postgres 12 actually -- we went great lengths to
> be able to do ALTER TABLE .. ATTACH PARTITION without a full
> AccessExclusive lock. However, it's just not possible to do the same
> for CREATE TABLE .. PARTITION AS.
>
> If you try the same in Postgres 11, you'll notice that both use an
> AccessExclusive lock.
>
> --
> Álvaro Herrera Valdivia, Chile
> Syntax error: function hell() needs an argument.
> Please choose what hell you want to involve.
>