Locks in creating a partition in CREATE TABLE vs ALTER TABLE
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
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.
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@alvh.no-ip.org>
wrote:
Show quoted text
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.