partitioning

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