fix(pgsql): key routines by signature so overloads survive the diff (#187) - #188

Fixes #187.

getRoutines() keyed its map on proname, but Postgres allows several functions to share a name with different argument signatures. Overloads overwrote each other, so N−1 were invisible to the diff entirely — genuine drift in them was missed — and which one survived depended on row order, which differs between databases.

Reproduced

Three identical cosine_distance overloads, created in a different order in each database. The schemas are content-identical (both hash to 058b5b2e16392bd9710a81b99769fa24), but getRoutines' own query returns them in different orders:

--- ovl_a ---                        --- ovl_b ---
cosine_distance -> RETURNS integer   cosine_distance -> RETURNS bigint
cosine_distance -> RETURNS text      cosine_distance -> RETURNS text
cosine_distance -> RETURNS bigint    cosine_distance -> RETURNS integer

Last row wins, so one side kept bigint and the other integer, and dbdiff generated a migration for two identical schemas. That migration did not even run:

DROP FUNCTION IF EXISTS "cosine_distance";
ERROR:  function name "cosine_distance" is not unique
HINT:  Specify the argument list to select the function unambiguously.

Had it been unambiguous it would have removed all three overloads to recreate one.

Fix

Key by signature. p.oid::regprocedure::text renders as cosine_distance(integer,integer) — unique per overload and stable across databases, unlike oid. Ordering by the same expression makes the result deterministic, where ORDER BY proname left ties unordered.

Carry the argument list into the DROP. RoutineDrop::build() appends it outside the quoted identifier — quoting the whole signature yields "fn(integer,text)", one odd identifier rather than a call signature. A bare name (MySQL, which has no overloading) passes through unchanged.

Three SQL generators carried identical private copies of the drop builder, so this consolidates them rather than writing the argument handling three times. CreateRoutineSQL was the third and I initially missed it — the golden-file tests caught it, emitting "get_product_count()" with the parens inside the quotes.

Triggers had the same class of bug. tgname is unique per table, not per schema, so two tables sharing a trigger name silently lost one — confirmed on a live server. Now keyed by table and name, with the bare name carried alongside so the emitted DDL is unchanged.

Verification

End to end against PostgreSQL 16:

• Identical schemas containing overloads → no drift (previously a spurious migration).
• One overload genuinely changed → DROP FUNCTION IF EXISTS "cosine_distance"(text,text);, which applies cleanly and leaves the other two overloads intact (2 remaining, not 0).
• Both same-named triggers on different tables now appear in the diff; previously one vanished.

803 tests pass, 15 of them new: 9 unit tests for the drop builder (argument list placement, bare names, PROCEDURE vs FUNCTION detection, both Alter directions) and 6 live-server tests for overload retention, signature keys, per-overload definitions, deterministic ordering, and the trigger collision.

Golden fixtures

programmable_objects_pgsql_14 through 18 updated for the argument-qualified DROP. The bare form was the bug, so the fixtures were encoding it. Both affected functions are zero-argument, so the change is "name"; → "name"(); identically on every version — verified directly on 16, and CI covers 14/15/17/18.

PostgresAdapter stays at 20 methods; the new RoutineDrop has 2.

Second commit: pinning the Dolt image

Unrelated to the fix, but it is what was holding CI red, so it rides along.

dolthub/dolt-sql-server:latest was the only unpinned database image in the repo — mysql and postgres are version-pinned everywhere, in CI and in docker-compose. On 2026-08-13 latest moved 2.2.3 → 2.3.0, after master's last green run, and the Dolt matrix started failing on branches that had not touched the code.

The failures are Dolt-side and non-deterministic — two runs of the same commit errored 9 tests and then 4, on different tests each time, all with:

PDOException: SQLSTATE[HY000]: General error: 1105 context canceled

thrown from loadFixture() while executing the fixture .sql. That is plain DDL, before any DBDiff code runs, and the sidecar log shows the server cancelling in-flight queries alongside stats stopped: context canceled. The last master run on 2.2.3 had zero such errors.

Pinned to 2.2.3 in CI, and docker-compose.yml now reads the DOLT_VERSION variable that .env.example already defined but nothing consumed, so local runs match CI. Worth revisiting once a Dolt release fixes the cancellation.

🤖 Generated with Claude Code