CODEWITHSUNDEEP
sqldatabasepostgresqldatetriggers
manishKungwani
manishKungwani

Reputation: 925

Trigger for change in date for PostgreSQL database!

How shall I write a trigger which will be fired when the LocalDate of the PostgreSQL database server changes? I want a notification when the LocalDate (the date of the postgresql database server) is changed, which will be the most effective way to do that? The application which needs the notification is written in Java.

Further clarification: I am building Public Transportation system, and i need to set the schedule of the day, at the start of every day. And the day is according to the time of the database server. How do I achieve this with minimum overhead?

Upvotes: 0

Views: 1720

Answers (3)

Tometzky
Tometzky

Reputation: 23890

There's not a way to do this only using database without periodic checking.

If you need notification in a client application then LISTEN/NOTIFY can be used. You'd need to configure server scheduler (for example cron) to launch a script which connect to a server at 00:00 and issue a NOTIFY. But this will need a Java client to periodically check for PQnotifies, which is not better than just checking current_date periodically.

What do you need this for? Maybe there's a better solution to your underlining problem.

Your only sensible option is to create a program on server which does your daily maintenance and schedule it using server operating system scheduler (cron on Unices, Windows Task Scheduler on Windows).

For extra reliability:

  • set your maintenance script to lock a special table maintenance ( last_maintenance date )
  • when a client connects to a database it should check if current_date=last_maintenance and error out if it isn't;
  • if currently maintenance is running then it would just wait on lock until it's done.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95582

The only thing I can think of is to have a scheduled job (server-side job) insert the current date into a table once a day at, say, 12:01 am local time. A trigger could respond to that.

Upvotes: 2

Frank Heikens
Frank Heikens

Reputation: 127086

What is "LocalDate" ? You mean the server system date? In that case it's not possible, triggers fire on record changes in a table. Check the manual: http://www.postgresql.org/docs/current/static/plpgsql-trigger.html

Upvotes: 0

Related Questions