Thread: Scheduled jobs
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 I know this could be implemented in exernal processes but from an application standpoint it would be much more consistent if all the database-related functionality is in the database server. Besides, both Oracle and Microsoft have the feature. Please advise. Thanks, Zlatko __________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com
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
-*- 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 serveror 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
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: > 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. > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
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> 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. Creatinga user on the system for every user I create in the DB and allowing him to run processes is not according to proceduresI 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> 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.
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> 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> 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/>
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 futuremaintenance." The point being, which I might have stated explicitly, that if someone (for example Zlatko who originally suggested it) willgo on implementing it, I believe it helps is indeed good. Weather or not it should be included in the main distributionis 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 willeat 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