Sheduler in Postgres

cron works pretty well in unix. Scheduled tasks on windows have, in my
experience, been on the flakey side.

On Tue, 7 Dec 2004, [iso-8859-2] Egy�d Csaba wrote:

The world rejoiced as csegyud@vnet.hu (Egy�d Csaba) wrote:

Hi, I should schedule the execution of several stored
procedures. Now I use an NT service for this, but as far as I know
e.g. the Oracle has such a thing. It would be great if I could fire
procedures on a timer basis.

Is there a better solution for this than mine?

Traditionally, PostgreSQL has consciously omitted such things where
they would merely be replicating existing operating system
functionality.

On Unix, "cron" is the traditional service that provides this
functionality.

I think there's a port to Windows NT, so you could presumably use that
if you haven't got any more "native" job scheduler.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/lsf.html
"How should I know if it works? That's what beta testers are for. I
only coded it." (Attributed to Linus Torvalds, somewhere in a
posting)

Thank you Ben, I already have known these tools. I was wonder if Postgres
has an integrated tool for scheduling sp's (like Oracle has!). OK it doesn't
have. No problem just tried to make world more simpler... :)

Bye,
-- Csaba

-----Original Message-----
From: Ben [mailto:bench@silentmedia.com]
Sent: Friday, December 10, 2004 11:29 PM
To: Együd Csaba
Cc: 'Pgsql-General@Postgresql.Org'
Subject: Re: [GENERAL] Sheduler in Postgres

cron works pretty well in unix. Scheduled tasks on windows have, in my
experience, been on the flakey side.

On Tue, 7 Dec 2004, [iso-8859-2] Együd Csaba wrote:

Hi,
I should schedule the execution of several stored procedures. Now I
use an NT service for this, but as far as I know e.g. the Oracle has such

a thing.

It would be great if I could fire procedures on a timer basis.

Is there a better solution for this than mine?

Many thanks,

-- Csaba

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03.

---------------------------(end of
broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03.

OK, thank you. Yes we have a more native tool - the "at" service on NT/2K.

I wanted to boundle it into the application because I don't like to place
parts of the application outside the application. I wrote a scheduler
service sepcialy for the applications tasks. It works fine, but I thought
this should be inside the server.

Thak you,
-- Csaba

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Christopher Browne
Sent: Saturday, December 11, 2004 5:09 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Sheduler in Postgres

The world rejoiced as csegyud@vnet.hu (Együd Csaba) wrote:

Hi, I should schedule the execution of several stored procedures. Now
I use an NT service for this, but as far as I know e.g. the Oracle has
such a thing. It would be great if I could fire procedures on a timer
basis.

Is there a better solution for this than mine?

Traditionally, PostgreSQL has consciously omitted such things where they
would merely be replicating existing operating system functionality.

On Unix, "cron" is the traditional service that provides this functionality.

I think there's a port to Windows NT, so you could presumably use that if
you haven't got any more "native" job scheduler.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/lsf.html
"How should I know if it works? That's what beta testers are for. I
only coded it." (Attributed to Linus Torvalds, somewhere in a
posting)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03.

Traditionally, PostgreSQL has consciously omitted such things where
they would merely be replicating existing operating system
functionality.

On Unix, "cron" is the traditional service that provides this
functionality.

I think there's a port to Windows NT, so you could presumably use that
if you haven't got any more "native" job scheduler.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/lsf.html
"How should I know if it works? That's what beta testers are for. I
only coded it." (Attributed to Linus Torvalds, somewhere in a
posting)

On Tue, 2004-12-14 at 07:49, Christopher Browne wrote:

Traditionally, PostgreSQL has consciously omitted such things where
they would merely be replicating existing operating system
functionality.

On Unix, "cron" is the traditional service that provides this
functionality.

I think there's a port to Windows NT, so you could presumably use that
if you haven't got any more "native" job scheduler.

I haven't administered a Winbox in a few years, but I remember wincron
being quite good back in the day. It's free, but registration gets you
support.

http://www.wincron.com/index.html

No, PostgreSQL doesn't have an equivalent to Oracle's dbms_job, but this
is a question that comes up pretty often. I think the bulk of the work
to make this happen could be done outside of the core database, so it
seems a good candidate for pgfoundry.

On Sat, Dec 11, 2004 at 07:44:03AM +0100, Egy?d Csaba wrote:

Thank you Ben, I already have known these tools. I was wonder if Postgres
has an integrated tool for scheduling sp's (like Oracle has!). OK it doesn't
have. No problem just tried to make world more simpler... :)

Bye,
-- Csaba

--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Wed, 15 Dec 2004, Jim C. Nasby wrote:

No, PostgreSQL doesn't have an equivalent to Oracle's dbms_job, but this
is a question that comes up pretty often. I think the bulk of the work
to make this happen could be done outside of the core database, so it
seems a good candidate for pgfoundry.

What's wrong with at, cron, and some trivial shell scripting?
(This is a real question, I wonder what advantages dbms_job has.)

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it

The only advantage would be that an in-database solution would be OS
independent and it could be managed using the same tools which manage
the database itself, including the backup and management of it. I'm not
sure how the Oracle thing is working, but I suppose you can manage it
using plain SQL. This makes for a more homogeneous solution.
Using cron makes your database solution OS dependent, and if you want to
programatically manage the tasks, then your program will be also OS
dependent.
This is about the advantages I can see of an integrated scheduling
service. That said, you can always shift that in your middle-tier (if
you have a 3 tier system), possibly backed by some DB tables (this is
how we do our scheduling here).

Cheers,
Csaba.

On Thu, Dec 16, 2004 at 10:47:46 +0100,
Csaba Nagy <nagy@ecircle-ag.com> wrote:

The only advantage would be that an in-database solution would be OS
independent and it could be managed using the same tools which manage
the database itself, including the backup and management of it. I'm not
sure how the Oracle thing is working, but I suppose you can manage it
using plain SQL. This makes for a more homogeneous solution.
Using cron makes your database solution OS dependent, and if you want to
programatically manage the tasks, then your program will be also OS
dependent.
This is about the advantages I can see of an integrated scheduling
service. That said, you can always shift that in your middle-tier (if
you have a 3 tier system), possibly backed by some DB tables (this is
how we do our scheduling here).

cron isn't really part of the OS. Up until 8.0, any OS that Postgres
ran on had cron. I have seen claims that there is a version of cron that
runs under windows, but haven't verified that. Given this I don't see
how a dependence on cron is going to cause you portability problems.

Well, if you program in Java for example, it is a portability problem to
call any native programs. It doesn't matter at all that the program
itself is portable, it is just not portable enough to call it at all,
not to mention other robustness problems arising from the Java-native
interaction.
Completely portable is nothing, but a solution which only requires you
to call your data base library with plain SQL is a lot more portable (in
any language you might use) than calling external programs.

Cheers,
Csaba.

Bruno Wolff III wrote:

cron isn't really part of the OS. Up until 8.0, any OS that Postgres
ran on had cron. I have seen claims that there is a version of cron that
runs under windows, but haven't verified that. Given this I don't see
how a dependence on cron is going to cause you portability problems.

I would prefer the development effort be applied to more specific
database engine issues and enhancements. As Bruno has noted, you can
get some variation of cron on virtually every OS that runs Postgresql.

--
Until later, Geoffrey

--- Bruno Wolff III <__> wrote:

On Thu, Dec 16, 2004 at 10:47:46 +0100,
Csaba Nagy <nagy@ecircle-ag.com> wrote:

The only advantage would be that an in-database solution would be

OS

independent and it could be managed using the same tools which

manage

the database itself, including the backup and management of it. I'm

not

sure how the Oracle thing is working, but I suppose you can manage

it

using plain SQL. This makes for a more homogeneous solution.
Using cron makes your database solution OS dependent, and if you

want to

programatically manage the tasks, then your program will be also OS
dependent.
This is about the advantages I can see of an integrated scheduling
service. That said, you can always shift that in your middle-tier

(if

you have a 3 tier system), possibly backed by some DB tables (this

is

how we do our scheduling here).

cron isn't really part of the OS. Up until 8.0, any OS that Postgres
ran on had cron. I have seen claims that there is a version of cron
that
runs under windows, but haven't verified that. Given this I don't see
how a dependence on cron is going to cause you portability problems.

the cron you mention is www.wincron.com. I'm just started testing it...
seems a nice enough tool.

Here are my two cents on a sheduling engine:
I agree that a scheduling engine inside pgsql could be a useful tool,
and the advantage is that, even if cron may be available on all the
platforms pg runs, there are portability issues. The code cron runs may
(and it will probably will) have to be changed for each platform. On
the other side, having a sheduling engine inside postgresql would help
keeping the code I develop using postgres portable inside postgresql.
No matter on what platform I decide to setup postgresql, I know that
the triggers, functions and other stuff works.

regards,

R.

No doubt about this, there are a lot of features which are way more
important, but this was not the point at all.

The question was if there is any advantage of having it in the DB, and
the answer is: yes there is some advantage. It's a totally different
problem how important it is - there are many ways to handle scheduled
processes.

I would myself vote with leaving it out in favor of other features (my
favorit is shared row level locks, or anything else it would take to
finally solve the too strong lock for FK check problem, which makes
inserts in tables with FKs deadlock prone). Not that I could influence
at all the development through contributions, nor funding...

Cheers,
Csaba.

Csaba Nagy wrote:

No doubt about this, there are a lot of features which are way more
important, but this was not the point at all.

The question was if there is any advantage of having it in the DB, and
the answer is: yes there is some advantage. It's a totally different
problem how important it is - there are many ways to handle scheduled
processes.

Sure, and there's an advantage to traveling the speed of light, but
you've got to set your priorities.

I would myself vote with leaving it out in favor of other features

Agreed.

--
Until later, Geoffrey

On Thu, Dec 16, 2004 at 10:12:46AM +0100, Marco Colombo wrote:

On Wed, 15 Dec 2004, Jim C. Nasby wrote:

No, PostgreSQL doesn't have an equivalent to Oracle's dbms_job, but this
is a question that comes up pretty often. I think the bulk of the work
to make this happen could be done outside of the core database, so it
seems a good candidate for pgfoundry.

What's wrong with at, cron, and some trivial shell scripting?
(This is a real question, I wonder what advantages dbms_job has.)

I don't know about dbms_job, but I implemented a simple cron-a-like in
postgresql for one of my apps so that database clients can schedule
events. The clients don't have access to a shell on the DB server,
it's a completely separate security domain. And the clients may not
even be powered up when an event is due to fire, so running cron there
isn't going to work either.

Cheers,
Steve

Csaba Nagy <nagy@ecircle-ag.com> writes:

The only advantage would be that an in-database solution would be OS
independent

That kind of argument leads to monstrosities like Oracle that reimplement
everything they can from the OS.

What would be a neat project is if someone wrote a cron implementation that
used postgres as a backend. You could run cron from the command-line and edit
your cron jobs normally, but the resulting data would get stuffed into a
postgres database. The cron daemon would connect to the database to get its
information.

This would be neat since it would let applications access and modify the data
through a well defined programmatic interface, but the data could easily be
queried using SQL to display in a nice GUI or HTTP interface.

I suspect this would be very attractive to shared hosting systems. It would
also be convenient for applications that want to provide more restricted
access to application-specific routine jobs.

This wouldn't really be a part of Postgres though, just another application
using Postgres. It could be something Postgres could recommend for people who
find cron too awkward for their application.

--
greg

After a long battle with technology, gsstark@mit.edu (Greg Stark), an earthling, wrote:

This wouldn't really be a part of Postgres though, just another
application using Postgres. It could be something Postgres could
recommend for people who find cron too awkward for their
application.

This has considerable merit.

One thing that is unfortunate about cron is that it provides little
verifyable feedback. It logs some things, sort of...

A "cron implementation using PostgreSQL as data store" would have a
wonderfully natural place to record log information in a usefully
structured fashion.

When a job runs, it would be a splendid idea to record such things as:
- Job ID (perhaps an OID, or some other candidate primary key)
- PID
- Start time
- End time
- Exit code

Given all of the above, a job might look at the logs and
self-terminate if there's another instance still running from last
hour.

Jobs that are supposed to be mutually exclusive could detect as much.

You could _attempt_ to run a job every hour, and have it decide "Oh,
I've already run successfully in the last [interval], so I'll not
bother."

None of this means forcing it into the database implementation; it
just means that it would be useful. "pgcron" sounds like an utterly
splendid idea.
--
output = ("cbbrowne" "@" "gmail.com")
http://www3.sympatico.ca/cbbrowne/x.html
"Applicants must have *at least* five years experience with Windows
XCVIII..."

See my comments below.

On Thu, 2004-12-16 at 21:01, Greg Stark wrote:

Csaba Nagy <nagy@ecircle-ag.com> writes:

The only advantage would be that an in-database solution would be OS
independent

That kind of argument leads to monstrosities like Oracle that reimplement
everything they can from the OS.

Now, cron isn't exactly part of the OS, is it ?
I wouldn't care if the postgres implementation would use cron
internally, as long as I can control it through plain SQL. The argument
was that the data base could hide the OS, not that it should not use it,
nor reimplement it.

What would be a neat project is if someone wrote a cron implementation that
used postgres as a backend. You could run cron from the command-line and edit
your cron jobs normally, but the resulting data would get stuffed into a
postgres database. The cron daemon would connect to the database to get its
information.

This actually is a very nice idea. It has the distinct advantage that it
doesn't have to be limited to Postgres, it could connect to any data
base, or any other data store providing the needed functionality.