Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Easy, alternative soft deletion: `deleted_record_insert`

brandur.org

Post reply on HN
Print view View original

101–109 of 109 posts

Re: Easy, alternative soft deletion: `deleted_record_insert`

#101

Post by TylerE »

aidos wrote: post #56
skissane wrote: post #52

Earlier quoted context omitted.

I wish Postgres had something like a "CHECK contraint" on a foreign key. There is a hack of sorts. You create a duplicate of the primary key column, named e.g. id_active. You create a CHECK constraint which says something like "(status = 'Deleted' AND id_Active IS NULL) OR (status 'Deleted' AND id_active = id)". You create a unique index on "id_active", and point your foreign key to that. When you create a record, po…

I’m also not sure about the foreign key restrictions on generated columns (and glancing at the docs I don’t see anything about it on there) but for all intents and purposes they are real columns so I’d imagine it probably works. Apparently they run after the before triggers, I’m not totally sure where foreign keys are checked but probably after that? As an aside, they’re a great feature. We’re using them to generate…

You can choose when FKs apply in postgres, including deferring until the very end of the transaction.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#102

Post by no_wizard »

nickjj wrote: post #62
no_wizard wrote: post #9

Earlier quoted context omitted.

To take a stab at it: This is one of those things where business requirements trump the technical implementation details. Prevailing theory is that actual deletes are bad because you can’t do historical analysis, recovery etc on the data. Say a customer stops using a service for a year but comes back: it’s a big win if you can (at least optionally) restore their data, so the theory goes. That’s why tricks like this e…

I think there's still a spot for deleted_at or deleted_at'like functionality. It's around historical data, especially in a work scenario. For example a worker might create a thread and then 38 other workers reply to it. There could be a lot of great information in this thread. It could also be referenced in 5 other threads and external sources (docs, etc.). If the worker leaves the company, should you really delete t…

If you’re separating your deletions from everything else then I imagine archiving a user would not be the same as deleting one, in which case why not just mark the user as archived?

It prevents the overload of what deletion means as to preserve proper separation of concepts too

Re: Easy, alternative soft deletion: `deleted_record_insert`

#103

Post by nonethewiser »

maxbond wrote: post #30
nonethewiser wrote: post #22

> I’ve spent the time migrating our code away from deleted_at, and we’re now at the point where it’s only left on a couple core tables where we want to retain deleted records for an exceptionally long time for debugging purposes. Sounds like the problem is not soft deleting, but applying soft deleting to _everything_ without thought. Then he goes on to suggest an alternative that is even more complicated. Just includ…

It's a set-and-forget solution, where you only have to reason about it locally to the schema. As they point out, `deleted_at` is viral, you have to incorporate it into every query that touches a table with that column type, otherwise you might get into weird behavior or possibly vulnerabilities/disclosures. A set and forget solution at the ORM level is begging trouble IMHO. I don't like ORMs, but I really don't like…

Id rather have the magic in an ORM than the database. Sometimes this desire to keep people as far as possible from understanding the database schema just makes everything much more complicated.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#104

Post by maxbond »

nonethewiser wrote: post #103
maxbond wrote: post #30

Earlier quoted context omitted.

It's a set-and-forget solution, where you only have to reason about it locally to the schema. As they point out, `deleted_at` is viral, you have to incorporate it into every query that touches a table with that column type, otherwise you might get into weird behavior or possibly vulnerabilities/disclosures. A set and forget solution at the ORM level is begging trouble IMHO. I don't like ORMs, but I really don't like…

Id rather have the magic in an ORM than the database. Sometimes this desire to keep people as far as possible from understanding the database schema just makes everything much more complicated.

It's funny, that's exactly how I feel about ORMs! (That they're a way to create distance between the programmer and the database, when what you should have is understanding.)

Understanding the schema is important I agree - a great way to achieve that is to make the schema simpler to understand & to eliminate the need to for special case logic as much as possible.

In my view triggers like this are great, they're keeping the integrity rules you've established for your data ("though shalt not delete a record without creating a copy for observability and manual recovery by incident responders") out of your application code (where shifting requirements and much higher change rate put them in jeopardy) and enshrining them in the schema (where that can stay for decades, through rewrites etc.).

Re: Easy, alternative soft deletion: `deleted_record_insert`

#105

Post by isitmadeofglass »

quesera wrote: post #97
hgsgm wrote: post #94

Earlier quoted context omitted.

That's illegal in Europe, and unethical everywhere.

That depends on the industry. In finance or medicine, it's often illegal[0] to delete customer information before the end of an expiration period (minimum several years). There are ways to comply without soft-deletion, but in practice they are much more expensive to implement and never used. 0: "Illegal" is the wrong word, really "non-compliant with regulations, resulting in significant fines but except in case of gr…

> In finance or medicine, it's often illegal[0] to delete customer information before the end of an expiration period (minimum several years).

There are also laws that require you to delete customer information. For instance patient data in situations where consent is withdrawn. So either way you can’t rely on “it’s often”. And on the flip side of things the legal requirements you are referring to are typically the audit requirements, which are still better served using audit tables, since it’s not enough to have a “current data” and “data that is currently not active”, since you need logs of who changed the data when, and you can easily run into situations where something is soft deleted, then restored, then soft deleted again.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#106

Post by quesera »

isitmadeofglass wrote: post #105
quesera wrote: post #97

Earlier quoted context omitted.

That depends on the industry. In finance or medicine, it's often illegal[0] to delete customer information before the end of an expiration period (minimum several years). There are ways to comply without soft-deletion, but in practice they are much more expensive to implement and never used. 0: "Illegal" is the wrong word, really "non-compliant with regulations, resulting in significant fines but except in case of gr…

> In finance or medicine, it's often illegal[0] to delete customer information before the end of an expiration period (minimum several years). There are also laws that require you to delete customer information. For instance patient data in situations where consent is withdrawn. So either way you can’t rely on “it’s often”. And on the flip side of things the legal requirements you are referring to are typically the a…

This is true, and in practice the solution frequently ends up being a combination of soft deletion and audit logs.

Just pointing out that in some common cases, the goals of consumer data control are impossible to implement, due to conflicting requirements for record retention.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#109

Post by japanman425 »

hgsgm wrote: post #91
japanman425 wrote: post #75

What’s the actual use case for soft deletes. Audits? Have an audit log then. There’s little reason to have deleted but not really deleted items in your database.

What's a use case for not even allowing soft deletes?

Normal record keeping activities
Post reply on HN