Fix SPLIT PARTITION Bound-Overlap Bug and Related Improvements

Core Problem: Silent Partition Bound Corruption

The thread reports a critical correctness bug in PostgreSQL's ALTER TABLE ... SPLIT PARTITION for range-partitioned tables. When a SPLIT PARTITION command creates exactly two new partitions — one explicit range partition and one DEFAULT partition — the validation logic in check_partition_bounds_for_split_range() fails to check the upper bound of the explicit partition against the upper bound of the original split partition. This allows the new partition to have an upper bound that exceeds the original partition's bound, creating overlapping partitions in the catalog.

Why This Is Architecturally Dangerous

PostgreSQL's partition pruning logic in the planner relies on the invariant that partition bounds are non-overlapping and contiguous. When this invariant is violated:

1. Silent data invisibility: Partition pruning can exclude partitions that actually contain matching rows. Kirill Reshke's reproduction demonstrates this concretely — SELECT * FROM t WHERE i = 51 returns zero rows when partition pruning is enabled, despite three rows with i = 51 existing in the table. Disabling partition pruning (enable_partition_pruning = off) reveals the rows.

2. Data integrity violation: The overlapping bounds mean the partition metadata is internally inconsistent. This is not just a cosmetic issue — it corrupts the logical model of the partitioned table. Any query plan that relies on bound exclusivity (which is essentially all partitioned queries) may return incorrect results.

Root Cause Analysis

The bug lives in src/backend/partitioning/partbounds.c in the function check_partition_bounds_for_split_range(). The function iterates over the new partitions specified in the SPLIT command and validates their bounds against the original partition being split. It uses first and last flags to determine which bounds to check:

• For the first non-default partition: check that its lower bound matches the split partition's lower bound.
• For the last non-default partition: check that its upper bound matches the split partition's upper bound.

The critical flaw is the use of if (first) ... else ... control flow. When there is exactly one non-default partition (which is both first AND last), only the if (first) branch executes. The else branch that checks the upper bound is never reached. This means:

SPLIT PARTITION p0a INTO (
  PARTITION p0a FOR VALUES FROM (0) TO (53),  -- upper bound 53 > original 51!
  PARTITION pdef DEFAULT
);

...passes validation despite the new p0a's upper bound (53) exceeding the original p0a's upper bound (51).

The Fix

The fix is minimal and surgical: change else to if (last) so both bounds are checked independently:

-               else
+               if (last)
                {
                        PartitionRangeBound *split_upper;

This ensures that when a partition is both first and last (the only non-default partition), both its lower and upper bounds are validated. This is a classic off-by-one-in-logic error — the original code assumed first and last were mutually exclusive, but they are not when there's only one explicit partition plus a DEFAULT.

Additional Proposed Improvements

Documentation Clarity (Item 1)

The current documentation for SPLIT PARTITION is ambiguous about the semantics of a DEFAULT partition in the split result. The docs state that "the combined bounds of new partitions should be equal to the bounds of the split partition," which could be read as implying the new DEFAULT partition only covers the remainder of the split partition's bound range. In reality, the new DEFAULT partition becomes the table-level global DEFAULT partition, covering all values not matched by any partition in the entire table (not just the split partition's range). The patch proposes documentation improvements to clarify this distinction.

Misleading Hint Message (Item 2)

When a SPLIT PARTITION command specifies both explicit partitions and a DEFAULT partition, and an explicit partition's bound exceeds the original, the error hint says "combined bounds of new partitions must exactly match the bound of the split partition." This is misleading because when a DEFAULT partition is included, the explicit bounds only need to stay within the original partition's bounds (the DEFAULT absorbs the remainder). The patch improves the hint message for this specific case.

Degenerate No-Op Split (Item 3)

The patch proposes rejecting SPLIT PARTITION commands where one of the new partitions has identical bounds to the original partition and the other is DEFAULT. This is a no-op split that effectively just adds a DEFAULT partition via a heavyweight code path (create new partition, move data, attach, drop old). Since CREATE TABLE ... PARTITION OF t DEFAULT and ALTER TABLE ... ATTACH PARTITION already provide direct ways to add a DEFAULT partition, allowing this degenerate form is wasteful and conceptually confusing.

Dmitry Koval, the likely original author of the SPLIT PARTITION feature (based on his Postgres Professional email), agrees on the bug fix but expresses hesitation about rejecting the degenerate case, suggesting it may be better to keep it "as is." This is a reasonable design disagreement — rejecting valid (if silly) SQL is a policy decision rather than a correctness fix, and there's a general PostgreSQL philosophy of not over-constraining users.

Technical Implications

The bug fix should be backported to all versions containing SPLIT PARTITION support (introduced in PostgreSQL 17). The overlap condition can silently corrupt query results in production, and there is no pg_catalog-level constraint preventing overlapping bounds from being written — the validation in check_partition_bounds_for_split_range() is the sole defense.

The fix is a one-line change with no performance implications, no ABI changes, and no catalog format changes. It purely corrects the control flow logic for bound validation. The additional improvements (docs, hints, degenerate rejection) are enhancements that may warrant more discussion and are appropriate for the next development cycle rather than backport.