Let me explain the scenario first before asking the question.
I have a database with a set of tables. One table, say Table A, contains username details, and other table, say Table B, contains user activity log. I want to delete the specific user log from Table B when that specific user entry is deleted from Table A. I can do this with a trigger, but there is a requirement that entries from Table B should be deleted after sometime (say after 5 minutes), not immediately.
Is it possible to delay trigger execution for 5 minutes? What could be the possible design for trigger. Whether deferred trigger will be helpful in this scenario.
Best Answer
No, deferred trigger execution will not help. It only defers triggers until the end of the transaction.
PostgreSQL doesn't have a built-in task/job scheduler, either, so you'll need to do this in your app.
In the trigger, insert a row into a table that keeps track of which entries should be deleted and when. Scan that table every few minutes and, in a single transaction, records that're past their expiry time and their corresponding entry in the delete queue table.