News for hackers. Stuff that matters.

A comment on Soft deletion probably isn't worth it. Parent comment

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 ]