Hacker News Reader

Comment by moehm | Hacker News Reader

Isn't this something your application solves with just another column? How can Postgres help you with this?

Yes, you can, except you have to constantly remember that column exists and adjusts queries accordingly so you don’t get back deleted results or modify deleted rows. It would be nice if that was abstracted away and you simply added some other keyword to a query when you want to deal in deleted objects.

I think the problem is there are too many trade offs Postgres would have to make.

- should soft-deleted rows move to another partition to avoid bloat?

- what’s the column name and type (boolean, timestamp, tstzrange)?

- what’s the syntax for a soft delete versus hard delete?

- what’s the behavior of updating a soft deleted row?

- do soft deleted keys prevent inserts with the same key?

Bitemporal tables mentioned in the thread are a more general solution. Alternately, you could use a statement-based trigger to prevent deletes to the table.

I can think of one possible solution: - Soft deleted rows should be moved to a shadow table, so that they can have their own unique ID and timestamp fields. - What's the column name and type of? - Well, you'd have to extend SQL, but that's done regularly. DELETE (PERMANENTLY | AS MOVE TO SHADOW) with permanently the default. - Not a good idea to update a soft deleted row, I'd prevent it with roles if possible. - No, because the shadow table has its own primary key, and shouldn't have any unique constraints on it because of this design.

Reply on news.ycombinator.com