Article: A production PostgreSQL deployment crashed after a developer added an optional argument to an existing function with CREATE OR REPLACE FUNCTION. The change left two functions with the same name, causing the database to return “function is not unique” and the API to emit a 400 error. The incident shows how a single migration mistake can silently corrupt a live schema and why code-level checks alone aren’t enough.

What went wrong

The team needed to extend a stored procedure with an extra, optional parameter. They ran CREATE OR REPLACE FUNCTION …, assuming it would overwrite the old definition. PostgreSQL only replaces a function when the full argument list matches exactly. Changing the signature creates a brand-new function entry while leaving the original untouched.

Because the new argument had a default value, callers that supplied the old number of arguments could match either definition. PostgreSQL couldn’t decide which one to invoke and threw the “function is not unique” error, which surfaced as a 400 response from the API.

The code repository showed a single definition, and a custom script that scanned the source tree reported no duplicates. The duplicate lived only in the database, introduced when an old migration file was re-executed on the server.

Why the migration slipped through

The migration that added the optional parameter simply ran CREATE OR REPLACE FUNCTION. When the migration ran a second time—perhaps after a rollback or during a repeat deployment—the database treated the command as “add a new overload” rather than “replace the existing one.” The migration didn’t verify the resulting state, so the duplicate persisted unnoticed.

The script that checked the repository examined source files, not the live schema. It was looking at the front door while the bug entered through the back.

The stakes

A single ambiguous function can bring down any service that relies on it. The fix involved rolling back the transaction if the function count was wrong and notifying the schema cache to reload.

How to safeguard migrations

The team rebuilt the migration with explicit checks, turning it into a self-asserting operation:

  • Start a transaction so any failure rolls back the whole change.
  • Drop the old function explicitly before creating the new version, guaranteeing only one definition exists.
  • Create the new function with the desired signature.
  • Count the functions with the given name in pg_catalog and verify the count is exactly one.
  • Roll back the transaction if the count deviates, preventing the duplicate from persisting.
  • Notify the schema cache to reload, ensuring subsequent queries see the updated definition.

By asking the database “what functions are present?” rather than assuming the code is correct, the migration becomes reliable against repeated runs, partial deployments, or manual edits.

Counter-argument: convenience vs. safety

CREATE OR REPLACE FUNCTION is attractive because it lets developers iterate quickly without writing separate drop statements. In environments where migrations run once and never re-run, the shortcut works fine. The risk appears when migrations are replayed—whether due to CI pipelines that reset test databases, automated rollbacks, or manual re-applications in production.

Takeaway

Changing a function’s signature with CREATE OR REPLACE does not guarantee replacement—PostgreSQL will silently create an overload if the argument list differs. Production environments that rely on migrations must verify the resulting schema, not just the source code. Embedding explicit drops, transactional checks, and post-migration assertions turns a convenient shortcut into a reliable, repeatable process.