Deleting files from db
Hi, I want to delete files from the DB, so I will need to use pg net.
I want to delete files attached to tasks, when the task gets deleted. I was thinking 2 approaches. Sending the DELETE request on a trigger when the task gets deleted.
Or doing nothing and have a nightly cron job delete them.
I suppose the second one is better since the first one has no gaurentee since the request with pg net are fire and forget so cant rollback. But would love to hear your thoughts.
6 Replies
I would go with the nightly cron job using a queue as this doesn't block current task and can retry if it fails for whatever reason. So I would put the task of deleting the files in a queue that would be picked up by a nightly cron job, if the cron fails then the item remains on the queue, if it's successful it should remove the item from the queue.
Sounds like a pretty solid idea. Should I make the db sent a pg_net directly to the storage api or edge function. On one hand i think cut out the middle man, but on the other hand edge functions are often more robust
Maybe even skip the queue since SELECT 1 FROM api.subject_files WHERE storage_object_id = objects.id is quite cheap
Personally I'd go with the edge function for it's robustness.
Alright, thanks for sharing your thoughts with me!
That would work too. I just like the idea of the queue since it can perform retries if it should fail for whatever reason.
Would you mean retries the same day? Since the next day the cron job will run again and natrually retry since its not removed?
In the first case the queue is indeed pretty nice. But I dont mind if its there a day longer if it fails, and I am getting tiered of missing deadlines haha so think I am gonna query