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 ?