Thread: Can we have time based triggers in Postgresql??
Dear Group,
I would like to know if can have a time based
trigger, for example a procedure that could be run everyday at say 10 in
the night. Thanking you,
Yours sincerely,
Shan.
--- Shanmugasundaram Doraisamy <shan@ceedees.com> wrote: > Dear Group, > I would like to know if can > have a time based > trigger, for example a procedure that could be run > everyday at say 10 in > the night. Thanking you, > > Yours sincerely, > > Shan. > I'm not aware of any such facility in Postgres (although I'm a relative newcomer). However, you can easily run sql scripts using the psql commandline tool. Combined with the cron daemon under *nix (or the task scheduler in Windows) you should be able to easily execute these scripts any time you want. Regards, Shelby Cain __________________________________ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html
> I would like to know if can have a time based > trigger, for example a procedure that could be run everyday at say 10 in > the night. Thanking you, Cron?
> I would like to know if can have a time based > trigger, for example a procedure that could be run everyday at say 10 in > the night. Thanking you, Isn't this what cron is for? Just set it up so it invokes pg with a command line query. Chris
Matthew T. O'Connor wrote: >> I would like to know if can have a time based >>trigger, for example a procedure that could be run everyday at say 10 in >> the night. Thanking you, > > > Cron? I think he probably means like an Oracle job. Although cron works, that would be handy so you wouldn't need to write wrapper scripts just to run a proc.
On Tue, Mar 30, 2004 at 12:16:50PM -0500, Steve Manes wrote:
> I think he probably means like an Oracle job. Although cron works, that
> would be handy so you wouldn't need to write wrapper scripts just to run
> a proc.
I hate to sound like an oldbie crank (although I'll admit to being a
crank), but what exactly is the advantage supposed to be here? One
invents a new special bit of database code which exists just so people
don't have to write shell scripts? I guess the idea gets under my
skin just because I have enough time-based problems without inventing
a new interface to make it more complicated. Hmm. Looks like the
sigmonster has gacked up a piece of wisdom.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie
Andrew Sullivan wrote: > On Tue, Mar 30, 2004 at 12:16:50PM -0500, Steve Manes wrote: > >>I think he probably means like an Oracle job. Although cron works, that >>would be handy so you wouldn't need to write wrapper scripts just to run >>a proc. > > I hate to sound like an oldbie crank (although I'll admit to being a > crank), but what exactly is the advantage supposed to be here? One > invents a new special bit of database code which exists just so people > don't have to write shell scripts? I guess the idea gets under my > skin just because I have enough time-based problems without inventing > a new interface to make it more complicated. The only real advantage I see are for those people in hosting environments that don't really have access to cron. I suppose it's also valuable in making your scripts more portable as you wouldn't need to make further alterations to the system beyond restoring a database backup or whatnot. In all, I think PostgreSQL has bigger fish to fry. Greg
Gregory Wood wrote: >>> I think he probably means like an Oracle job. Although cron works, >>> that would be handy so you wouldn't need to write wrapper scripts >>> just to run a proc. >> >> I hate to sound like an oldbie crank (although I'll admit to being a >> crank), but what exactly is the advantage supposed to be here? One >> invents a new special bit of database code which exists just so people >> don't have to write shell scripts? I guess the idea gets under my >> skin just because I have enough time-based problems without inventing >> a new interface to make it more complicated. > > The only real advantage I see are for those people in hosting > environments that don't really have access to cron. I suppose it's also > valuable in making your scripts more portable as you wouldn't need to > make further alterations to the system beyond restoring a database > backup or whatnot. > > In all, I think PostgreSQL has bigger fish to fry. I tend to agree with this. As familiar as probably most of us with cron, it is: a) an external tool (which means in some environment people don't get access to it); b) Unix-only (yes, there are ports or clones on Win32 etc, but still the whole thing is Unix-oriented, like one crontab per *Unix user* instead of database user, using SHELL=... etc); c) reporting/status is by e-mail only. In short, it would be nice if we have a scheduling system where one can run queries or in-process procedures/functions instead of external commands. Also where one can use normal SQL/psql commands to manipulate the jobs; cron entries aren't exactly "programmable" or "structured" (e.g. how do you easily find entries that runs at least once every 10 min or more often?) -- dave
Just my experience, but we when I have used Oracle as my database. My
processes that run in batch use shell scripts to kick of stored procedures
even though Oracle does provide jobs. Shell scripts can be much more
flexible in many cases. You can have dependancies on all kinds of things
(time, a file arriving, after system backups are done, etc). Granted you
could in spend time doing this from yoru favorite stored procedure langauge
but why make things more complex then they need be.
Not that jobs built into the database are of no value, but shell scripts
seem to do everything you need and when there are other things to put into
posgtresql I wouldn't vote to waste time on jobs.
|---------+---------------------------------->
| | Andrew Sullivan |
| | <ajs@crankycanuck.ca> |
| | Sent by: |
| | pgsql-general-owner@pos|
| | tgresql.org |
| | |
| | |
| | 04/06/2004 10:59 PM |
| | |
|---------+---------------------------------->
>------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: pgsql-general@postgresql.org
|
| cc:
|
| Subject: Re: [GENERAL] Can we have time based triggers in Postgresql??
|
>------------------------------------------------------------------------------------------------------------------------------|
On Tue, Mar 30, 2004 at 12:16:50PM -0500, Steve Manes wrote:
> I think he probably means like an Oracle job. Although cron works, that
> would be handy so you wouldn't need to write wrapper scripts just to run
> a proc.
I hate to sound like an oldbie crank (although I'll admit to being a
crank), but what exactly is the advantage supposed to be here? One
invents a new special bit of database code which exists just so people
don't have to write shell scripts? I guess the idea gets under my
skin just because I have enough time-based problems without inventing
a new interface to make it more complicated. Hmm. Looks like the
sigmonster has gacked up a piece of wisdom.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may
containproprietary, confidential and/or privileged information. If you are not the intended recipient, any use,
copying,disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient,
pleasenotify the sender immediately by return e-mail, delete this communication and destroy all copies.
*************************************************************************
> Not that jobs built into the database are of no value, but shell scripts > seem to do everything you need and when there are other things to put into > posgtresql I wouldn't vote to waste time on jobs. I wonder if Oracle's time-based jobs feature came to be as a result of some other uses for that code, such as snapshots? Time-based jobs wouldn't make my top 10 wish list either, though if I had them, I'd probably use them. The security aspects of them could be important to some users or potential users. Using cron either forces one to have passwords out there in plaintext in the .pgpass file or to use a 'trusted' username that could also be a major security hole. Also, a script-based job can be changed or deleted by someone with the right file permissions even though they may not have database permissions, and vice versa. -- Mike Nolan
On Wed, Apr 07, 2004 at 08:25:32AM -0500, Mike Nolan wrote: > The security aspects of them could be important to some users or > potential users. Using cron either forces one to have passwords out > there in plaintext in the .pgpass file or to use a 'trusted' username > that could also be a major security hole. > > Also, a script-based job can be changed or deleted by someone with the > right file permissions even though they may not have database permissions, > and vice versa. Is there any particular reason why someone couldn't write a pgcron that works exactly like cron except it reads its data from a database. Like, say, a CRONTAB table with all the right columns. If each user has a schema then the existance of user.CRONTAB would be jobs for that user. Why build it into the database if it can be done just as well externally. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > This space intentionally left blank
Attachment
David Garamond <lists@zara.6.isreserved.com> writes: > In short, it would be nice if we have a scheduling system where one can run > queries or in-process procedures/functions instead of external commands. Also > where one can use normal SQL/psql commands to manipulate the jobs; cron > entries aren't exactly "programmable" or "structured" (e.g. how do you easily > find entries that runs at least once every 10 min or more often?) There's no reason you couldn't write a cron daemon that retrieved its list of jobs from an SQL database. In fact we discussed doing exactly this at my last job. I expect others have thought of the same idea and wouldn't be surprised if it existed by now. -- greg
> There's no reason you couldn't write a cron daemon that retrieved its list of > jobs from an SQL database. In fact we discussed doing exactly this at my last > job. I expect others have thought of the same idea and wouldn't be surprised > if it existed by now. Is there (or would we need) a way to specify access privileges for such a daemon in pg_hba.conf, and would that allow controlling the access privileges for each job or the user who sets it? (ISP's would want tight controls here.) If the answer to both questions can be 'yes', this probably gives us most of what the Oracle jobs feature does. -- Mike Nolan
Gregory Wood wrote: > Andrew Sullivan wrote: >> On Tue, Mar 30, 2004 at 12:16:50PM -0500, Steve Manes wrote: >> >>>I think he probably means like an Oracle job. Although cron works, that >>>would be handy so you wouldn't need to write wrapper scripts just to run >>>a proc. >> >> I hate to sound like an oldbie crank (although I'll admit to being a >> crank), but what exactly is the advantage supposed to be here? One >> invents a new special bit of database code which exists just so people >> don't have to write shell scripts? I guess the idea gets under my >> skin just because I have enough time-based problems without inventing >> a new interface to make it more complicated. > > The only real advantage I see are for those people in hosting > environments that don't really have access to cron. I suppose it's also > valuable in making your scripts more portable as you wouldn't need to > make further alterations to the system beyond restoring a database > backup or whatnot. and as soon as one would implement such stuff the users will be asking "and how do I execute shell commands from inside the DB?". Now that would be good thing. In preparation for that we should make the postmaster run under root, so that the pgcron functionality can start those external commands as any user! </irony> Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Wed, Apr 07, 2004 at 08:25:32AM -0500, Mike Nolan wrote: > > Not that jobs built into the database are of no value, but shell scripts > > seem to do everything you need and when there are other things to put into > > posgtresql I wouldn't vote to waste time on jobs. > > I wonder if Oracle's time-based jobs feature came to be as a result of some > other uses for that code, such as snapshots? I believe there's a lot of other oracle technologies that rely on dbms_job (oracle jobs). Materialized views and replication are two that come to mind; there's probably many others. If you look closely at all the dbms_* procedures oracle has, it's pretty clear than many of them exist to be of use to higher-level features. Oracle's mindset seems to be one of doing as much in SQL as is reasonable, and packaging the building blocks they create for something like replication as stand-alone utilities so that their customers can make the same use of them. > Time-based jobs wouldn't make my top 10 wish list either, though if I had > them, I'd probably use them. I tend to agree, but I wish people would stop poo-poo'ing having something like oracle jobs in pgsql. If you look at how oracle jobs work, they're not the same as crontab at all. The two reasons you state are just some of the differences. > The security aspects of them could be important to some users or > potential users. Using cron either forces one to have passwords out > there in plaintext in the .pgpass file or to use a 'trusted' username > that could also be a major security hole. > > Also, a script-based job can be changed or deleted by someone with the > right file permissions even though they may not have database permissions, > and vice versa. > -- > Mike Nolan > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Jim C. Nasby, Database Consultant jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America 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?"
Andrew Sullivan wrote: > On Tue, Mar 30, 2004 at 12:16:50PM -0500, Steve Manes wrote: >> I think he probably means like an Oracle job. Although cron works, >> that would be handy so you wouldn't need to write wrapper scripts >> just to run a proc. > > I hate to sound like an oldbie crank (although I'll admit to being a > crank), but what exactly is the advantage supposed to be here? One > invents a new special bit of database code which exists just so > people don't have to write shell scripts? I guess the idea gets > under my skin just because I have enough time-based problems without > inventing a new interface to make it more complicated. Hmm. Looks > like the sigmonster has gacked up a piece of wisdom. > > A As one who was about to ask the same question, I can provide one reason: ease of use/administration. I can create everything I need to manage/process my database *in* my database, rather than in numerous shell scripts scattered about. I had a weird issue with cron recently. I needed to do some db maintenance and wanted to make sure no one was going to change it, so I removed all cron jobs (crontab -r). Halfway through, suddenly the database started getting updated!! I finally figured out that my predecessors, for some reason, had stuck a couple cron jobs in the root crontab, which I corrected. And of course, if I copy a database someplace, the jobs would go with it; not so cron jobs. Now having said this, I realize that even if a scheduler was added to PostgreSQL, there is nothing to stop someone from additionally using cron jobs anyway. But there would be less temptation to do so. -- Guy Rouillier
Jan Wieck wrote: > Gregory Wood wrote: >> The only real advantage I see are for those people in hosting >> environments that don't really have access to cron. I suppose it's >> also valuable in making your scripts more portable as you wouldn't >> need to make further alterations to the system beyond restoring a >> database backup or whatnot. > > and as soon as one would implement such stuff the users will be asking > "and how do I execute shell commands from inside the DB?". Now that > would be good thing. In preparation for that we should make the > postmaster run under root, so that the pgcron functionality can start > those external commands as any user! </irony> I think you stretched well beyond the scope of my comments, but since you brought it up... yes, it can be handy to execute shell commands from inside the DB. Of course it's already possible in the form of untrusted languages (including the purpose built pl/sh), so it must not have been such a terribly bad idea. I don't think we need to rehash the argument about why running as root is bad, but I suppose there's always sudo for those users who really want to shoot themselves in the foot. Greg
My thoughts in a perfect world for having oracle style jobs in postgresql
would be a very loud yes. In the world called reality I say why waste
precious time on something that can already be accomplished by other means.
This feature at this time would just be icing on an already tasty cake.
This is not putting down anyone's ideas on getting these this is just my
personal thought at this point in time. If it would be easy for the c
guru's to do ,great put it in there. But if it will pull away from more
critical areas why do it.
|---------+---------------------------------->
| | "Guy Rouillier" |
| | <guyr@masergy.com> |
| | Sent by: |
| | pgsql-general-owner@pos|
| | tgresql.org |
| | |
| | |
| | 04/07/2004 05:00 PM |
| | |
|---------+---------------------------------->
>------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: "PostgreSQL General" <pgsql-general@postgresql.org>
|
| cc:
|
| Subject: Re: [GENERAL] Can we have time based triggers in Postgresql??
|
>------------------------------------------------------------------------------------------------------------------------------|
Andrew Sullivan wrote:
> On Tue, Mar 30, 2004 at 12:16:50PM -0500, Steve Manes wrote:
>> I think he probably means like an Oracle job. Although cron works,
>> that would be handy so you wouldn't need to write wrapper scripts
>> just to run a proc.
>
> I hate to sound like an oldbie crank (although I'll admit to being a
> crank), but what exactly is the advantage supposed to be here? One
> invents a new special bit of database code which exists just so
> people don't have to write shell scripts? I guess the idea gets
> under my skin just because I have enough time-based problems without
> inventing a new interface to make it more complicated. Hmm. Looks
> like the sigmonster has gacked up a piece of wisdom.
>
> A
As one who was about to ask the same question, I can provide one reason:
ease of use/administration. I can create everything I need to
manage/process my database *in* my database, rather than in numerous
shell scripts scattered about. I had a weird issue with cron recently.
I needed to do some db maintenance and wanted to make sure no one was
going to change it, so I removed all cron jobs (crontab -r). Halfway
through, suddenly the database started getting updated!! I finally
figured out that my predecessors, for some reason, had stuck a couple
cron jobs in the root crontab, which I corrected. And of course, if I
copy a database someplace, the jobs would go with it; not so cron jobs.
Now having said this, I realize that even if a scheduler was added to
PostgreSQL, there is nothing to stop someone from additionally using
cron jobs anyway. But there would be less temptation to do so.
--
Guy Rouillier
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may
containproprietary, confidential and/or privileged information. If you are not the intended recipient, any use,
copying,disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient,
pleasenotify the sender immediately by return e-mail, delete this communication and destroy all copies.
*************************************************************************
Gregory Wood wrote: > Jan Wieck wrote: >> Gregory Wood wrote: >>> The only real advantage I see are for those people in hosting >>> environments that don't really have access to cron. I suppose it's >>> also valuable in making your scripts more portable as you wouldn't >>> need to make further alterations to the system beyond restoring a >>> database backup or whatnot. >> >> and as soon as one would implement such stuff the users will be asking >> "and how do I execute shell commands from inside the DB?". Now that >> would be good thing. In preparation for that we should make the >> postmaster run under root, so that the pgcron functionality can start >> those external commands as any user! </irony> > > I think you stretched well beyond the scope of my comments, but since Sure did I and on purpose too, because when you offer "them" the small finger, you find yourself easily dismembered entirely! Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Mike Nolan <nolan@gw.tssi.com> writes: > > There's no reason you couldn't write a cron daemon that retrieved its list of > > jobs from an SQL database. In fact we discussed doing exactly this at my last > > job. I expect others have thought of the same idea and wouldn't be surprised > > if it existed by now. > > Is there (or would we need) a way to specify access privileges for such > a daemon in pg_hba.conf, and would that allow controlling the access > privileges for each job or the user who sets it? (ISP's would want > tight controls here.) Uh, that's not at all what I was picturing when I wrote the description above. I was picturing a cron daemon just like any other cron daemon except it happens to be backed by the database instead of a flat file. The privileges would be specified in tables just like they're currently specified in flat files (well currently in the directory structure above the flat files actually). The main advantage of such a database backed cron daemon would be the possible integration with other database backed systems. Job status could be viewed on database-backed web pages, Jobs could be created or deleted from web pages, failures could automatically create trouble tickets... -- greg
Greg Stark wrote: > Mike Nolan <nolan@gw.tssi.com> writes: > >> > There's no reason you couldn't write a cron daemon that retrieved its list of >> > jobs from an SQL database. In fact we discussed doing exactly this at my last >> > job. I expect others have thought of the same idea and wouldn't be surprised >> > if it existed by now. >> >> Is there (or would we need) a way to specify access privileges for such >> a daemon in pg_hba.conf, and would that allow controlling the access >> privileges for each job or the user who sets it? (ISP's would want >> tight controls here.) > > Uh, that's not at all what I was picturing when I wrote the description above. > I was picturing a cron daemon just like any other cron daemon except it > happens to be backed by the database instead of a flat file. The privileges > would be specified in tables just like they're currently specified in flat > files (well currently in the directory structure above the flat files > actually). > > The main advantage of such a database backed cron daemon would be the possible > integration with other database backed systems. Job status could be viewed on > database-backed web pages, Jobs could be created or deleted from web pages, > failures could automatically create trouble tickets... > And all that can be implemented just fine with the functionality that is there right now. Create a daemon and have triggers on the job table that NOTIFY the daemon so that it rereads and reschedules when the next job is due. This does not need the slightest little bit of backend changes. A wonderfull idea for a gborg/pgfoundry project, isn't it? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #