Hacker News Reader

Comment by freetanga | Hacker News Reader

Always do a select with your criteria before doing a Delete or update.

Don’t ask me how I learned this.

At a previous place I worked, if they were working on the cli (eg in psql or similar) they'd always use these two steps, either of which would provide adequate protection:

1. Start a transaction before even thinking of writing the delete/update/etc (BEGIN; ...)

2. Always write the WHERE query out first, THEN go back to the start of the line and fill out the DELETE/UPDATE/etc.

It worked well, and it's a habit I've since tried to keep on doing myself as well.

Once I wanted to do `rm -fr *~` to delete backup files, but the `~` key didn't register...

Now I have learnt to instinctively stop before doing anything destructive and double-check and double-check again! This also applies to SQL `DELETE` and `UPDATE`!

I know that `-r` was not neccessary but hey that was a biiiig mistake of mine!

For entirely critical systems, I by now rather generate a reviewable script and run that. Something along the lines of:

    find /backups/ -mtime '-30' -printf 'rm -f %p\n' > very_scary_deletes.sh

This gives you a static script with a bunch of rm's. You can read that, check it, give it to people to validate and when you eventually run it, it deletes exactly those files.

That's clever, only do really dangerous things if you have a way to carefully review the steps.

Another way is sometimes: `echo *.txt~` and when you like the result, replace `echo` with `rm`.

I do this too. Although I usually do `echo rm .txt~` then just remove the `echo` once everything works. Also works well for things like `parallel` and `xargs` where you can do `parallel echo rm -- .txt` and it basically prints out a script. Then you can remove the `echo` to run it.

If you ever type really dangerous commands, it is good practice to prefix them with a space (or whatever your favorite shell convention is) to make sure they not saved in your history.

One of my "oopsies" involved aggressive <up> and <enter> usage and a previous `rm -rf *` running in /home...

I didn't know about the space thing!

Though for me, I think the greater risk would be from not having a record of what I'd run.

One time I was debugging the path resolver in a static site generator I was writing. I generated a site ~/foo, thinking it would do /home/shantaram/foo, but instead it made a dir '~' in the current directory. I did `rm -rf ~` without thinking. Command took super long, wondered what was going on, ctrl-c'd in horror... that was fun.

I’m really curious: without cheating by using the GUI, what would be the proper way to delete such an obnoxiously-named directory? Would “$PWD/~” work?

A few ways.

rm ./~ is likely the easiest.

Another option, shell dependent, would be to turn off shell globbing.

The `GNU` version of `find` has a `-maxdepth` option so

find . -iname '~' -maxdepth 1 -exec rm {} \;

would work, but I don't like relying on `GNU` extensions.

Or just: rm "~"

You remind me of that time I wanted to `rm -rf ./*` but the dot hadn't registered... I now avoid that statement.

I click in Explorer. Have to confirm too. Never been confused.

>Don’t ask me how I learned this.

Joke's on you, we all learned it this exact way.

This gives my heart pains I can't explain to my therapist..

Luckily I learned from someone else. I did figure out start with a transaction the hard way though

i can still feel the pain in the pit of my stomach when i saw the amount of rows affected.

thankfully my boss at the time was a amazing db admin and he helped me fixing my mistake.

Came here to say exactly that! I do a select count and then a limit against the returned count. At least it may reduce the blast radius.

In most of my scenarios, I'd actually rather cause a catastrophic global change than a silent subtle corruption of a handful of rows

That may be fun in a trivial setup such as op’s but when millions of customers or billions of transactions are affected it’s a nightmare. A competent engineer runs queries against a local and then a uat db, verifies results and then on prod. But if you must do it in prod then it must be limited in scope.

We're on the same page with the best approach, I just don't consider corrupting an unpredictable subset of my database much of an improvement. It's not closer to correct, it's just still incorrect.

This can also bite you if your dataset is larger than the buffer pool (or whatever other RDBMS calls it), and the particular table you’re querying isn’t commonly accessed.

Turns out when you start loading millions of rows of useless data into memory, the useful data has to get kicked out, and that makes query latency skyrocket.

Same thing on a Unix/Linux level: When using find, I always do it first with -print until I see the files I want. Only then do I add the actual action I want.

Then start a transaction, and only commit when the row numbers match.

That, and some DB tools (line JetBrains DataGrip) block UPDATEs and DELETEs without a WHERE condition.

Gone are the days of hard deletes in my approach; it's exclusively soft deletes now!

In the EU soft deletes can violate GDPR, so not always an available strategy.

Soft delete items immediately, purge the soft deleted rows automatically after 30 days or so.

how did you learn it?

Reply on news.ycombinator.com