4 ms·

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

by kukx 4y ago

smallnamespace 4y ago
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.
- BeefySwain 4y ago
  Could someone take a stab at an example of what this would look like? Sounds really interesting.
  - smallnamespace 4y ago
    Here's a toy implementation: https://www.db-fiddle.com/f/n3ux4s7mcZ2554738T14QR/2 https://www.db-fiddle.com/f/n3ux4s7mcZ2554738T14QR/2
  - NoInkling 4y ago
    Here's an interesting approach using rules: https://evilmartians.com/chronicles/soft-deletion-with-postgresql-but-with-logic-on-the-database https://evilmartians.com/chronicles/soft-deletion-with-postg...
xwdv 4y ago
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.
simcop2387 4y ago
There's the idea of temporal tables, https://pgxn.org/dist/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.
- sa46 4y ago
  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.
- dspillett 4y ago
  > 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.
- psYchotic 4y ago
  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 https://en.wikipedia.org/wiki/SQL:2011
  - simcop2387 4y ago
    Neat I didn't know that had happened. I can't say I follow SQL standards all that thoroughly.
pbardea 4y ago
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.html 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.

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

by kukx 4y ago

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

smallnamespace 4y ago

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.

BeefySwain 4y ago
Could someone take a stab at an example of what this would look like? Sounds really interesting.
- smallnamespace 4y ago
  Here's a toy implementation: https://www.db-fiddle.com/f/n3ux4s7mcZ2554738T14QR/2 https://www.db-fiddle.com/f/n3ux4s7mcZ2554738T14QR/2
- NoInkling 4y ago
  Here's an interesting approach using rules: https://evilmartians.com/chronicles/soft-deletion-with-postgresql-but-with-logic-on-the-database https://evilmartians.com/chronicles/soft-deletion-with-postg...

xwdv 4y ago

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.

simcop2387 4y ago

There's the idea of temporal tables, https://pgxn.org/dist/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.

sa46 4y ago
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.
dspillett 4y ago
> 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.
psYchotic 4y ago
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 https://en.wikipedia.org/wiki/SQL:2011
- simcop2387 4y ago
  Neat I didn't know that had happened. I can't say I follow SQL standards all that thoroughly.

pbardea 4y ago

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.html 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.