Soft deletion probably isn't worth it

Re:Soft deletion probably isn't worth it by kukx on Tuesday July 19, 2022 @03:34PM (#32156806)

Is not there any attempt to improve the soft deletion at the engine/SQL level? I can see it as a possible feature request.

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by smallnamespace on Tuesday July 19, 2022 @03:57PM (#32157107)

If you're using PostgreSQL, you can implement cascading soft-deletes yourself.

The information schema table holds all foreign key relationships, so one can write a generic procedure that cascades through the fkey graph to soft-delete rows in any related tables.

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by BeefySwain on Tuesday July 19, 2022 @04:35PM (#32157597)

Could someone take a stab at an example of what this would look like? Sounds really interesting.

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by NoInkling on Thursday July 21, 2022 @11:11AM (#32180340)

Here's an interesting approach using rules: https://evilmartians.com/chronicles/soft-deletion-with-postg...

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by smallnamespace on Tuesday July 19, 2022 @10:55PM (#32160811)

Here's a toy implementation:

https://www.db-fiddle.com/f/n3ux4s7mcZ2554738T14QR/2

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by simcop2387 on Tuesday July 19, 2022 @03:47PM (#32156978)

There's the idea of temporal tables, https://pgxn.org/dist/temporal_tables/

It's not a standard (I think) but it'd let you do a cascading delete and then be able to go and look at the old objects as they were at time of deletion too.

You'd need to do things very differently to show a list of deleted objects though.

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by dspillett on Tuesday July 19, 2022 @05:38PM (#32158317)

> temporal tables … It's not a standard

They were introduced in ANSI SQL 2011.

How closely implementations follow the standard I don't know, but something close exists in several DBMSs: I use them regualrly in MS SQL Server, there are plug-ins for postgres, MariaDB has them, and so forth.

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by psYchotic on Tuesday July 19, 2022 @05:53PM (#32158466)

It appears that there's been an attempt at standardizing temporal features in SQL in the SQL:2011 standard: https://en.wikipedia.org/wiki/SQL:2011

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by simcop2387 on Tuesday July 19, 2022 @09:06PM (#32160036)

Neat I didn't know that had happened. I can't say I follow SQL standards all that thoroughly.

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by sa46 on Wednesday July 20, 2022 @01:00AM (#32161604)

You lose traditional FK constraints with temporal tables since there's multiple copies of a row. One workaround is to partition current rows separately from historical rows and only enforce FK constraints on the current partition.

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by pbardea on Tuesday July 19, 2022 @05:07PM (#32157989)

One interesting feature that some DBs implement is something like SELECT AS OF SYSTEM TIME (https://www.cockroachlabs.com/docs/stable/as-of-system-time....) which _kinda_ does this.

However in practice this usually dramatically slows down reads if you have to constantly skip over the historic rows so you probably don't want to keep garbage round longer than absolutely necessary. The concept of a historic table mentioned below could be interesting though - especially if it could be offloaded to cold storage.

[ Parent | on HN ]

Re:Soft deletion probably isn't worth it by xwdv on Tuesday July 19, 2022 @03:42PM (#32156926)

Doubt it. It seems like something obvious yet I’ve waited so long for it. Seems like you have to rely on third party plugins.

[ Parent | on HN ]