I ran the members migration in prod, watched it come back green-ish, and then a search index blew up with 42703: column "search" does not exist. The table it wanted that column on was right there in my migration file. So why was Postgres insisting it did not exist?
The whole day was meant to be the members platform going live -- resource catalog, gating, Razorpay subscriptions, an admin CMS, the lot. Nine PRs, all merged, tests green. The last thing standing between me and a working /members was this one migration. And it failed on a column I had very clearly just written a few lines above.
if not exists does not mean what you want it to mean
Here is the trap I walked into. My migration opened with create table if not exists public.resources. Months ago, back when I was throwing things at the wall, I had made a scratch resources table by hand and forgotten about it. Postgres saw a table named resources already sitting there, shrugged, and skipped my create table entirely. Nothing errored. It just quietly did nothing.
Then the very next statement tried to build a full-text search index on the search column -- a column that only exists in my new schema, the schema that never got created. Hence 42703. The error surfaced three statements downstream from the actual problem, which is the worst kind of error to debug at the end of a long day.
if not exists guards against the name, not the shape. It is idempotency by name, and name-based idempotency is a lie the moment two unrelated things share a name. My new members resources table and my forgotten scratch resources table had nothing in common except eight letters.
The fix, and the thing I did not do
The tempting move at 6pm is drop table resources and re-run. I did not. Dropping a table you have not looked at is how you find out later that it had something in it. Instead the migration now opens with a guard block: if a resources table exists WITHOUT a search column, rename it to resources_legacy. Nothing gets deleted. The old table steps aside, my schema gets created properly, and I can inspect or drop the legacy table later, in daylight, when I am not tired and annoyed.
I also added drop trigger if exists before each trigger create, so the whole file re-runs cleanly even if it half-applied the first time. That is the entire point of a migration: you should be able to run it twice and have nothing bad happen. Mine could not, and now it can.
if not exists checks the name, not the shape -- so a name collision fails silent and blows up three statements later.
— note to self
Members V1 is code-complete and the migration is finally safe to re-run. Tomorrow it actually goes live. The lesson was cheap in hindsight and expensive at 6pm: a migration that silently skips is far more dangerous than one that loudly fails.