1

From UI user will give us some specific time (with timezone )for updating the exe version on the devices which we are storing in postgresql db as demandedInstallationTime.

 -------------------------------------------------------------
 | Id | DeviceName | demanded Installation Time | Other data  |
 -------------------------------------------------------------

Instead of polling in Java for every minute to check the table if there is any entry with 'demandedInstallationTime' and then invoke installation, I am thinking from postgresql tools. like pg_notify() but that works when there is Insert/Update/Delete of row of the table.

Cron jobs will also need to be scheduled to run either minutely or hourly, etc. continuously 24/7 which is not feasible in our case.

I wanted some thread to notify/trigger some script automatically when 'demandedInstallationTime' column equals 'current time'. How can we achieve this? Any suggestions?

Elletlar
  • 3,136
  • 7
  • 32
  • 38
Rekha
  • 11
  • 2
  • I don't think this is possible with database means. You need some kind of scheduled job. – Laurenz Albe Oct 15 '20 at 09:31
  • for PostgreSQL on Amazon RDS? using some CloudWatch Events and lambda functions? I am new to AWS so not sure , just started analyzing on that part. Please let me know your views too. Thanks in advance! – Rekha Oct 16 '20 at 05:38
  • I don't know Amazon's services. – Laurenz Albe Oct 16 '20 at 06:01

1 Answers1

-1

You could use a trigger written in say PL/Perl or PL/Python (other languages also available) which execute an external script of some kind. You'd need to carefully consider any possible performance/security implications of doing this kind of thing, of course.

Alternatively you could write a script which runs as a daemon and checks for updates (e.g. in a queue table written by a trigger on the table you're interested in) or which listens for notifications. If you're feeling really adventurous you could write a custom background worker, but that would probably be unnecessarily complex for the desired purpose.

Ian Barwick
  • 141
  • 5