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