Hacker News Reader

Comment by morelisp | Hacker News Reader

A similar tip I picked up long ago: If you're typing a dangerous command, first type a `#` (or `--` if it's SQL, etc.), then the command. Then read it. Then go back to the start of the line and remove the comment and run it.

View on HN

I always do destructive SQL commands in two steps: first run a select using the WHERE clause you intend to use and verify which records will be affected, then hit the up arrow and edit the beginning of the query leaving the WHERE intact.

I also like adding redundant conditions to the WHERE so a typo in any single one of them won't sink me.

For the rare but critical manual SQL mod our common safety measure is to wrap every DELETE or UPDATE in BEGIN TRAN...ROLLBACK TRAN first. Run on test systems or snapshots multiple times, checking the result inside the transaction.

Finally, change ROLLBACK to COMMIT only when you are positive all is well.

IIRC (without checking the manuals) data-definition commands might not be covered by such transactions: such as altering, dropping tables and possibly truncates.

PostgreSQL is quite good about DDL being transactional. So I was surprised (tbf, I shouldn't have been) when Redshift autocommitted after a TRUNCATE. But DROP TABLE is transactional, go figure.

DDL is transactional in Microsoft SQL Server as well.

I do the same thing. I also keep auto commit off and make sure the rows updated looks correct before committing the change.

A related Bash command: alt+# to prepend a hash symbol and submit the line, so you can return to it later (through Bash's history) to run it.

I use an alternate version on SQL: when running any modification on any kind of sensible database (which is a bad practice in itself, obviously, but sometimes you don't have a choice), always type in the WHERE clause before the table name (added bonus: do a SELECT first with that clause to see what you are modifying).

That way, if you accidentally send it, the command fails and nothing happens.

For SQL, always BEGIN first. If you’re unsure, run it as an EXPLAIN first. Then fire. Then commit it or roll back.

Especially useful if you're remoted in over a laggy connection.

Reply on news.ycombinator.com