Hacker News Reader

Comment by KronisLV | Hacker News Reader

I don't get why all of the big RDBMSes (PostgreSQL, MariaDB/MySQL, SQL Server, Oracle, ...) don't seem to have built in support for soft deletes up front and center?

  CREATE TABLE ... WITH SOFT DELETES

Where the regular DELETE wouldn't get rid of the data for real but rather you could query the deleted records as well, probably have timestamps for everything as a built in low level feature, vs having to handle this with a bunch of ORMs and having to remember to put AND deleted_at IS NULL in all of your custom views.

If we like to talk about in-database processing so much, why don't we just put the actual common features in the DB, so that toggling them on or off doesn't take a bunch of code changes in app, or that you'd even be able to add soft deletes to any legacy app that knows nothing of the concept, on a per table basis or whatever.

View on HN

Maybe my intuition is wrong, but to me this sounds like a violation of the principle of least power.

"Soft deletes" is just a name for a regular write operation, with specific semantics.

Adding a layer of magic to the DB for this doesn't seem right to me.

And applications could have many different requirements for soft deletes, like the article points out. For example, the simplest version would be just a boolean "deleted" column, but it could also be "deleted_at", "deleted_by", etc.

All of these cases require an bunch of code changes anyway, and the more complex ones could interfere with an implementation of this feature at the database level: such a transparent implementation couldn't access app-specific concerns such as user data, for example.

Adding soft deletes to a legacy app that knows nothing about it would only work for a boolean flag and a maybe date-time value, unless the DBMS would also offer triggers for soft deletes etc?

Seems to me to that this capability would make a DBMS much more complicated.

I don't think it's all magic because you have to implement soft deletes in your application layer and it would be nice to have a little DB support for it. It doesn't have to be some big thing, just the ability for selects and such to work only on undeleted elements by default unless you ask for them would be nice so it doesn't pollute your code and make you have to always remember to point the gun away from your foot.

I'd argue that what SQL needs is better facilities for code reuse, metaprogramming and such, it ought to give you the tools that you can make something that lets you add something to the language such that you can add

   ... WITH SOFT UPDATES

and it adds to the table definition as well as to the schema that will cause subsequent statements to be rewritten. There's a lot of interesting logic (in the literal sense) in SQL that is hidden by a strange, irregular syntax that is more obvious in other approaches to databases such as Datalog. I think it was little appreciated outside the hardcore semantic web community that you could compile SPARQL + OWL to SQL and get powerful inference facilities. SQL is a great target for metaprogramming precisely because it is not Turing complete and that a code generator doesn't have to think at all about the order that events are sequenced in. It's kinda sad that metaprogramming tools for SQL are almost all pre-Chomsky and pre-dragon book internal DSLs like JooQ and SQLAlchemy which have their charms (JooQ's excellent integration with Java IDEs) but fall short of what could be done with SQL-to-SQL and SQL-to-X transformations.

Stored procedures are great but many shops don't use them for various reasons. It doesn't help that they look like a mix of FORTRAN and COBOL and also come in a few variations from the (better) set-based PL/SQL of Oracle to the (worse) Transact-SQL based stored proc of Microsoft SQL and PostgresSQL. The other day I talked with Krisztián Szabó of

https://schemamap.io/

who developed a compiler that writes stored procs that do database synchronization.

On the other hand, if you've got access to the internals of the frickin' database I think you can do something better than the ordinary application level soft updates. For instance a "customer record" might very well be not just a row in one table but maybe 15 rows in four tables that are inserted in a transaction and you want to be able to undelete them as a unit.

>Adding soft deletes to a legacy app

As an aside, I've never found this to be worth it since you have to change too much and re-test everything for minimal user benefit and time savings. The effort is way worse if the code is not great in the first place. It can be a great decision to make before everything is written.

Maybe it's worth it for files which are hard to reproduce, but you can also rely on DB backups to get those back. If people are regularly deleting things they're not supposed to, you're better off removing the user-facing delete actions, limiting the action to specific users, etc.

The whole model of RDBMS is based on mutable tuples; soft deletes don't make much sense as an intrinsic part of that model. If you want soft deletes, you create an application layer or use a different data model.

Most of the time if you want "soft deletes", you really want an immutable log so that you time travel to any point in the history. XTDB and Datomic are worth looking at if you want to solve the problem at the data model level.

Because it's too dependent on business logic.

Different products will handle soft deletes differently. Which queries need to include soft-deleted rows and which don't? What about different levels of soft deletes, e.g. done by the user (can be undone by user) vs. done by an admin (can't be undone by user)?

Implementing soft deletes yourself isn't hard. Yes you'll have to make a bunch of decisions about how they work in every circumstance, but that's the point.

Temporal tables in SQL server fit this use-case[0], I think.

0: https://learn.microsoft.com/en-us/sql/relational-databases/t...

Available on postgres as an extension. It's a bit jank and doesn't have language integrated clauses like sql server.

Temporal Tables and their query language were formalized in ANSI SQL 2011 standard, so hopefully it is only a matter of time and resources to see it added into the postgres core directly.

Indeed...it's why I still pick MS SQL for certain projects.

Oracle has flashback queries (SELECT ... AS OF timestamp).

It's one of these things that are available but most people ignore it and implement it manually with created_at updated_at deleted_at columns etc. I think one reason for this is lack of support in ORMs and lack of standardization between RDBMSes.

Temporal Tables are ANSI Standard today (from SQL 2011). So far the closest to Standard is (surprisingly) Microsoft's SQL Server, but hopefully more of the DBs will pick up the standard sooner rather than later.

Also System-Versioned Tables in MariaDB: https://mariadb.com/kb/en/system-versioned-tables/

It's just not bothersome enough to deviate from the standard.

If they did this, nobody would use it. They do lots of more useful things that people don't use because it's not portable.

There's a sibling comment about temporal databases. Those solve a very bothersome problem, so a few people use them. That means that there's a chance soft deletes get adopted as a side effect of a much more complex standard.

old problem. in postgresql many ways already exist to keep deleted data hanging around, not the least of which is explicity archiving the delete transaction in an archive table. for legacy code that can not be changed: triggers, row level security, tailing logical replication log.

Oracle has this already. SELECT ... AS OF timestamp.

It needs to be enabled of course and it's not free.

Reply on news.ycombinator.com