CODEWITHSUNDEEP
sqlpostgresql
Enguias
Enguias

Reputation: 73

How to make the database delete a row after certain time on the row?

I'm doing a project about reporting situations on a map. Each report is marked by a pin on the map. The pin table has an attribute, report_time, that saves the hour in which the pin was inserted (or in which the report was made).

How can I make a function/trigger/procedure (whatever is the best option) to delete that row after 15 minutes since the report? I want something on my database that gets the report_time and deletes the row if 15 minutes have passed.

Upvotes: 0

Views: 1140

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Instead of constantly deleting rows, how about just using a view?

create view v_pin as
    select p.*
    from pin p
    where p.report_time > now() - interval '15 minute;

Anyone who uses the view will only see the most recent rows.

Then, when the database is quiescent or when you want -- say once an hour or once a day or once a week, just schedule a job to remove old rows:

delete from pin
    where report_time < now() - interval '15 minute';

What is elegant about this solution is that users will never see inappropriate rows in the table, even if the job does not run as scheduled or the table is locked for updates for some reason.

Upvotes: 3

Related Questions