partitioning

[Torsten Förtsch <tfoertsch123@xxxxxxxxx>]

Hi,

I have an old, several TB table. That table has a child table (table inheritance) which is much smaller. Each row represents a certain process. The original idea was while the process is in progress it is stored in the small table. Once it has reached its final state, it is moved to the big one.

Now I want to convert this to the declarative partitioning scheme and in that process introduce a new partition for finished processes.

The fact that a process is finished is represented by the boolean column is_sold. The table has also a timestamp column called purchase_time. The new partitioning should be as follows

CREATE TABLE ... (...)
PARTITION BY RANGE (is_sold, purchase_time);

Then I want to reattach the original small table with the unfinished processes like so:

ALTER TABLE ... ATTACH PARTITION original_small_table
  FOR VALUES FROM (false, '-infinity') TO (false, 'infinity');

Reattach the big table like so:

ALTER TABLE ... ATTACH PARTITION original_big_table
  FOR VALUES FROM (true, '-infinity') TO (true, 'tomorrow');

And create a new default partition for the rest, the newly finished processes:

CREATE TABLE ... PARTITION OF ... DEFAULT;

Both is_sold and purchase_time have a NOT NULL constraint. The small table also has a check constraint CHECK(NOT is_sold).

Now, the documentation (https://www.postgresql.org/docs/14/ddl-partitioning.html) says, if the table that's going to be attached as a partition has a constraint that ensures the partition boundaries, the check while attaching it can be avoided:

> 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.

So, initially I thought that check constraint should be enough. But it was not.

Then I added this constraint to the small table:

ALTER TABLE original_small_table
ADD CONSTRAINT partition_boundaries
CHECK((false, '-infinity')<=(is_sold, purchase_time)
  AND (is_sold, purchase_time)<(false, 'infinity'))
  NOT VALID;

And validated it.

When this is being attached as a partition, I still can see the process is reading the entire table.

What am I missing? What should the check constraint look like in my case to match the partition constraint?

This is PG 14.

Thanks,

Torsten