4 ms·
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’
by sa46 4y ago
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.
- jasfi 4y agoI 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.