Updating data when fetching

Question

Is it possible to fetch data and update it if a time constraint has been met? Originally, my implementation was a cron job that regularly updated the data in my PostgreSQL table every minute on the minute after selecting all the rows that met the time constraint. (Meaning the job always ran at 12:00, 12:01, 12:02, etc)

However, I now have to include a new use case that there could be data to be updated every second, but not always. (Meaning the constraint can not be on 12:00:00, 12:00:46 or 12:12:12) The straightforward answer here would be to modify the cron job, but a cron running every second on a live database doesn't seem prudent, and maybe not possible?

My first proposed solution was to trigger an update based on the time constraint whenever a user fetches data

Backend fetch -> DB select -> DB trigger update -> DB returns data -> Backend returns data

but Postgre does not allow for trigger functions on SELECT statements.

My second alternative is to simply do a separate call to update the data whenever it is fetched.

Backend fetch -> DB select -> Backend update data -> DB update data -> Backend returns data

But while both of these solutions gives the illusion of real-time updates (such as if a user refreshes before and after the time constraint), neither of these solutions actually update the data until it is fetched, which ends up becoming kind of Schrodinger-y.

My third and final proposal was to include the frontend into it

Backend fetch -> Backend returns data -> Frontend timer -> Backend fetch when constraint is met

But I feel like that introduces more variables, so I'm currently leaning the most towards solution #2. However, I feel like I might be overlooking an obvious solution and am trying to over-engineer a simple problem, so I am open to all and any resources or solutions.

Thank you!

• Ewan: your question in unclear. what is the source of the change? make that push the update

Answer 1

It sounds to me that you want records to "expire" after a certain amount of time.
If that's the case, then calculate and store the expiry date/time in the record as it is saved. That way, you don't need to update anything on a regular basis.

You simply retrieve the record and have your application react according to whether or not the expiry time has passed.

Database updates can be expensive and slow, and you cannot guarantee that they will happen on the lovely, regular basis that you need.

• Nerdragen: This is the solution I ended up going with! I was going to answer my own question, but I'll mark your reply as the answer instead