Thread: Scheduling Events?
Is there anyway to schedule DB Events based on time? So lets say I had a table w/ depreciation schedules in it, I would like the DB to apply the formula and make the entries on the END of every month.
On Thu, Jan 23, 2003 at 04:16:52PM -0800, David Durst wrote: > Is there anyway to schedule DB Events based on time? > So lets say I had a table w/ depreciation schedules in it, > I would like the DB to apply the formula and make the entries on the END > of every month. 1) Write a script that invokes psql with the appropriate queries 2) Schedule a cron job to the end of the month Each tool doing its own small task well done. -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Junior, quit playing with your floppy!
On a side note, if the DB doesn't support this capability.
Does anyone see a HORRIBLE issue w/ creating a C func
something of this nature.
int handle_temporal_events() { if(fork == 0) { //In here we monitor what time it is //And maintain a Datastructure
w/events //And update it every so often //Then preform various background tasks } else if(fork == -1) { //Thread
error}
}
On Thu, 23 Jan 2003, David Durst wrote: > Is there anyway to schedule DB Events based on time? Yes! cron > So lets say I had a table w/ depreciation schedules in it, > I would like the DB to apply the formula and make the entries on the END > of every month. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
> On Thu, 23 Jan 2003, David Durst wrote: > >> Is there anyway to schedule DB Events based on time? > > Yes! cron > >> So lets say I had a table w/ depreciation schedules in it, >> I would like the DB to apply the formula and make the entries on the >> END of every month. > On Thu, 23 Jan 2003, David Durst wrote: > >> Is there anyway to schedule DB Events based on time? > > Yes! cron > >> So lets say I had a table w/ depreciation schedules in it, >> I would like the DB to apply the formula and make the entries on the >> END of every month. Here is the basic problem w/ using CRON in an accounting situation. I can't be sure that cron will always be up when the DB is up, so lets say crond goes down for some random reason (User, System error, Etc..) And outside adjustment is made to lets say the equipment account and that adjustment was made on the value of the equipment, BUT it hadn't been depreciated because crond went down and no one notice. Now I have a HUGE issue! So I have to be sure that all entries/adjustments are made accurately in the time frame they were meant to happen in.
On Fri, 24 Jan 2003, David Durst wrote: > > On Thu, 23 Jan 2003, David Durst wrote: > > > > Here is the basic problem w/ using CRON in an accounting situation. > > I can't be sure that cron will always be up when the DB is up, > so lets say crond goes down for some random reason (User, System error, > Etc..) > I you cannot depend on your system to run crond then you should not depend on it to run postgresql either. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
On Fri, Jan 24, 2003 at 00:45:38 -0800, David Durst <ddurst@larubber.com> wrote: > > I can't be sure that cron will always be up when the DB is up, > so lets say crond goes down for some random reason (User, System error, > Etc..) One option would be to run the cron job fairly often and have it check if there are any tasks that need to be done. If there are it does them in a transaction which also makes some update that indicates that the task has been done. The other option would be that the process(es) that use the monthly updates, check to see that they have been done as part of their transaction and if not do the update first before proceeding.
Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> On Fri, 24 Jan 2003, David Durst wrote:
>> Here is the basic problem w/ using CRON in an accounting situation.
>>
>> I can't be sure that cron will always be up when the DB is up,
>> so lets say crond goes down for some random reason (User, System error,
>> Etc..)
> I you cannot depend on your system to run crond
> then you should not depend on it to run postgresql either.
Indeed. Cron is one of the oldest and most basic Unix daemons.
I find it really, really, really hard to believe that any substitute
code that anyone might come up with is going to be more reliable than
cron.
If it makes you feel better, you could institute some cross-checking.
For example, have the cron-launched task update a timestamp in some
database table whenever it finishes. Then make your user applications
check that timestamp when they start up (or every so often) and complain
if it's not within the range (now - expected cron frequency, now).
That doesn't fix the problem, but at least makes some humans aware of it.
regards, tom lane
Or if you are so paranoid about the stability of crond, you can probably do a check to see whether crond is up when you update the database. If crond is up then update else mail root the error reject the update end This is going to affect the performance dramatically though. ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Achilleus Mantzios" <achill@matrix.gatewaynet.com> Cc: "David Durst" <ddurst@larubber.com>; <pgsql-sql@postgresql.org> Sent: Friday, January 24, 2003 10:32 AM Subject: Re: [SQL] Scheduling Events? > Achilleus Mantzios <achill@matrix.gatewaynet.com> writes: > > On Fri, 24 Jan 2003, David Durst wrote: > >> Here is the basic problem w/ using CRON in an accounting situation. > >> > >> I can't be sure that cron will always be up when the DB is up, > >> so lets say crond goes down for some random reason (User, System error, > >> Etc..) > > > I you cannot depend on your system to run crond > > then you should not depend on it to run postgresql either. > > Indeed. Cron is one of the oldest and most basic Unix daemons. > I find it really, really, really hard to believe that any substitute > code that anyone might come up with is going to be more reliable than > cron. > > If it makes you feel better, you could institute some cross-checking. > For example, have the cron-launched task update a timestamp in some > database table whenever it finishes. Then make your user applications > check that timestamp when they start up (or every so often) and complain > if it's not within the range (now - expected cron frequency, now). > That doesn't fix the problem, but at least makes some humans aware of it. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
Hi I would agree that cron is probably the best solution. You could have cron perform a query that has a trigger and performs all the tasks you need done. As well you could create a trigger on other queries that would perform the other things as well, but make sure it isn't a heavily used query but instead a query that is run hourly or daily. As a backup for cron you could manualy or using "anacron" or somthing similar run the query cron should run on a regular basis, but you should make sure your trigger keeps an entry in your database letting the other queries know when the update is started and when it has finished. Using this check ensures you don't get "overlapping" updates, and can also give you a clue to how much time the updates are taking and possibly alert you to a "hung" update. Guy
here is a possible NON-Cron solution that a friend of mine came up w/ 1) Create a table w/ scheduled events and Account Ids attached to them. 2) Create a table w/ temporal event execution timestamps. 3) On journal entry check to see if there any schedule events for the Account 4) Check timestamp table for last execution If Last execution is out of range force execution Else continue as normal This is passive but it should allow for data integrity w/ out the need of a external system.
> > Yes! cron > > > Here is the basic problem w/ using CRON in an accounting situation. > > I can't be sure that cron will always be up when the DB is up, > so lets say crond goes down for some random reason (User, System error, > Etc..) > > And outside adjustment is made to lets say the equipment account and that > adjustment was made on the value of the equipment, BUT it hadn't been > depreciated because crond went down and no one notice. > > Now I have a HUGE issue! > > So I have to be sure that all entries/adjustments are made accurately in > the time frame they were meant to happen in. > It seems that you have a good concern, so I have a suggestion. First, let me say that if you cannot count on cron to run your stuff at a certain time, then you cannot count on anything to run your stuff at a certain time. All of your reasoning for distrusting cron is perfectly valid in distrusting every conceivable automated system. Therefore, you have to design your application with the assumption that your scheduling system is untrustworthy. If you do that, then you have the freedom to use cron (or some other scheduling system) and build checks into your database activities to ensure that invalid data cannot be used if your scheduled processes did not take place. If you don't want to make changes to existing code, then you can create a solution as simple as a rule on your essential table(s) that first checks to make sure the most recent scheduled task was completed successfully and if it hasn't completed return something that the client will understand as invalid. If you're unfamiliar with "rules", they essentially rewrite your query on the fly. To quote Bruce Momjian's book, PostgreSQL: Introduction and Concepts, "Rules allow actions to take place when a table is accessed. In this way, they can modify the effects of SELECT, INSERT, UPDATE, and DELETE." I'm sure that you can think of several acceptable solutions if you learn to distrust your data. -- Matthew Nuzum www.bearfruit.org cobalt@bearfruit.org
On Thursday January 23 2003 5:16, David Durst wrote: > Is there anyway to schedule DB Events based on time? If you're using one of the unices (linux, etc.), how about... crontab + perl + DBI + DBD::Pg? or crontab + bash/sh + psql + pl/pgsql?