CODEWITHSUNDEEP
postgresqldebian
mgttlinger
mgttlinger

Reputation: 1435

Run program on times specified in database

Project (only for context)

I am currently developing a booking solution where customers can book resources from a webapp by dragging events into a calendar (also able to move, edit, delete) in a google calendar like fashion. The persistence layer of my webapp stores the reservations in a table on postgres 9.3 with an start and end (as timestamp with timezone because the users/customers will be in different timezones). This part workes fine. The webapp runs on Debian and works with Play! written with scala if that matters.

Problem

Now the problem I am trying to solve is that on the start time of the reservation a program needs to be run to configure client access to the resource (and on the stop time as well to disable access). Also it needs to get parameters passed or at least get informed why it was started (to make it clear if multiple resources are booked at this time) somehow. The configuration program will be written in java or scala if that matters and will run on a different machine than the database.

EDIT: It does not need a GUI or anything it just needs to get run.

What I already read

I read something about LISTEN and NOTIFY but am not sure about how I would go about using this to start my program. I am very new to the whole topic of databases. Is this even the right way to go?

tl;dr

I try to run a program on times stored in a postgres database and dont know how.

Upvotes: 1

Views: 159

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324395

You need an external cron job or other periodic task scheduler that queries the table and decides whether something needs to run. If you're in Java/Scala you could do it with a simple Thread that sleeps during idle periods. Or whatever timing framework Play! includes internally - which looks like asynchronous jobs in Play! 1.2; there'll be something similar in 2.x if you're using that. This answer might help you out: https://stackoverflow.com/a/16982421/398670

You can have the program sleep for short periods then poll for new notifications on a PostgreSQL connection and re-check the schedule when it gets a notification. When the schedule table changes in the database, have a trigger send a NOTIFY so the polling client knows to update its copy.

The polling program can modify its sleep intervals so it sleeps until the exact time that an event is supposed to occur, at which point it launches the specified command.

PgJDBC doesn't offer a callback when a NOTIFY comes in, so you can't sleep until the next event or until woken by callback. You'll have to do short sleeps and then wake up to poll for notifications using PGConnection.getNotifications()

Upvotes: 2

Related Questions