How to subscribe to the date aging trigger event and call NOTIFY?

Question

PostgreSQL

Hello!

I make a transactional system (payments for the store, services, transfers of bonuses). The architecture is microservice, transactions (payments) are distributed. By "transaction" in this context, I mean not the DBMS of the transaction, but the transactions / payments themselves. The actual, final status of the transaction in general in the partner's systems, such as acquiring, therefore, to find out if the transaction was successful, you have to water the partner's systems. It is necessary to water the status according to a certain algorithm, the longer the transaction is processed, the less often it is watered. For example, make the first request after 1 second, the second after 3, the third after 5, the fifth after 15, the sixth after a minute. Now I'm just doing poller, the task is not new for me, I already did it at my last job, then I did everything at the application level. Roughly speaking, my application was launched along the crown and flickered every second.

Now I don’t like this solution and I want to do without selects at all, I want the database itself to report about the records for which it is necessary to request the status. Something like a trigger on next_check_at <= NOW(), which will call NOTIFYthe , to which my application is subscribed. I also want the field to be incremented by this trigger for each found record next_check_at, on the trail. a timestamp based on the current polling iteration.

Googling led me to the idea that there are no triggers for the onset of a date. But I really do not want to implement all the described logic in the application. Of the more or less interesting tools in this area, I googled pg_cron and pgpro_scheduler. The first - do not understand what kind of extension and it will be difficult for me to drag it from the architect, the second is only for postgresPro.

Advise, please, the decision satisfying my requirements or any successful compromise.

Thank you.

UPD!

Now I thought that I can write a function poll_outdated_recordsthat will select by condition next_check_at <= NOW()and, if such records are found, will make UPDATEtheir fields next_check_atfor the next time point, and then call NOTIFYon these records. At the cron level, I can simply configure how often I want to call the function poll_outdated_records: once a second, 5 seconds, a minute ...

But I would like to clarify from experts how normal this is? How optimal?

Answer 1

once every 10 minutes, select records with an obsolete date and do whatever you need with them.