Postgresql -附加新分区时CHECK约束不阻止ACCESS EXCLUSIVE锁和表扫描

disbfnqx  于 2023-01-17  发布在  PostgreSQL
关注(0)|答案(1)|浏览(113)

我在运行postgresql13.
postgres doc文档的以下部分指出,我应该能够避免扫描和ACCESS EXCLUSIVE锁来验证分区约束。
在运行ATTACH PARTITION命令之前,建议在要附加的表上创建一个CHECK约束条件,该约束条件与预期的分区约束条件相匹配,如上所述。这样,系统就可以跳过验证隐式分区约束条件所需的扫描。如果没有CHECK约束条件,将扫描该表以验证分区约束,同时在该分区上持有ACCESSEXCLUSIVE锁。
但是,当我创建一个带有检查约束的新分区,向其中插入数据,然后附加它时,在扫描表时会持有一个ACCESS EXCLUSIVE锁。
分区表:

CREATE TABLE IF NOT EXISTS tasks
(
    task_time timestamp(6) with time zone not null,
    task_sp_time timestamp(6) with time zone,
    task_org_id text not null,
    build_id text,
    unit_id  text,
    unit_req numeric(12,2),
    ... 30 columns truncated ...,
    constraint tasks_pkey1
        primary key (task_org_id, task_time)
)
partition by RANGE(task_time);

task_timenot null并且类型为timestamp (6) with timezone

-- create new empty partition table
CREATE TABLE tasks_partitions.tasks_20230111
(LIKE tasks INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

-- add CHECK constraint on new partition 
ALTER TABLE tasks_partitions.tasks_20230111 ADD CONSTRAINT tmp_20230111
CHECK (task_time >= '2023-01-11 00:00:00+00' AND task_time <= '2023-01-11 23:59:59.999999+00');

-- select around 100 million rows into the new partition from an old default partition that has been detached.
INSERT INTO tasks_partitions.tasks_20230111
SELECT * FROM tasks_partitions.tasks_default_old where (task_time >= '2023-01-11 00:00:00+00' AND task_time <= '2023-01-11 23:59:59.999999+00');

-- attach partition
ALTER TABLE tasks ATTACH PARTITION tasks_partitions_tasks_20230111
FOR VALUES FROM ('2023-01-11 00:00:00+00') TO ('2023-01-11 23:59:59.999999+00')

附加分区仍然持有ACCESS EXLUSIVE锁,并且扫描整个表。
tasks表确实有一个默认分区,但我将其分离并重命名以解决另一个问题。我目前没有连接到tasks的默认分区。
当我从上面的例子中附加分区时,我看到新分区上有一个ACCESS EXCLUSIVE锁和一个看似随机的关系468140。在附加分区和锁就位时,我无法向tasks表中插入任何记录。
如果有用的话,我运行的查看锁的查询是:

SELECT a.datname,
       l.relation::regclass,
       l.transactionid,
       l.mode,
       l.GRANTED,
       l.usename,
       a.query,
       a.query_start,
       age(now(), a.query_start) AS "age",
       a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;
ulydmbyx

ulydmbyx1#

正在创建的检查约束与分区边界不匹配。the documentation中缺少此语句:
创建范围分区时,用FROM指定的下限是包含边界,而用TO指定的上限是排除边界。
因此,应将约束定义为

ALTER TABLE tasks_partitions.tasks_20230111 ADD
CHECK (task_time >= '2023-01-11 00:00:00+00' AND
       task_time <  '2023-01-12 00:00:00+00');

并将隔板与

ALTER TABLE tasks ATTACH PARTITION tasks_partitions_tasks_20230111
FOR VALUES FROM ('2023-01-11 00:00:00+00')
             TO ('2023-01-12 00:00:00+00');

相关问题