Scheduled jobs

Zlatko Michailov <zmichailov@yahoo.com> writes:

I didn't find any documentation mentioning scheduled jobs. I assume
there is no such a feature yet. I would like to implement it if
someone helps me with the development process (I am brand new to
OpenSource projects).

Basically the feature should include scheduling function exeution at:
- postmaster startup
- postmaster shutdown
- a specified moment
- a time of the day/month/year
- recurring at a time interval

Use cron. I see no value in duplicating cron's functionality inside
Postgres.

Besides, both Oracle and Microsoft have the feature.

They can afford to expend developer time on inventing and maintaining
useless "features". We have very finite resources and have to be
careful of buying into supporting things that won't really pull their
weight.

regards, tom lane

On Mon, 12 May 2003, Tom Lane wrote:

Zlatko Michailov <zmichailov@yahoo.com> writes:

I didn't find any documentation mentioning scheduled jobs. I assume
there is no such a feature yet. I would like to implement it if
someone helps me with the development process (I am brand new to
OpenSource projects).

Basically the feature should include scheduling function exeution at:
- postmaster startup
- postmaster shutdown
- a specified moment
- a time of the day/month/year
- recurring at a time interval

Use cron. I see no value in duplicating cron's functionality inside
Postgres.

I was going to say use cron :)

Only cron can't handle some of those cases listed, but then one could always
patch one's own local installation of pg_ctl etc. to run things at startup and
shutdown.

I'm not sure how specified moment differs from time of day/month/year (except
can cron handle years?)

Besides, both Oracle and Microsoft have the feature.

They can afford to expend developer time on inventing and maintaining
useless "features". We have very finite resources and have to be
careful of buying into supporting things that won't really pull their
weight.

regards, tom lane

--
Nigel J. Andrews

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:

Only cron can't handle some of those cases listed, but then one could always
patch one's own local installation of pg_ctl etc. to run things at startup and
shutdown.

If you are using a typical init script setup, it's easy to add
additional operations to the init script's start and stop actions.

I'm a tad suspicious of adding on-shutdown actions anyway, as there's
little guarantee they would get done (consider system crash, postmaster
crash, etc).

regards, tom lane

Zlatko Wrote:

I didn't find any documentation mentioning scheduled jobs. I assume
there is no such a feature yet. I would like to implement it if
someone helps me with the development process (I am brand new to
OpenSource projects).

Basically the feature should include scheduling function exeution at:
- postmaster startup
- postmaster shutdown
- a specified moment
- a time of the day/month/year
- recurring at a time interval

Have you ever considered using cron?

- It is available on every Unix.
- It may readily be compiled for Cygwin.

It seems preposterous to imagine that reimplementing the functionality
of cron would significantly add to the functionality of PostgreSQL.

If you really want a unified way of accessing crontabs, then feel free
to write some plpgsql functions that are "wrappers" for cron...
--
(concatenate 'string "aa454" "@freenet.carleton.ca")
http://www.ntlug.org/~cbbrowne/unix.html
FLORIDA: Relax, Retire, Re Vote.

On Mon, 12 May 2003, Tom Lane wrote:

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:

Only cron can't handle some of those cases listed, but then one could always
patch one's own local installation of pg_ctl etc. to run things at startup and
shutdown.

If you are using a typical init script setup, it's easy to add
additional operations to the init script's start and stop actions.

I'm a tad suspicious of adding on-shutdown actions anyway, as there's
little guarantee they would get done (consider system crash, postmaster
crash, etc).

regards, tom lane

Absolutely. That's why you'd patch your startup/shutdown scripts. Adding it to
pg_ctl does enable those to kick the necessary stuff without requiring use of
the system's init scripts for manual control of the postmaster. When the
emphasis on the 'controlled' aspect of this is acknowledged then it's just a
toss up between editing pg_ctl or your own wrapper for it. I would go for my
own wrapper since then that still leaves the ability for pg_ctl to be used
_without_ kicking those startup/shutdown actions.

I believe this has arisen several times and each time there's been no
enthusiasm to stick cron into the core which I think is a reasonable stance.

--
Nigel J. Andrews

For windows, look up wincron, I used it back in the day. don't know if
it's still an up to date package, but it was great back when NT4.0 still
held some small attraction to me.

On Mon, 12 May 2003, Christopher Browne wrote:

-*- Tom Lane <tgl@sss.pgh.pa.us> [ 2003-05-12 22:18 ]:

Use cron. I see no value in duplicating cron's functionality inside
Postgres.

The biggest advantages I see:
- running tasks as a specific database user without having to store passwords on the server
- When deploying a database -- maintenance jobs can be created with SQL commands
- Not everybody have access to the server or don't have another machine to run it from

Just mentioning some pros I see -- I do agree with your point on resources and future maintenance.

--
Regards,
Tolli
tolli@tol.li

Quoth nandrews@investsystems.co.uk ("Nigel J. Andrews"):

I believe this has arisen several times and each time there's been
no enthusiasm to stick cron into the core which I think is a
reasonable stance.

I think it _would_ be kind of neat to set up some tables to contain
what's in the postgres user's crontab, and have a pair of stored
procedures to move data in and out. If you had some Truly Appalling
number of cron jobs, manipulating them in a database could well be a
great way to do it.

That is, of course, quite separate from having cron in the core. And
having a _good_ set of semantics for the push/pull is a nontrivial
matter...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne&gt; rate me
http://cbbrowne.com/info/nonrdbms.html
As Will Rogers would have said, "There is no such thing as a free
variable." -- Alan Perlis

On Mon, May 12, 2003 at 23:03:20 +0000,
Þórhallur Hálfdánarson <tolli@tol.li> wrote:

The biggest advantages I see:
- running tasks as a specific database user without having to store passwords on the server

You can do that now using ident authentication. For some OS's you can do
this using domain sockets and don't have to run an ident server.

-*- Bruno Wolff III <bruno@wolff.to> [ 2003-05-13 11:39 ]:

On Mon, May 12, 2003 at 23:03:20 +0000,
Þórhallur Hálfdánarson <tolli@tol.li> wrote:

The biggest advantages I see:
- running tasks as a specific database user without having to store passwords on the server

You can do that now using ident authentication. For some OS's you can do
this using domain sockets and don't have to run an ident server.

In most of my setups there is only a limited number of users on the system, but many other users in PostgreSQL. Creating a user on the system for every user I create in the DB and allowing him to run processes is not according to procedures I follow, and I believe that applies to more people.

--
Regards,
Tolli
tolli@tol.li

On Tue, May 13, 2003 at 12:40:41PM +0000, Þórhallur Hálfdánarson wrote:

In most of my setups there is only a limited number of users on the
system, but many other users in PostgreSQL. Creating a user on the
system for every user I create in the DB and allowing him to run
processes is not according to procedures I follow, and I believe that
applies to more people.

In this case you can put the passwords in ~/.pgpass, I think.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El sentido de las cosas no viene de las cosas, sino de
las inteligencias que las aplican a sus problemas diarios
en busca del progreso." (Ernesto Hernández-Novich)

-*- Alvaro Herrera <alvherre@dcc.uchile.cl> [ 2003-05-13 13:19 ]:

On Tue, May 13, 2003 at 12:40:41PM +0000, Þórhallur Hálfdánarson wrote:

In most of my setups there is only a limited number of users on the
system, but many other users in PostgreSQL. Creating a user on the
system for every user I create in the DB and allowing him to run
processes is not according to procedures I follow, and I believe that
applies to more people.

In this case you can put the passwords in ~/.pgpass, I think.

The suggestion on using ident was to eliminate the need for storing passwords in the first place...

--
Regards,
Tolli
tolli@tol.li

Yes. We need a system table to store scheduled jobs and a single external daemon to fire them up.

The example I have in mind is maintaining active sessions where my app maintains its own user
accounts. When a user logs in, a session row is created in a an app table. Every time a new
request comes through that session, a last_used timestamp is updated. At the same time there must
be a job checking that same table every minute for rows where the last_used timestamp is over 20
minutes old and remove such rows. Since the account registrations are inside the database, it
appeals to me that session maintenance should also be there.

Please think about it again. I can provide a table and SQL command (or stored proc) proposal.

Thanks,
Zlatko

--- Christopher Browne <cbbrowne@cbbrowne.com> wrote:

Quoth nandrews@investsystems.co.uk ("Nigel J. Andrews"):

I believe this has arisen several times and each time there's been
no enthusiasm to stick cron into the core which I think is a
reasonable stance.

I think it _would_ be kind of neat to set up some tables to contain
what's in the postgres user's crontab, and have a pair of stored
procedures to move data in and out. If you had some Truly Appalling
number of cron jobs, manipulating them in a database could well be a
great way to do it.

That is, of course, quite separate from having cron in the core. And
having a _good_ set of semantics for the push/pull is a nontrivial
matter...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne&gt; rate me
http://cbbrowne.com/info/nonrdbms.html
As Will Rogers would have said, "There is no such thing as a free
variable." -- Alan Perlis

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com

On Tue, May 13, 2003 at 01:33:25PM +0000, ??rhallur H?lfd?narson wrote:

The suggestion on using ident was to eliminate the need for storing
passwords in the first place...

But how are you going to let them run scheduled jobs inside the
postmaster if they can't be authenticated, then? You either have to
use .pgpass, user kerberos, or use ident; nothing else is safe in the
context you're discussing. I don't understand the problem.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

On Tue, May 13, 2003 at 07:18:52AM -0700, Zlatko Michailov wrote:

remove such rows. Since the account registrations are inside the
database, it appeals to me that session maintenance should also be
there.

I still don't see what this is going to buy which cron will not. And
what you're asking for is that the whole community pay the cost of
maintaining an expensive and redundant piece of functionality because
of your preference.

If you really want that, you can probably implement it yourself. I
know plenty of people (including me) who would argue very strongly
against putting this sort scheduling function inside any release of
PostgreSQL. It's a maintenance nightmare which adds nothing to cron;
moreover, it's a potential source of some extremely serious bugs,
including security vulnerabilities. Finally, the effort that might
be expended in maintaining a redundant piece of code like this is
something that could be expended instead on providing functionality
which is at present not available at all.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

-*- Andrew Sullivan <andrew@libertyrms.info> [ 2003-05-13 14:42 ]:

On Tue, May 13, 2003 at 01:33:25PM +0000, ??rhallur H?lfd?narson wrote:

The suggestion on using ident was to eliminate the need for storing
passwords in the first place...

But how are you going to let them run scheduled jobs inside the
postmaster if they can't be authenticated, then? You either have to
use .pgpass, user kerberos, or use ident; nothing else is safe in the
context you're discussing. I don't understand the problem.

I was simply pointing out some scenarios when scheduled jobs are nice. :-)

I believe you have to be authenticated to *create* jobs... and would probably run as the owner, if it gets implemented.

--
Regards,
Tolli
tolli@tol.li

That is, of course, quite separate from having cron in the core. And
having a _good_ set of semantics for the push/pull is a nontrivial

Sometimes I need some external action to be taken when a table is updated. I
can't find anything about how to do this with triggers.

The easiest would be to have a server listening on a port. But can I write a
PL/pgSQL trigger that can talk tcp/ip?

There is a short description about untrusted Perl. This might solve the
problem of talking to the port, but I'm not sure I would like to run anything
called "Untrusted" in my server!

--
Kaare Rasmussen --Linux, spil,-- Tlf: 3816 2582
Kaki Data tshirts, merchandize Fax: 3816 2501
Howitzvej 75 Åben 12.00-18.00 Email: kar@kakidata.dk
2000 Frederiksberg Lørdag 12.00-16.00 Web: www.suse.dk

On Tue, 2003-05-13 at 10:48, Þórhallur Hálfdánarson wrote:

-*- Andrew Sullivan <andrew@libertyrms.info> [ 2003-05-13 14:42 ]:

On Tue, May 13, 2003 at 01:33:25PM +0000, ??rhallur H?lfd?narson wrote:

The suggestion on using ident was to eliminate the need for storing
passwords in the first place...

But how are you going to let them run scheduled jobs inside the
postmaster if they can't be authenticated, then? You either have to
use .pgpass, user kerberos, or use ident; nothing else is safe in the
context you're discussing. I don't understand the problem.

I was simply pointing out some scenarios when scheduled jobs are nice. :-)

I believe you have to be authenticated to *create* jobs... and would probably run as the owner, if it gets implemented.

Wouldn't it make more sense to modify cron to be able to read scheduling
details out of the database -- rather than trying to modify PostgreSQL
to try to feed cron?

See examples of FTP, DNS, etc. software that can read authentication
elements from databases -- and the lack of DBs that have knowledge of
how to push data into those services.
--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

On Tue, May 13, 2003 at 04:55:01PM +0200, Reinoud van Leeuwen wrote:

One thing comes in mind: portability. Scripts and cron work different on
Unix compared to Windows or other platforms. Even cron is not the same on
al Unix variants.
When the scheduling system is inside the database, it works identical on
all platforms...

Unless, of course, they have used different versions of an
identically-named library. In which case you get different
performance anyway, and so then you end up writing a custom library
which is or is not the default for HP-UX version 9 when compiled with
certain options (see another recent thread about exactly such a
case).

That's exactly the sort of terrible maintenance problem that I can
see by implementing such functionality, and I can't see that it's
anywhere near worth the cost. Given that the behaviour of /bin/ksh
and cron are both POSIX, you can still rely on some standardisation
across platforms.

It seems to me that, if the price of supporting Windows is that
Postgres has to have its own cron, the cost is too high. I don't
believe that Postgres _does_ need that, however: a scheduling service
is available on Windows that's good enough for these purposes, and
you cannot really expect perfect portability between any flavour of
UNIX and Windows (as anyone who's had to support such a heterogenous
environment knows).

Of course it's true that if you re-implement every service of every
supported operating system yourself, you get a more portable system.
But in that case, perhaps someone should start the PostgrOS project.
(It's a database! No, it's an operating system! No, it's a
data-based operating environment! Wait. Someone already did that:
PICK. Nice system, but not SQL.)

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

On 13 May 2003, Rod Taylor wrote:

On Tue, 2003-05-13 at 10:48, Þórhallur Hálfdánarson wrote:

-*- Andrew Sullivan <andrew@libertyrms.info> [ 2003-05-13 14:42 ]:

On Tue, May 13, 2003 at 01:33:25PM +0000, ??rhallur H?lfd?narson wrote:

The suggestion on using ident was to eliminate the need for storing
passwords in the first place...

But how are you going to let them run scheduled jobs inside the
postmaster if they can't be authenticated, then? You either have to
use .pgpass, user kerberos, or use ident; nothing else is safe in the
context you're discussing. I don't understand the problem.

I was simply pointing out some scenarios when scheduled jobs are nice. :-)

I believe you have to be authenticated to *create* jobs... and would probably run as the owner, if it gets implemented.

Wouldn't it make more sense to modify cron to be able to read scheduling
details out of the database -- rather than trying to modify PostgreSQL
to try to feed cron?

See examples of FTP, DNS, etc. software that can read authentication
elements from databases -- and the lack of DBs that have knowledge of
how to push data into those services.

Bingo, Rod. You obviously reached across the miles into my head and stole
that from my brain, because honestly I was about 30 seconds from posting
the same thing.

Zlatko,

I think your proposal for a job scheduler is a great idea .... as a GBORG
project for PostgreSQL add-in. That is, I think you should develop it and
put it up at gborg.postgresql.org for others to use and modify.

However, making such an accessory part of the core distribution is not a good
idea; that amounts to forcing users who have no need of a job scheduler to
download and install one anyway. Particularly so since it seems to me that
such a program is better implemented *external* to PostgreSQL, using the
database merely as a data store for programmed and completed jobs. If you
look at the design of MS SQL Server, you will find that the "SQL Server
Agent" which handles scheduled jobs is an external service to the database
itself ... it merely uses msdb as its data store.

I look forward to hearing of your efforts.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Tolli wrote:

-*- Andrew Sullivan <andrew@libertyrms.info> [ 2003-05-13 14:42 ]:

On Tue, May 13, 2003 at 01:33:25PM +0000, ??rhallur H?lfd?narson wrote:

The suggestion on using ident was to eliminate the need for storing
passwords in the first place...

But how are you going to let them run scheduled jobs inside the
postmaster if they can't be authenticated, then? You either have to
use .pgpass, user kerberos, or use ident; nothing else is safe in the
context you're discussing. I don't understand the problem.

I was simply pointing out some scenarios when scheduled jobs are
nice. :-)

"Nice" does not dictate "Someone should be responsible for the
implementation."

In the old fable about the mice and the cat, it would sure be "nice" if
they could put a bell on the cat so the mice could hear the cat coming.
But in the fable, none of the mice were prepared to risk life and limb
getting the bell put onto the cat.

In this case, the fact that you'd like a scheduler does not imply that
anyone will want to take the job on.

I believe you have to be authenticated to *create* jobs... and would
probably run as the owner, if it gets implemented.

No, these "jobs" would run as the "postgres" user. (Or whatever user
it is that the PostgreSQL server runs as.)

And there enters a *big* whack of complexity, particularly if that
isn't the right answer.

It rapidly turns into a *very* complex system that, even with MS-SQL
and Oracle, isn't really part of the database. Why is it complex?
Because of the need to be able to change user roles to different
system users, which is inherently system-dependent (e.g. - very
different between Unix and Windows) and *highly* security-sensitive.

I agree with the thoughts that it would be a slick idea to come up
with a way of having PostgreSQL be the "data store" for some outside
scheduling tool. You likely won't have something that anyone will
have compete with Cron or Maestro or [whatever they call the Windows
'scheduler'], but it could be useful to those that care. And by
keeping it separate, those of us that don't care don't get a bloated
system.
--
(reverse (concatenate 'string "gro.gultn@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/spreadsheets.html
Think of C++ as an object-oriented assembly language.

Andrew Sullivan wrote:

Of course it's true that if you re-implement every service of every
supported operating system yourself, you get a more portable system.
But in that case, perhaps someone should start the PostgrOS project.
(It's a database! No, it's an operating system! No, it's a
data-based operating environment! Wait. Someone already did that:
PICK. Nice system, but not SQL.)

MaVerick apparently implements something Pick-like on top of
PostgreSQL... <http://www.maverick-dbms.org/articles/article1.html&gt;

And IBM Universe and UniData *do* make this into SQL...
http://www-3.ibm.com/software/data/u2/pubs/whitepapers/nested_rdbms.pdf

And it's also worth considering that we have array types that support
something Like MV, although that's quite a separate debate.

Or perhaps not; let me suggest the thought that it would be more
worthwhile to examine the notion of adding MV SQL keywords to PostgreSQL
than it would be to try adding a batch scheduler...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne&gt; rate me
http://www.ntlug.org/~cbbrowne/multiplexor.html
Rules of the Evil Overlord #47. "If I learn that a callow youth has
begun a quest to destroy me, I will slay him while he is still a
callow youth instead of waiting for him to mature."
<http://www.eviloverlord.com/&gt;

Hi

-*- Christopher Browne <cbbrowne@cbbrowne.com> [ 2003-05-13 20:38 ]:

Tolli wrote:
"Nice" does not dictate "Someone should be responsible for the
implementation."

In the old fable about the mice and the cat, it would sure be "nice" if
they could put a bell on the cat so the mice could hear the cat coming.
But in the fable, none of the mice were prepared to risk life and limb
getting the bell put onto the cat.

In this case, the fact that you'd like a scheduler does not imply that
anyone will want to take the job on.

As I said in my original reply to Tom: "Just mentioning some pros I see -- I do agree with your point on resources and future maintenance."

The point being, which I might have stated explicitly, that if someone (for example Zlatko who originally suggested it) will go on implementing it, I believe it helps is indeed good. Weather or not it should be included in the main distribution is a matter of a totally seperate debate later on. :-)

I believe you have to be authenticated to *create* jobs... and would
probably run as the owner, if it gets implemented.

No, these "jobs" would run as the "postgres" user. (Or whatever user
it is that the PostgreSQL server runs as.)

And there enters a *big* whack of complexity, particularly if that
isn't the right answer.

Eeek! What I've been thinking about all along is something for running, err, SQL (which therefor can be run as the owner) or some internal tasks -- nothing with external processes.

It rapidly turns into a *very* complex system that, even with MS-SQL
and Oracle, isn't really part of the database. Why is it complex?
Because of the need to be able to change user roles to different
system users, which is inherently system-dependent (e.g. - very
different between Unix and Windows) and *highly* security-sensitive.

I agree with the thoughts that it would be a slick idea to come up
with a way of having PostgreSQL be the "data store" for some outside
scheduling tool. You likely won't have something that anyone will
have compete with Cron or Maestro or [whatever they call the Windows
'scheduler'], but it could be useful to those that care. And by
keeping it separate, those of us that don't care don't get a bloated
system.

I sincerely agree that I'd not like to see PostgreSQL bloated with a cron-wannabe. ;-)

--
Regards,
Tolli
tolli@tol.li

OK, here's an idea. You write a set of stored procs that let you do
something like:

insert into batch_jobs ('..... I'm not sure what we'd put here...)

then, postgresql has a crontab entry that uses something like redhats
runparts script to run the SQL commands it finds in the table.

I.e. the jobs could be scheduled by something as simple as a query, and
removed as well. Just need a postgresql cron that runs every 5 minutes or
whatever resolution you need.

I worked on project at GBorg to implement:

- a specified moment
- a time of the day/month/year
- recurring at a time interval

But now the project is frozen for some time, exist only UML.

Expected features were:

1. All transactions from job spool performed in one connection serially.
So on multy processors servers spooled jobs will eat only one CPU.
And job performed in expected order.
2. Guarantee launch job from spool in none guarantee time, even if
server crashed when job transaction not ended.

Cron can't implement this.

--
Olleg