pgsql: overloaded functions collapse to one entry — false drift on identical schemas, and the generated DROP is invalid #187

Summary

PostgresAdapter::getRoutines() keys its result map on proname alone:

$routines[$row['name']] = rtrim(trim($row['definition']), ';');

Postgres allows several functions to share a name with different argument signatures, so overloads overwrite each other — only one survives per name. The query orders by p.proname, which does not break ties between overloads, so which one survives is arbitrary and can differ between two databases.

Two consequences:

1. False drift on byte-identical schemas.
2. The generated migration is invalid and destructive — an unqualified DROP FUNCTION is ambiguous when overloads exist, and only one of N overloads is recreated.

Reproduction

Two databases, three identical cosine_distance overloads, created in a different order in each:

-- ovl_a
CREATE FUNCTION cosine_distance(a int,    b int)    RETURNS int    LANGUAGE sql AS $$ SELECT 1 $$;
CREATE FUNCTION cosine_distance(a text,   b text)   RETURNS text   LANGUAGE sql AS $$ SELECT 'x' $$;
CREATE FUNCTION cosine_distance(a bigint, b bigint) RETURNS bigint LANGUAGE sql AS $$ SELECT 2::bigint $$;

-- ovl_b — same three, reverse creation order
CREATE FUNCTION cosine_distance(a bigint, b bigint) RETURNS bigint LANGUAGE sql AS $$ SELECT 2::bigint $$;
CREATE FUNCTION cosine_distance(a text,   b text)   RETURNS text   LANGUAGE sql AS $$ SELECT 'x' $$;
CREATE FUNCTION cosine_distance(a int,    b int)    RETURNS int    LANGUAGE sql AS $$ SELECT 1 $$;

The two schemas are content-identical — hashing all definitions sorted by text gives 058b5b2e16392bd9710a81b99769fa24 on both.

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 ovl_a keeps the bigint overload and ovl_b keeps integer. Running a schema diff between them:

$ dbdiff diff --server1-url=…/ovl_a --server2-url=…/ovl_b --type=schema --include=both

-- ==================== UP ====================
DROP FUNCTION IF EXISTS "cosine_distance";
CREATE OR REPLACE FUNCTION public.cosine_distance(a bigint, b bigint)
 RETURNS bigint
 LANGUAGE sql
AS $function$ SELECT 2::bigint $function$;

A migration for two identical schemas. And it does not run:

$ psql -d ovl_b -c 'DROP FUNCTION IF EXISTS "cosine_distance";'
ERROR:  function name "cosine_distance" is not unique
HINT:  Specify the argument list to select the function unambiguously.

Were the DROP unambiguous, it would remove all three overloads and recreate one — silent data-layer loss.

Verified against master (5fc4f0b) on PostgreSQL 16.

Why this shows up in practice

Any extension exposing overloaded functions in public triggers it. It was found via pgvector, whose cosine_distance, inner_product, l2_norm, l2_normalize, binary_quantize, array_to_vector and array_to_halfvec are each overloaded across vector / halfvec / sparsevec. A downstream user saw nine byte-identical functions reported as drift with the same extension version installed on both sides (akalforge/supaforge#35).

Ordinary user code hits it too — any fn(int) / fn(text) pair.

Root cause

Two separate problems:

1. Identity. The map key must distinguish overloads. p.oid::regprocedure renders as cosine_distance(integer,integer) — unique per overload and stable across databases, unlike oid itself.
2. Ordering. ORDER BY p.proname leaves ties unordered, so row order (and therefore which overload wins) is not deterministic. Even after fixing the key, the map should be ordered deterministically so the diff output is stable.

Suggested fix

• Key routines by signature rather than bare name, e.g. p.oid::regprocedure::text, and order by that too.
• Emit argument-qualified DROPs — DROP FUNCTION IF EXISTS "public"."cosine_distance"(integer, integer) — so the statement is unambiguous and drops only the intended overload.
• Same consideration applies to getTriggers (unique per table, so tgname alone can collide across tables) — worth checking while in there.

Impact

• Every overloaded function is a potential false positive on every schema diff.
• N−1 overloads are invisible to the diff entirely, so genuine drift in them is missed.
• Any generated migration touching an overloaded function fails to apply, or destroys the other overloads.