Hacker News Reader

Comment by jrumbut | Hacker News Reader

Even having a dry run mode is exciting. Doesn't even have to give complete results just "I was planning to delete 3 files and create 7 files", gives a hint whether the command will blow up the system or not.

View on HN

I wish SQL had a dry-run mode in updates and deletes for that reason.

"Run it as a query first" gets 90% of the way until you drop a constraint by accident whilst rewriting it as an update :o

For interactive queries / surgery, you do have an option with a transaction (begin/commit/abort).

If it is Postgres (don't know about other dbs), you can go a way long way using "savepoints" and "rollbacks" to truly have a trial-and-error safe surgery on db. Still dangerous, but quite helpful. I hate working on any other db without those features. Postgres also allows schema changes to be within a txn envelope.

Transactions and rollback is the dry run. The problem is that if you keep the transaction open for too long, you will block other updates to the same data.

Yep, I always write any update queries as a rollback transaction with some selects inside it to verify what the data looks like after it's done now, before I switch it to commit. I primarily use Microsoft SQL Server right now, so I also use WITH (NOLOCK) to prevent issues running my query will have with other updates.

Wrap it in a transaction and roll back the transaction at the end. Then remove the transaction when you are ready to do it for real.

You can jam a select in the end of the transaction to check what happens.

I've thought the same thing. I also wish SET came after where. I've done "UPDATE table_x SET something = true"; and then forgot the WHERE clause.

I think an improvement to SQL would be for insert/update/delete clauses to require a where clause and allow for something like 1=1 if you really intend to hit all rows. A safe but even more invasive would be requiring an end to the were clause as well (to prevent selecting a few but not all constraints).

I think this is true for mysql, at least for delete.

MySQL has a command line option "--i-am-a-dummy" (aka "--safe-updates") for exactly this purpose.

https://dev.mysql.com/doc/refman/8.0/en/mysql-command-option...

Enough folks have replied that transactions are the way to go, but I just wanted to add that whatever interface tool you use for your database may have an option to force you to commit your transactions manually. For example PostgreSQL's default 'psql' shell has the "autocommit" option which, when disabled, requires you to manually 'commit;' before any changes take effect.

Reply on news.ycombinator.com