Attaching a partition to Postgres partitioned table without Exclusive Access Lock

Viewed 118

We have a table that is partitioned on a bigint field.

Here is an example definition :

CREATE TABLE IF NOT EXISTS public.demo_part
(
    n bigint NOT NULL,
    CONSTRAINT demo_part_pkey PRIMARY KEY (n)
) PARTITION BY RANGE (n);

If we attach a default partition with :

CREATE TABLE public.demo_part_default PARTITION OF public.demo_part

Later partitions cannot be attached without acquiring an AccessExclusiveLock because the default partition must be scanned to ensure no conflicting data is in the default partition.

I tried adding CHECK constraints to both the partition to be added and the default partition without success.

CREATE TABLE IF NOT EXISTS demo_part_default PARTITION OF demo_part DEFAULT;
ALTER TABLE demo_part_default DROP CONSTRAINT IF EXISTS def_check;
ALTER TABLE demo_part_default ADD CONSTRAINT def_check CHECK (n >= 1000 AND n < 500000);
CREATE TABLE IF NOT EXISTS demo_part_1_10 (LIKE demo_part);
ALTER TABLE demo_part_1_10 DROP CONSTRAINT IF EXISTS n_1_10;
ALTER TABLE demo_part_1_10 ADD CONSTRAINT n_1_10 CHECK (n >= 1 AND n < 10);
ALTER TABLE demo_part ATTACH PARTITION demo_part_1_10 FOR VALUES FROM (1) TO (10);

The later ATTACH always creates an AccessExclusiveLock even if the constraints are mutually exclusive.

Is it possible to attach a partition without AccessExclusiveLock when a default partition is attached ?

0 Answers
Related