Technical Analysis: uuidv7() Interval Shift Boundary Validation

Core Problem

The uuidv7(interval) function in PostgreSQL accepts arbitrary interval offsets to shift the timestamp embedded in a UUIDv7, but performs no bounds checking on the resulting timestamp value. This creates several problematic scenarios:

1. Pre-epoch timestamps: uuidv7('-1000 years'::interval) produces a UUID with a negative Unix timestamp, which is invalid per RFC 9562. The spec defines the unix_ts_ms field as an unsigned 48-bit integer representing milliseconds since the Unix epoch (1970-01-01), making negative values semantically meaningless.

2. Overflow/wraparound: uuidv7('100000 years'::interval) exceeds the 48-bit representable range (~8,919 years from epoch) and silently wraps around, producing a UUID whose embedded timestamp bears no meaningful relationship to the intended time.

3. Infinity inputs: uuidv7('infinity'::interval) overflows the int64 during epoch conversion, producing garbage output with no diagnostic.

The architectural significance is that UUIDv7 was specifically designed to provide time-ordered identifiers. If the timestamp field can silently wrap around or encode invalid values, the fundamental sortability guarantee is destroyed — and users may not discover this until years of data have been generated with broken ordering.

Design Tension: Flexibility vs. Safety

The central disagreement in this thread is between two design philosophies:

Permissive Approach (Andrey Borodin)

Borodin argues that the overflow behavior was intentionally left unchecked to support a sharding use case: uuidv7(INTERVAL '1000 years' * shard_id) gives each shard its own "time lane" within the 48-bit space, achieving per-shard time-locality without cross-shard ordering. He states the RFC authors confirmed this is compliant. His position is that even pre-epoch offsets produce UUIDs that are still unique, still ascending within a single backend, and thus don't violate documented guarantees.

Strict Approach (Christophe Pettus, Masahiko Sawada, Baji Shaik)

The counter-argument is multi-pronged:

• The RFC defines unix_ts_ms as unsigned, so negative values are spec-violating
• Wraparound silently destroys the monotonicity guarantee that is UUIDv7's raison d'être
• PostgreSQL itself raises errors on timestamp overflow (timestamp + interval that exceeds representable range), so silent wraparound is inconsistent with platform conventions
• The sharding pattern, while creative, becomes a footgun with enough shards (only ~6 shards with a 10-year offset before wraparound occurs)
• Users relying on sort-order guarantees may only discover the problem catastrophically, years later

Proposed Solutions

Patch 1: Lower-bound check (Christophe Pettus)

• Adds a check that the shifted timestamp is ≥ 0 (Unix epoch)
• Raises an error if the interval would produce a pre-epoch timestamp
• Includes regression test
• Applies cleanly to HEAD

Patch 2: Infinity check (Baji Shaik)

• Adds TIMESTAMP_NOT_FINITE() check after timestamptz_pl_interval()
• Catches both infinity and -infinity interval inputs
• Offered to extend to full 48-bit upper/lower bound validation

Implied Complete Solution (Sawada's synthesis)

• Both lower-bound (≥ 0) AND upper-bound (≤ 2^48 - 1 milliseconds) checks
• Plus infinity rejection
• Error semantics consistent with PostgreSQL's native timestamp overflow behavior

Technical Details of the 48-bit Constraint

The UUIDv7 unix_ts_ms field is exactly 48 bits, representing unsigned milliseconds since Unix epoch:

• Minimum: 0 (1970-01-01 00:00:00.000 UTC)
• Maximum: 2^48 - 1 = 281,474,976,710,655 ms ≈ year 10889

Any shifted timestamp that falls outside [0, 2^48 - 1] milliseconds produces either a spec-violating UUID or a wrapped-around value that breaks ordering guarantees.

Backward Compatibility Concern

Sawada correctly identifies that adding boundary checks is a behavioral change that could break existing applications already relying on the permissive behavior. However, any such application is already producing spec-noncompliant UUIDs or UUIDs with broken time-ordering, so the "breakage" is arguably surfacing a latent bug rather than removing a feature.

Current Status

The thread has consensus building toward adding boundary validation, with two concrete patches offered and a third (comprehensive) solution being coordinated. No committer has formally objected to the stricter checking. The discussion is at the "agree on direction" stage before a final unified patch is produced.