1

I'm running postgresql 13.

The below section of the postgres doc doc says I should be able to avoid a scan and ACCESS EXCLUSIVE lock to validate the partition constraint.

Before running the ATTACH PARTITION command, it is recommended to create a CHECK constraint on the table to be attached that matches the expected partition constraint, as illustrated above. That way, the system will be able to skip the scan which is otherwise needed to validate the implicit partition constraint. Without the CHECK constraint, the table will be scanned to validate the partition constraint while holding an ACCESS EXCLUSIVE lock on that partition.

But, when I create a new partition with a check constraint, insert data into it, and then attach it, an ACCESS EXCLUSIVE lock is held while the table is scanned.

The partitioned table:

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_time is not null and of type 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')

Attaching the partition still holds the ACCESS EXLUSIVE lock and the entire table is scanned.

The tasks table did have a default partition at one point, but I detached it and renamed it in order to resolve another issue. I currently do not have a default partition attached to tasks.

When I attach the partition from the example above, I see an ACCESS EXCLUSIVE lock on the new partition and a seemingly random relation, 468140. I cannot insert any records into the tasks table while the partition is being attached and the locks are in place.

If it helps, the query I run to see locks is:

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;
user797963
  • 2,907
  • 9
  • 47
  • 88

1 Answers1

1

The check constraint you are creating does not match the partition boundaries. You missed this statement from the documentation:

When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound.

So you should define the constraint as

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');

and attach the partition with

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');
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks, but this didn't seem to work. I've tried using just the date in my constraint as well. I also tried adding indexes on `tasks` for `(task_time)` and `(task_time, task_org_id)` thinking it may be an index issue but it still takes around 10 minutes to attach the new partition with an `ACCESS EXLUSIVE` lock. – user797963 Jan 12 '23 at 18:19
  • Yeah, I can't seem to get this to work even when creating new tables to test. – user797963 Jan 12 '23 at 21:41
  • @user797963 In my hands the scan is create the index. You need to pre-create the indexes, as well as the constraints. – jjanes Jan 12 '23 at 21:44
  • @jjanes you mean add the indexes on the partition to attach, correct? And you are referring to the `CHECK` constraint on the partition to attach? EDIT: ahh, I think I may have to attach the partition indexes to the `tasks` table. Any chance you have an example or can point me to some doc that explains what you mean? – user797963 Jan 12 '23 at 21:52
  • @jjanes Thank you! This worked. I had to create indexes and primary key constraints on the new partitions before attaching. I can now attach without holding the lock. I'm going to try a few different things and will update this answer with additional info and accept. – user797963 Jan 12 '23 at 22:41
  • If it were missing indexes, you didn't include that information in the question. The table there has only the primary key index, and the new partition copied that with `CREATE TABLE ... (LIKE ... INCLUDING CONSTRAINTS)`. So it must have been some other index. – Laurenz Albe Jan 13 '23 at 06:39
  • 1
    It actually was - ends up that datagrip wasn't showing me the full list of indexes when I was using it to generate DDL. I was able to trace the locks back to the missing indexes and resolve the problem. – user797963 Feb 12 '23 at 21:00