Easy, alternative soft deletion: `deleted_record

Re: Easy, alternative soft deletion: `deleted_record_insert`

#71

Post by paulryanrogers » Sun, Jan 01, 2023, 2:05 PM UTC

bastawhiz wrote: post #70

porsager wrote: post #66

Setting up row level security policies to exclude rows `where deleted_at is not null` solves most of the issues with the discarded solution. Of course it would be crazy to have a system where you will need any extra where clauses for the default queries being made. You can even make a simple function that sets a statement level variable checked in the rls policy so that deleted rows can be included or only deleted ro…

Excluding the rows still doesn't solve problems with foreign keys (you can't DELETE CASCADE and instead have to iterate all relationships manually). It also means you still need to remember to consider deleted_at when doing things like setting up unique indexes.

> It also means you still need to remember to consider deleted_at when doing things like setting up unique indexes.

Considering soft deleted for uniqueness can be a feature, especially if one has a restore feature. Though I agree it can be easily overlooked if you want to exclude them, such as forgetting to use COALESCE with some reserved value. (Otherwise null will make the whole constraint always unique.)

Re: Easy, alternative soft deletion: `deleted_record_insert`

#72

Post by drittich » Sun, Jan 01, 2023, 2:23 PM UTC

hdra wrote: post #27

rubyfan wrote: post #8

I did something similar to this many years ago but didn’t limit to deleted records and instead created an audit log with a very similar approach. It worked nicely and provided a view into how data was changing by our users.

I considered this for an audit log as well, but ran into a roadblock in terms of associating the operation with a logged in user who triggered the change.. how did solve that particular issue? or was it not a requirement in your case?

We do this in SQL Server by running a SET CONTEXT_INFO command every time we open a connection. This makes the current user ID available to any SQL that runs.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#73

Post by fb03 » Sun, Jan 01, 2023, 2:36 PM UTC

I wrote a specific library to do this (automatically generates a mixin for SQLAlchemy and installs a hook which rewrites all queries, removing soft-deleted items from queries and also relationships) so that the soft-delete problem becomes a non issue and you still have the data there if you want to revert/activate something

https://github.com/flipbit03/sqlalchemy-easy-softdelete

Re: Easy, alternative soft deletion: `deleted_record_insert`

#74

Post by revskill » Sun, Jan 01, 2023, 2:45 PM UTC

In most case, i just see deletion as change status from `active` to `deleted`. You should never remove the actual record from database.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#75

Post by japanman425 » Sun, Jan 01, 2023, 2:46 PM UTC

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.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#76

Post by douge1 » Sun, Jan 01, 2023, 3:14 PM UTC

I’ve recently put together an awesome list about temporality, including: soft delete, time travel, slowly changing dimensions, and bitemporality. https://github.com/daefresh/awesome-data-temporality

Re: Easy, alternative soft deletion: `deleted_record_insert`

#77

Post by gavinray » Sun, Jan 01, 2023, 3:19 PM UTC

yen223 wrote: post #37

TylerE wrote: post #23

Earlier quoted context omitted.

Bwttwr yet, encode the logic in a view and run your queries against the view. DRY

In postgres you cannot make a foreign-key reference to a view, which effectively means you can't prevent another table from referencing a soft-deleted record.

I'm not trying to shill for the company I work at, but in Hasura, you can make FK's and relationships between views and treat them like regular insertable/updatable tables.

It was one of the many things that impressed me so much when I was a user that made me want to hack on the tool for a living.

So, this problem of soft-deletes becomes trivial

Re: Easy, alternative soft deletion: `deleted_record_insert`

#78

Post by lazide » Sun, Jan 01, 2023, 3:36 PM UTC

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.

‘Recycle bin’ type functionality for data is usually highly desirable for customers.

They want the ability to undelete stuff they accidentally delete, since accidental deletes happen all the time.

True for consumer users. Super super true for enterprise users.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#79

Post by ttfkam » Sun, Jan 01, 2023, 4:09 PM UTC

ivank wrote: post #35

https://github.com/xocolatl/periods periods does this for PostgreSQL.

Great solution. Unfortunately only works on installations that support extensions, which excludes most managed database services like AWS RDS.

I like how you tried to track the standard as close as possible. I've seen (and written) ad hoc solutions that hard-code too much or mandate certain columns to be present.

That said, unlike the standard and most other RDBMSs, Postgres supports range types. Seems a shame to rely simply on two timestamptz columns when one tstzrange should suffice.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#80

Post by LudwigNagasena » Sun, Jan 01, 2023, 4:10 PM UTC

If you want to delete something, delete it.

If you want to restore something, get it from a backup.

If you want to delete something, but you fear that it will ruin something in your db because the architecture is a mess and you are not really sure what references what and what will break, then soft-delete it.

But what is the point of this?