Locks in creating a partition in CREATE TABLE vs ALTER TABLE

From: Asaf Flescher <asaf(at)armis(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Locks in creating a partition in CREATE TABLE vs ALTER TABLE
Date: 2021-03-02 20:18:35
Message-ID: CABNzHgnQ7q-EN_gc_GTKsGMksbxxD8=XT03rT2CRme=21KD_FQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

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. Short recreation -

In one session:
CREATE TABLE stuff (stuff_id int) PARTITION BY LIST (stuff_id);
BEGIN;
SELECT * FROM stuff;

Then in a second session:

CREATE TABLE stuff_1 PARTITION OF stuff FOR VALUES IN (1); (Will get stuck,
and a query on pg_locks will show an ungranted AccessExclusive lock).
CREATE TABLE stuff_1 (LIKE stuff); (Will work)
ALTER TABLE stuff ATTACH PARTITION stuff_1 FOR VALUES IN (1); (Will work)

Logically, the two approaches are doing the same thing, are they not? Or am
I missing something?

Would appreciate any advice here,
Asaf

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2021-03-02 20:19:00 Re: Localizing stored functions by replacing placeholders in their body
Previous Message Alexander Farber 2021-03-02 17:30:23 Re: Localizing stored functions by replacing placeholders in their body