"trigger"ing a procedure every X minutes

Fran Fabrizio <ffabrizio@Exchange.WebMD.net> writes:

Is the preferred method of calling a procedure every X minutes to have a
cronjob installed external to the database, or is there something I can
do internally to achieve the same effect (and would be portable along
with the database when/if I move it to a different machine)?

I'm pretty sure there is no internal mechanism for scheduled
procedures. I think the cronjob is your best bet.

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time... --Dylan

Fran Fabrizio <ffabrizio@Exchange.WebMD.net> writes:

Is the preferred method of calling a procedure every X minutes to have a
cronjob installed external to the database,

Yes.

I see no need for us to replicate the functionality of cron ...

regards, tom lane

Some thoughts are:

1. It would be portable with the database. I don't have to go setting up
crons on the new machine if I move the db.
2. The cron usually involves writing a shell/perl/whatever script to call
the psql client and pass in SQL queries via STDIN or file redirect just to
invoke the desired procedure. An internal mechanism would bypass all of
that.
3. All of the code that maintains the database could be in one place
internal to the db.

It's certainly not a big deal to use the cron, I just thought maybe someone
had experimented with an internal mechanism. I'd like to try it someday,
when I know a lot more about Pg. My current project has an ever-growing
number of cron jobs (up to 6 now) and was just thinking about various ways to
tidy it up.

Thanks for the info,
Fran

On Wed, 6 Jun 2001, Fran Fabrizio wrote:

Some thoughts are:

1. It would be portable with the database. I don't have to go setting up
crons on the new machine if I move the db.
2. The cron usually involves writing a shell/perl/whatever script to call
the psql client and pass in SQL queries via STDIN or file redirect just to
invoke the desired procedure. An internal mechanism would bypass all of
that.
3. All of the code that maintains the database could be in one place
internal to the db.

It's certainly not a big deal to use the cron, I just thought maybe someone
had experimented with an internal mechanism. I'd like to try it someday,
when I know a lot more about Pg. My current project has an ever-growing
number of cron jobs (up to 6 now) and was just thinking about various ways to
tidy it up.

If you like to avoid the aid of your OS maybe Perl is your friend. There
is a library Schedule::Cron which does the same as the crontab. I guess
portability is given if you have your scheduler on the application level.

See http://search.cpan.org?mode=module&amp;query=cron

Best regards
Herbie

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services Adlergasse 5, 4500 Solothurn
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

It's certainly not a big deal to use the cron, I just thought maybe

someone

had experimented with an internal mechanism. I'd like to try it someday,
when I know a lot more about Pg. My current project has an ever-growing
number of cron jobs (up to 6 now) and was just thinking about various

ways to

tidy it up.

If you like to avoid the aid of your OS maybe Perl is your friend. There
is a library Schedule::Cron which does the same as the crontab. I guess
portability is given if you have your scheduler on the application level.

You might also be able to use pl/tcl's after function to do the same thing.

len morgan