Skip to main content

PG.L5.FK_NEAR_CEILING — A foreign-key column the referenced key is about to outgrow

  • Category: safety
  • Level: 5
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18

What it checks​

A foreign-key column narrower than the key it references, where the parent's sequence has handed out at least half of the values the column can hold. The share is the one the primary-key rule uses:

'audit' => [
    'key_ceiling' => [
        'min_percent_used' => 50,
    ],
],

Reported, years after the migrations that created the two tables:

Schema::create('depots', function (Blueprint $table) {
    $table->id();                            // bigint
});

Schema::create('deliveries', function (Blueprint $table) {
    $table->id();
    $table->integer('depot_id');             // integer: 2,147,483,647 values
    $table->foreign('depot_id')->references('id')->on('depots');
});

// depots_id_seq has handed out 2,000,000,000 values: 93.1% of what depot_id can hold.

Not reported, because the column takes its type from the key it references:

Schema::create('deliveries', function (Blueprint $table) {
    $table->id();
    $table->foreignId('depot_id')->constrained();   // bigint, derived from the target
    $table->index('depot_id');
});

Not reported: the same pair while the parent's sequence is below the share, and a column as wide as the key it references.

The column runs out before the key does​

PG.L6.FK_NARROWER_THAN_KEY reports the narrower column on its types alone, from the day the table exists. The parent's own key has room for nine quintillion values, so nothing about the parent looks wrong, and PG.L5.PK_NEAR_CEILING says nothing about it either.

The child column fails the first INSERT that points at a parent row past its ceiling. That day is decided by the parent's sequence, and this rule reads how far it has got. Once the parent has used half of the column's ceiling, the type mismatch has become a date.

The finding names every column past the share, each with the key it references, the sequence behind that key and the share used, in one finding per table.

Where the position comes from​

The parent's position is read beside the catalog, the way the primary-key rule reads its own, and joined onto the referencing table. A bigint key on a bigint sequence cannot run out itself and is normally left unread; it is read when a single-column smallint or integer foreign key references it, because that column's ceiling is decided by its position.

A role that holds neither SELECT nor USAGE on the parent's sequence cannot read the position; pg_monitor, the recommended audit role, is such a role. The finding is then undetermined and names the grant:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO sqlens_audit;

What it cannot read​

A referenced key filled by the application, without a sequence, has no counter, and the column pointing at it is not judged. A referenced table outside the audited schemas is not read, so a column pointing into it is not judged either.

Sources​

The fix material this rule carries​

A finding from this rule carries machine-readable fix material, using this sequence:

  • expand_contract — Add the new shape, move readers and writers across, remove the old one — over three deploys.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.