Thread: Timed Triggers?

This may seem a little silly, but I'd like to request a feature to
implement timed triggers.

With the number of cron jobs that run a perl script which runs a simple
SELECT function(); that does various nightly cleanup or maintenance
(billing run) it would be much nicer to have an actual trigger run every
12 hours rather than be initiated by a cron job.

Less places things could go wrong.  Could be requested to run only when
a table isn't being used at the time (low priority maintenance).  The
same thing could be implemented for a periodic vacuum.

I'd like to take this up myself, but will need to learn alot about the
way Postgres works first so if someone else sees the need as well please
feel free to implement (It'll take me atleast 6 months before I feel
comfortable with the results).

-- 
Rod Taylor
--

Rod Taylor <rod.taylor@inquent.com> writes:
> With the number of cron jobs that run a perl script which runs a simple
> SELECT function(); that does various nightly cleanup or maintenance
> (billing run) it would be much nicer to have an actual trigger run every
> 12 hours rather than be initiated by a cron job.

Er ... why?  Seems like what you're describing is implementing the guts
of cron in the postmaster.  I don't see a real good reason to re-invent
the wheel like that.
        regards, tom lane

Tom Lane wrote:
> 
> Rod Taylor <rod.taylor@inquent.com> writes:
> > With the number of cron jobs that run a perl script which runs a simple
> > SELECT function(); that does various nightly cleanup or maintenance
> > (billing run) it would be much nicer to have an actual trigger run every
> > 12 hours rather than be initiated by a cron job.
> 
> Er ... why?  Seems like what you're describing is implementing the guts
> of cron in the postmaster.  I don't see a real good reason to re-invent
> the wheel like that.

My current concern is the reliance on cron, perl, and the postmaster
running properly in order to accomplish a job.  When one fails, they
operation fails.  If the postmaster is down, how does cron know that
something needs to be re-run?  In the postmaster we could issue a
trigger of type 'once every 4 hours, and on startup' to ensure that the
billing run (or whatever) is executed properly as early as possibly.  At
the moment it takes quite a bit of code outside of the database to
accomplish what would be a trivial setting for the creator of the
trigger to tie to it.

Your right though, it's too bad cron wasn't more library oriented so
that it could be used within programs rather than having to be
re-implemented.

-- 
Rod Taylor

"People get annoyed when you try to debug them." -- Larry Wall, 2nd
State of the Onion.

"A + B + C = Success if, A = Hard Work, B = Hard Play, C = Keeping your
mouth shut." -- Albert Einstein

--