5

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.

5

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.

  • Thanks for the suggestion. I have created a job scheduler for this problem. Whenever the app deletes the record from table A, then at same time it schedules a new job using scheduler. The newly created job deletes the records for specific user from Table B after required time. – Tanish Sep 19 '14 at 5:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.