"UPDATE table_name SET w = $1, x = $2, z = $4 WHERE y = $3 RETURNING *",
does not do the same as
"UPDATE table_name SET w = $1, x = $2, y = $3, z = $4 RETURNING *",
It’s 2 am and my mind blanked out the WHERE, and just wanted the numbers neatly in order of 1234.
idiot.
FML.
This is a hard lesson to learn. From now on, my guess is you will have dozens of backups.
And a development environment. And not touch production without running the exact code at least once and being well slept.
Replied hastily, but the way to run db statements in prod while dealing with sleep deprivation and drinking too much is to run it a bunch in several test env scenarios so you’re just copy pasting to prod and it CAN confidently be done. Also enable transactions and determine several, valid smoke tests.
Edit: a -> several
And always use a transaction so you’re required to commit to make it permanent. See an unexpected result? Rollback.
Transactions aren’t backups. You can just as easily commit before fully realizing it. Backups, backups, backups.
Yes, but
- Begin transaction
- Update table set x=‘oopsie’
- Sees 42096 rows affected
- Rollback
Can prevent a restore, whereas doing the update with auto commit guarantees a restore on (mostly) every error you make
This doesn’t help you but may help others. I always run my updates and deletes as selects first, validate the results are what I want including their number and then change the select to delete, update, whatever
That’s an easy one to recover from:
Simply fake your own death and become a goat herder in Guatemala.
Pro tip: transactions are your friend
Postgres has a useful extension, pg_safeupdate
https://github.com/eradman/pg-safeupdate
It helps reduce these possibilities by requiring a where clause for updates or deletes.
I guess if you get into a habit of adding where 1=1 to the end of your SQL, it kind of defeats the purpose.
MySQL (and by extension, MariaDB) has an even better option:
mysql --i-am-a-dummy
