Thread: Schedule Jobs in Postgres
Hi all,
My company ZONIAC INDIA PVT LTD is extensively using Pstgres as the
database for their product. And right now I'm in need to schedule a job that
inserts a record into a table based on the value of a field in another
table. How can I do this? Please let me know as soon as possible. It is
URGENT!!!.
I'm not talking about triggers because no other event is triggering this
particular job. This job must be scheduled so that as soon as the difference
between the value in a field and a particular date is reduced to 1 day, a
record is inserted in another table.
Eagerly awaiting your reply,
Thanx
Madhavi Daroor
> My company ZONIAC INDIA PVT LTD is extensively using Pstgres as the
> database for their product. And right now I'm in need to schedule a job that
> inserts a record into a table based on the value of a field in another
> table. How can I do this? Please let me know as soon as possible. It is
> URGENT!!!.
> I'm not talking about triggers because no other event is triggering this
> particular job. This job must be scheduled so that as soon as the difference
> between the value in a field and a particular date is reduced to 1 day, a
> record is inserted in another table.
I guess I don't understand the above.
It seems to me you have several possibilities here.
1. If what you are looking for is to do something when the column
value is updated to be within one day of the date in question, then
you have something which could be done with a trigger funcion.
2. If what you're looking for is something that happens in relation to
a change between the external date and an [unchanged] column
value, you may be able to accomplish this with a cron job that
does the time comparison. Would a daily or hourly or once-per-minute
cron job satisfy your stated requirement that this job be scheduled
'as soon as ....'
3. If neither of the above meets your conditions, you probably have a
task that is beyond pgsql's current state of development, as it is
a real-time task. As I recall, Oracle has the ability to schedule
jobs, but I don't recall the time granularity it uses.
--
Mike Nolan
3.
Dear Madhavi Daroor, I'm not sure about the existance of scheduling in Postgres. As I can undersand your needs, you want to do isertions when one of two fields are modified. If it's true, I would do the modifications by means of stored procedures, chacking the difference between the appropriate field values. Whith this method it can be garanteed that the insertion is immediatelly done after the condition is fulfilled. On the other hand you could apply the crontab. I hope I could help you. Best Regards, -- Csaba
On Wed, 14 May 2003, Madhavi Daroor wrote: > Hi all, > My company ZONIAC INDIA PVT LTD is extensively using Pstgres as the > database for their product. And right now I'm in need to schedule a job that > inserts a record into a table based on the value of a field in another > table. How can I do this? Please let me know as soon as possible. It is > URGENT!!!. > > I'm not talking about triggers because no other event is triggering this > particular job. This job must be scheduled so that as soon as the difference > between the value in a field and a particular date is reduced to 1 day, a > record is inserted in another table. You're likely gonna need to write a cron job that runs when the date changes, or whatnot. Then, you can run it with something like: psql dbname -c -- 'query goes here' Basically, making things happen on at a certain time is cron's job.
On Wed, 14 May 2003, scott.marlowe wrote: > On Wed, 14 May 2003, Madhavi Daroor wrote: > > > Hi all, > > My company ZONIAC INDIA PVT LTD is extensively using Pstgres as the > > database for their product. And right now I'm in need to schedule a job that > > inserts a record into a table based on the value of a field in another > > table. How can I do this? Please let me know as soon as possible. It is > > URGENT!!!. > > > > I'm not talking about triggers because no other event is triggering this > > particular job. This job must be scheduled so that as soon as the difference > > between the value in a field and a particular date is reduced to 1 day, a > > record is inserted in another table. > > You're likely gonna need to write a cron job that runs when the date > changes, or whatnot. Then, you can run it with something like: > > psql dbname -c -- 'query goes here' > > Basically, making things happen on at a certain time is cron's job. To add to this, it might work by writing a stored proc or something similar that does the checking, then schedule a cron job to run every 5 minutes or so and call that stored procedure. That way, most of the code is in postgresql, and all the cron job does is give it a wake up call every so often.
unless madhavi is running postgres in windoze.
cron is a reliable way of executing psotgresql tasks.
Some Tips:
1. I find it helpful to put the SQL related crons under
a dedicated user , so that i dont have to hunt around
to get the list of all scheduled SQLs.
2. If sql batch is to be run its better to use a .sql script
with all the SQL statements in it and enclosed in
a BEGIN WORK ; <SQLS> ; COMMIT;
3. MAILTO env variable shud be set to get error reports
on SQL activity.
4. -q or -e option can be with psql depending on need.
Regds
Rajesh Kumar Mallah.
> On Wed, 14 May 2003, Madhavi Daroor wrote:
>
>> Hi all,
>> My company ZONIAC INDIA PVT LTD is extensively using Pstgres as the
>> database for their product. And right now I'm in need to schedule a job that inserts a record
>> into a table based on the value of a field in another table. How can I do this? Please let me
>> know as soon as possible. It is URGENT!!!.
>>
>> I'm not talking about triggers because no other event is triggering this particular job. This
>> job must be scheduled so that as soon as the difference between the value in a field and a
>> particular date is reduced to 1 day, a record is inserted in another table.
>
> You're likely gonna need to write a cron job that runs when the date changes, or whatnot.
> Then, you can run it with something like:
>
> psql dbname -c -- 'query goes here'
>
> Basically, making things happen on at a certain time is cron's job.
>
>
> ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off
> all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/