4 ms·

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 ha

• gmfawcett 6y ago
  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. :)
  • gav 6y ago
    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).
    • gmfawcett 6y ago
      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.
  • benjohnson 6y ago
    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.
    • niccl 6y ago
      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
      • munk-a 6y ago
        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.
    • ptman 6y ago
      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)
  • rrrrrrrrrrrryan 6y ago
    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.
  • abower 6y ago
    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.
• munk-a 6y ago
  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.