Schema Diff: rebuilding a partitioned table leaves its scaffolding default partition behind

[dpage]

Bug Description

When Schema Diff has to rebuild a partitioned table, it adds a default partition to the replacement table and never removes it, so the target ends up with a partition the source does not have and the two databases are still reported as different after the script has been applied.

The generated script builds a temporary partitioned table, adds the default partition, copies the rows across, and renames everything into place:

CREATE TABLE IF NOT EXISTS test_schema_diff.temp_partitioned_7169680 (
    LIKE test_schema_diff.table_for_partition_1 INCLUDING ALL
) PARTITION BY RANGE (col1);

CREATE TABLE test_schema_diff.partition_1624663 PARTITION OF test_schema_diff.temp_partitioned_7169680
    FOR VALUES FROM ('1') TO ('10');

-- It helps when none of the partitions of a relation
-- matches the inserted data.
CREATE TABLE IF NOT EXISTS test_schema_diff.table_for_partition_1_default PARTITION OF test_schema_diff.temp_partitioned_7169680 DEFAULT;

INSERT INTO test_schema_diff.temp_partitioned_7169680(col1)
SELECT col1 FROM test_schema_diff.table_for_partition_1;

The default partition is there for a good reason, since the copy would otherwise fail for any row that no partition accepts, but it is scaffolding rather than part of the definition being applied. Where the source table has no default partition, the target is left with <table>_default and comparing the two databases again reports the partitioned table as different, indefinitely.

Expected Behaviour

Where the source has no default partition, the rebuild should drop the default partition it created once the rows have been copied, so that applying the script settles the difference. Where the source does have one, it should of course be kept.

Context

Found by making the Schema Diff regression test assert that applying the generated script leaves the two databases identical (#10293). The test lists this as a known outstanding difference until it is fixed.