Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Easy, alternative soft deletion: `deleted_record_insert`

brandur.org

Post reply on HN
Print view View original

51–60 of 109 posts

Re: Easy, alternative soft deletion: `deleted_record_insert`

#51

Post by simonw »

duskwuff wrote: post #40
username1407 wrote: post #39

Earlier quoted context omitted.

Can't you use json operators [1] for this? -- Permanently delete message sent by jon DELETE from deleted_record where table_name = 'messages' and data->>'sender' = 'jon'; [1] https://www.postgresql.org/docs/15/functions-json.html

That assumes that all of a user's data is a single foreign key relationship away from the user, which often isn't the case. It also requires you to scan the entire table and decode JSON for every row you've ever deleted, which may become prohibitively expensive for large databases.

PostgreSQL can create indexes on the results of expressions, so it's possible to accelerate queries that select against a nested value inside a JSON blob.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#52

Post by skissane »

yen223 wrote: post #37
TylerE wrote: post #23

Earlier quoted context omitted.

Bwttwr yet, encode the logic in a view and run your queries against the view. DRY

In postgres you cannot make a foreign-key reference to a view, which effectively means you can't prevent another table from referencing a soft-deleted record.

I wish Postgres had something like a "CHECK contraint" on a foreign key.

There is a hack of sorts. You create a duplicate of the primary key column, named e.g. id_active. You create a CHECK constraint which says something like "(status = 'Deleted' AND id_Active IS NULL) OR (status 'Deleted' AND id_active = id)". You create a unique index on "id_active", and point your foreign key to that. When you create a record, populate both id and id_active to same value; when you soft-delete it, set id_active to NULL. Actually, maybe a simpler solution is to make id_active a "GENERATED ALWAYS AS ... STORED" column–although I'm not sure if Postgres supports them for foreign keys? That's a relatively new Postgres feature and I haven't done much yet with the more recent versions in which that feature was added.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#54

Post by egeozcan »

eitland wrote: post #43
piyh wrote: post #21

JSONB seems like something I need to put a few nights into understanding, but my time is short and filled with diapers and bottles.

Good to see you have your priorities sorted. Sincerely, a dad with 5 kids.

Got my first one nearly 2 months ago. How are you still alive? I'm joking of course... But really, how?

Re: Easy, alternative soft deletion: `deleted_record_insert`

#55

Post by chewyfruitloop »

ducaale wrote: post #28
AlexeyMK wrote: post #19

Earlier quoted context omitted.

You can get around the "check in every transaction" problem with an ORM, but now you're (more) coupled to your ORM which you will occasionally inevitable need to circumvent for something or other. And now you've made it (more of a) leaky abstraction.

or you can create view for every table that supports soft deletion and ensure all of your read-only queries are using those table CREATE VIEW current_customers AS SELECT * FROM customers where deleted_at is null; SELECT * FROM current_customers JOIN ... Of course, this comes with its downsides e.g. views need to be recreated in every migration and there might be some complex join operations that might not work.

It may be work sometime in the future, but having to prefix every query with a null check sounds bonkers. A view can be optimised to do all that for you. I made the what the flip expression reading that they did a prefix on everything.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#56

Post by aidos »

skissane wrote: post #52
yen223 wrote: post #37

Earlier quoted context omitted.

In postgres you cannot make a foreign-key reference to a view, which effectively means you can't prevent another table from referencing a soft-deleted record.

I wish Postgres had something like a "CHECK contraint" on a foreign key. There is a hack of sorts. You create a duplicate of the primary key column, named e.g. id_active. You create a CHECK constraint which says something like "(status = 'Deleted' AND id_Active IS NULL) OR (status 'Deleted' AND id_active = id)". You create a unique index on "id_active", and point your foreign key to that. When you create a record, po…

I’m also not sure about the foreign key restrictions on generated columns (and glancing at the docs I don’t see anything about it on there) but for all intents and purposes they are real columns so I’d imagine it probably works. Apparently they run after the before triggers, I’m not totally sure where foreign keys are checked but probably after that?

As an aside, they’re a great feature. We’re using them to generate columns that we can index for efficient joins between tables and also for creating text strings for searching over using trigram indexes. The whole thing is really seamless.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#57

Post by jbverschoor »

AlexeyMK wrote: post #25
TylerE wrote: post #23

Earlier quoted context omitted.

Bwttwr yet, encode the logic in a view and run your queries against the view. DRY

Sure, but you need an "undeleted" view per table now - I haven't seen an ORM offer maintaining those views capability out of the box - any to look at?

Don’t forget versions of the schema, and data that is not migrated to the new schema

Re: Easy, alternative soft deletion: `deleted_record_insert`

#59

Post by simcop2387 »

aidos wrote: post #56
skissane wrote: post #52

Earlier quoted context omitted.

I wish Postgres had something like a "CHECK contraint" on a foreign key. There is a hack of sorts. You create a duplicate of the primary key column, named e.g. id_active. You create a CHECK constraint which says something like "(status = 'Deleted' AND id_Active IS NULL) OR (status 'Deleted' AND id_active = id)". You create a unique index on "id_active", and point your foreign key to that. When you create a record, po…

I’m also not sure about the foreign key restrictions on generated columns (and glancing at the docs I don’t see anything about it on there) but for all intents and purposes they are real columns so I’d imagine it probably works. Apparently they run after the before triggers, I’m not totally sure where foreign keys are checked but probably after that? As an aside, they’re a great feature. We’re using them to generate…

I haven't tested it but it might be the case that it'd need to be a stored generated column to be referenced like that but that shouldn't be a big deal.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#60

Post by [deleted] »

brightball wrote: post #18
no_wizard wrote: post #9

Earlier quoted context omitted.

To take a stab at it: This is one of those things where business requirements trump the technical implementation details. Prevailing theory is that actual deletes are bad because you can’t do historical analysis, recovery etc on the data. Say a customer stops using a service for a year but comes back: it’s a big win if you can (at least optionally) restore their data, so the theory goes. That’s why tricks like this e…

Another is the support case “Help! I accidentally deleted the wrong thing!” where it saves a huge amount of time compared to loading up a full DB backup.

[deleted]
Post reply on HN