exclusion constraint miscalculation

[jw1u1]

On new partition sets without any "old" partitions, exclusion constraints are created on the current partition, regardless of the optimize_constraint setting.
This does happen, if an existing table is attached as default parittion.

v_child_stop is calculated like this:

fcdb=# select to_char('2025-02-01 00:00:00+01'::timestamptz - ('1 mon'::interval * (1 + 4 + 1) ), 'YYYYMMDD');
 to_char  
----------
 20240801

But there isn't any Partition for 20240801:

fcdb=# SELECT partition_schemaname, partition_tablename FROM partman.show_partitions('fc.aggregation_value', 'ASC');
 partition_schemaname |     partition_tablename     
----------------------+-----------------------------
 fc                   | aggregation_value_p20241001
 fc                   | aggregation_value_p20241101
 fc                   | aggregation_value_p20241201
 fc                   | aggregation_value_p20250101
 fc                   | aggregation_value_p20250201

The raise condition
IF v_row.partition_tablename = v_child_stop THEN does not match and exclusion constraints are created on the "current" partition aggregation_value_p20241001.

Proposed solution:
check, if v_child_stop is a partition of the parent_table before applying any constraints:
select true from partman.show_partitions('fc.aggregation_value') where partition_tablename='aggregation_value_p20240801';

[keithf4]

Confirmed this bug. Thanks for reporting!

[keithf4]

Thanks again for reporting this! Issue was just in the reapply_constraint_proc() that I found so far (where the v_stop_child variable was located). Were you having issues of this happening during normal maintenance as well?

I'm also working on clarifying the documentation on how to use this feature as well.

[jw1u1]

Hi Keith,
we have to set constraint_valid to false to prevent locking issues.
I cannot confirm if the maintenance run or the reapply created the constraints.
Thanks, Jens

[keithf4]

Actually, I've reworked the logic in both the reapply_constraint_proc() procedure and the apply_constraints() function that is called during normal maintenance. This should work more consistently in both cases now. This fix will be in the next release.

Thank you!

[keithf4]

If you are able to test in a development environment, the development branch here has the fix for this up.

[keithf4]

Version 5.2.0 has been released with this fix. Thanks again for reporting the issue!