Hacker News Reader

Comment by gav | Hacker News Reader

I'm not sure if it's just me, but when I see DELETEs that use CTEs like that I start to get nervous. It's easy to get wrong and it's hard to undo.

I like to have an intermediary table where I can verify what's going to be deleted (or updated) before it runs. Even better it lets you save the mappings of old ids so when you find out some downstream system was using them you can fix that too.

I know it's just an example but soft deletes solve a huge range of problems.

View on HN

Rule of thumb for me is that every DELETE or UPDATE query should start life as a SELECT query.

The eight stages of DELETE:

- SELECT what you're deleting;

- fix the mistake in the query (there's always one);

- SELECT again;

- BEGIN TRANSACTION;

- DELETE;

- SELECT to confirm the right deletion -- twice for good measure, and maybe ROLLBACK once or twice if you're feeling twitchy;

- Hover finger anxiously over the un-entered COMMIT command for several seconds while resisting Dunning–Kruger effect;

- COMMIT. :)

I do the same thing.

However, it won't save you if realize that there's an issue after you commit. Maybe I'm just paranoid but I like to have both a snapshot before a major change, plus I'm fond of having a spare (via delayed replication or log shipping).

Ah, I forgot to include the twelve stages of proactive, redundant backups. :) I usually do the same thing -- local snapshots if the database is small and the app is less critical... or a careful review of the central db backup history and a documented rollback plan if it's a bigger system.

Here's my insanity

Give the table an DeletedTimestamp and DeleatedReason column.

Mark the records as deleted.

Run around like a lunatic and fix queries so they don't show "deleted" columns to the rest of the app.

Make a page in the application where people can "restore" deleted items. Because they do stupid things too once in a while.

My fix for this is to rename the table to something like _b_<table_name> then create a view <table_name> that excludes the 'deleted' values (based on the value in DeletedReason column). That way you make one change to the structure when you create the column, and everything else Just Works

We use the same approach and specifically use a "...withdeleted" suffix for the table - you got a table of containing widget objects there? how about a "widgetwithdeleted" table.

until you hit requirements that data must really be gone from the system

at least you need something like DELETE FROM foo WHERE deleted < NOW() - '30 days'::timedelta (or whatever the correct syntax is)

Large DELETEs muck up table statistics and cached joinplans, which is why a lot of enterprise databases have an IsDeleted column on their large tables. Setting things up this way has the added benefit of being able to easily "delete" things without too much anxiety.

Ideally, all SELECT queries are forced through a view layer (which already has an IsDeleted = 0 restriction), and you can just schedule a nightly job which deletes or archives all the rows with IsDeleted = 1, then refresh the table statistics automatically.

Yep, I do the same. Depending on size of data being affected, level of table RI in place,and necessity of seeing the data again later, or client calling back the following day/week having changed their mind and can we 'undo' that, I will often tack in a select into tablename_changes_jan7_2020 or some such convention to grab the unchanged records. Qty, backup retention options etc also play into this so use some common sense of course. If you worry about space, just set an drop table job up to clear it after a month or whatever. Lots of these CYA tricks if you want to make sure you don't shoot yourself in the foot.

It's a good idea to never run hand-crafted data mutating queries against a production DB. Nervousness is fine but CTEs are extremely useful - at our company we insist on automated tests around SQL that will be run against the prod DB and that makes me quite happy. Just make sure the devs prove their CTE is correct before letting it touch the data.

Reply on news.ycombinator.com