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

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.