Trigger for change in date for PostgreSQL database!

Question

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?

Trigger fire only on database changes. What exactly are you trying to accomplish? — Nordic Mainframe
– Nordic Mainframe, Commented Feb 8, 2011 at 16:55

Mike Sherrill 'Cat Recall' · Accepted Answer · 2011-02-08 17:35:25Z

2

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.

answered Feb 8, 2011 at 17:35

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tometzky · Accepted Answer · 2011-02-09 17:52:05Z

1

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.

edited Feb 9, 2011 at 17:52

answered Feb 9, 2011 at 11:10

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

2 Comments

manishKungwani Over a year ago

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?

manishKungwani Over a year ago

although not a neat solution, yet accepted as the best amongst the ones given.

Frank Heikens · Accepted Answer · 2011-02-08 16:56:33Z

0

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

answered Feb 8, 2011 at 16:56

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

1 Comment

manishKungwani Over a year ago

Yes, I need a notification of change in the server system date.

Collectives™ on Stack Overflow

Trigger for change in date for PostgreSQL database!

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related