CREATE OR REPLACE didn't replace: one optional parameter, and my API 400'd in production

A button in my admin panel stopped working. It had worked the day before. I hadn't touched it.

I run a small coffee e-commerce platform, alone. One of the things it does is issue signed links — the customer gets a link, opens it, confirms they received their order. There are three entry points: a button in the admin order list, a button that pushes the link to the customer over LINE, and a button inside the customer's own order page.

All three broke at once. All three showed the same toast: "Couldn't generate the confirmation link, please try again later."

Which told me nothing at all.

The error was there. My frontend was eating it.

The catch block behind that button looked like every catch block I've ever written:

} catch (err) {
  error.value = err.message || 'Could not create confirmation link'
  setLink('')
  return null
}

The caller only sees null, so it throws the generic toast. The actual message never reaches a human.

I found the real error the way you always find it — DevTools → Network → click the failing rpc/... request → read the response body:

{ "code": "42725", "message": "function fn_issue_order_action_token(...) is not unique" }

42725. Function is not unique. Postgres was telling me there were two functions with that name, and it refused to guess which one I meant.

I only ever wrote one.

CREATE OR REPLACE FUNCTION does not replace your function

Here is the thing I understood wrong for about six months.

The original function had three parameters:

CREATE OR REPLACE FUNCTION fn_issue_order_action_token(
  p_order_id       text,
  p_action         text,
  p_expires_minutes integer
) RETURNS jsonb ...

Later I added support for partial pickups, which needed to carry some extra data along with the token. So I added one parameter — optional, defaulted, entirely backward compatible:

CREATE OR REPLACE FUNCTION fn_issue_order_action_token(
  p_order_id       text,
  p_action         text,
  p_expires_minutes integer,
  p_metadata       jsonb DEFAULT NULL   -- <- the whole bug
) RETURNS jsonb ...

Every existing caller passes three arguments. The fourth defaults to NULL. Nothing breaks. That's the reasoning, and it's wrong.

CREATE OR REPLACE FUNCTION replaces a function only if the identity arguments match exactly. (text, text, integer, jsonb) is not (text, text, integer). So it didn't replace anything. It created a second function, and now the database held both:

oid 109091 | (text, text, integer)          <- from an old migration
oid 108290 | (text, text, integer, jsonb)   <- the "replacement"

Then PostgREST called it with three arguments. The 3-arg version matches. The 4-arg version also matches, because its fourth parameter has a default. Two candidates, no tiebreaker, 42725, HTTP 400.

The symptom — "worked in the last PR, started 400ing after I added a parameter" — is genuinely hard to connect to the cause, because the parameter you added is the one nobody is passing.

The fix is thirty seconds:

DROP FUNCTION IF EXISTS public.fn_issue_order_action_token(text, text, integer);
NOTIFY pgrst, 'reload schema';

Two things worth knowing here. Get the exact signature from pg_get_function_identity_arguments rather than typing it from memory — the DROP silently no-ops if you get one type wrong, and IF EXISTS means you won't even be told:

SELECT oid, pg_get_function_identity_arguments(oid)
FROM pg_proc WHERE proname = 'fn_issue_order_action_token';

And the NOTIFY pgrst matters if you're on PostgREST/Supabase — it caches the schema, so without it the API keeps serving the ambiguity you just resolved.

That's the bug. If the post ended here it would be a fine TIL. It doesn't end here, because I'd already fixed this bug. Twice.

Rule, then guard, then it came back anyway

Pulling git log afterward, this class of bug had visited me on a schedule:

When	What	What I did about it
Feb 27	Return-type conflict (42P13), needed a DROP first	Fixed it inline. Forgot about it.
May 17	Changed an OUT parameter on the topup RPC — same trap	Fixed it inline again, still relying on remembering
May 20	The signed-link RPC, ambiguous overload	Fixed it, and finally wrote an automated check
May 26	The signed-link RPC. Again.	—

Three months between the first and the second, so I never connected them. Then two in one week, which is what it took for me to stop patching and write a static check: scan every migration, if it changes a function signature without a preceding DROP FUNCTION, block the commit.

Six days later the same RPC broke the same way.

I want to be precise about why, because it's the part I got wrong and it's the part that generalizes.

My check asked: does this new migration introduce an overload without dropping the old one? Every migration in the repo passed. It kept passing while production was broken — because nothing new had been written. What happened was that an old migration — the one that created the 3-arg version, written months earlier and long since superseded — got applied again. Somewhere. Somehow. It re-created a function I had already dropped.

My repo said the 3-arg function was gone. My repo was right about its own contents and wrong about the world.

That's the shape of the mistake: I had written a guard against the failure I had just experienced, and assumed it was a guard against the failure mode. Introducing a bad overload and resurrecting a dead one produce the identical broken state, and my check could only see the first one.

Two things fixed it, and neither of them was a rule

The migration now checks itself. Instead of trusting the check that runs on my machine, the migration asserts against the database it's actually modifying:

BEGIN;

DROP FUNCTION IF EXISTS public.fn_issue_order_action_token(text, text, integer);

DO $$
DECLARE v_count int;
BEGIN
  SELECT COUNT(*) INTO v_count FROM pg_proc
  WHERE proname = 'fn_issue_order_action_token';

  IF v_count <> 1 THEN
    RAISE EXCEPTION 'expected 1 overload, got %', v_count;
  END IF;
END $$;

NOTIFY pgrst, 'reload schema';
COMMIT;

This doesn't care how the extra function got there. It doesn't care whether the migration was new, old, or replayed by something I don't know about. It looks at pg_proc, counts, and refuses to commit if reality disagrees. Same principle now runs as a smoke test against the live database over a whitelist of critical RPCs — the repo can't answer "how many overloads exist in production," so I stopped asking the repo.

And I removed the race that likely caused it. My migrations were numbered sequentially — 217_, 218_. Going back through the folder I found five numbers that each had two different files: 217, 218, 219, 230, 231. Sequential numbering means every branch and every parallel session guesses the same next number, and whoever writes second wins the filename. I never proved that's how the old migration got replayed, but it's an obvious way to end up applying a file you didn't mean to, so I moved everything to UTC timestamps. New filenames can't collide.

The part I gave up on

I never found out how that old migration got re-applied.

I tried. The migrations table had exactly one row for it and zero rows for everything in the 217–231 range, because these were applied by hand and hand-applied migrations don't register. Git had a single commit and nothing suspicious. The production logs from the relevant window had already rotated out.

So I wrote down that the forensics failed, and stopped. The self-asserting migration plus the live overload smoke test mean that if it happens a third time I'll know within one command instead of one customer complaint — and I decided that's the property I actually needed. Knowing the exact mechanism would have been satisfying. It wouldn't have made the next occurrence any less likely.

If there's one thing to take from this, it's not the DROP FUNCTION — you'll hit that once and remember it forever. It's the shape of the second mistake: a guard written from a single incident tends to encode that incident's route to the failure, not the failure. Mine watched the front door for six days while the same bug walked back in through a door I'd already locked and never checked again.

The cheapest way I've found to avoid that is to make the check ask the system what state it's in, rather than asking my repo what state it should be in.