Re: Is there anyway to...

Wes,
Thanks. That is fair.

Wes Sheldahl wrote:

> On 11/2/06, *AgentM* <agentm(at)themactionfaction(dot)com
> <mailto:agentm(at)themactionfaction(dot)com>> wrote:
>
>
> On Nov 2, 2006, at 14:02 , Glen Parker wrote:
>
> > louis gonzales wrote:
> >> Hey Brian,
> >> Yeah I had considered this, using cron, I just feel like that is
> >> too dirty.
> >> Actually I didn't see Andreas' post, can someone forward that?
> >> I'm running this application on Solaris 9. Ultimately what I want
> >> to know is, is there something that is internal to postgresql that
> >> can be used that doesn't need external action, to make it do some
> >> task?
> >> Some built in function that can be set to do some simple task on a
> >> daily - or other time - interval, where all of the defined users
> >> may not have any activity with the database for day's or week's at
> >> a time, but this builtin function still operates?
> >> Am I making any sense with how I'm asking this? I could of course
> >> have cron do a scheduled task of checking/incrementing/
> >> decrementing and define triggers to occur when one of the cron
> >> delivered actions sets the appropriate trigger off, but are there
> >> other methods that are standard in the industry or are we stuck
> >> with this type of external influence?
> >
> >
> > Just some commentary... This is exactly the sort of thing cron is
> > for. Duplicating that functionality in the RDBMS would be silly
> > IMO. I don't see why you could consider cron to be "dirty" for
> > this application...
>
> I actually tried to come up with something for this. There are plenty
> of good reasons to have some timer functionality in the database:
>
> 1) it makes regular database-oriented tasks OS portable
> 2) your cron user needs specific permissions + authorization to
> access the database whereas postgres could handle "sudo"-like
> behavior transparently
> 3) there are triggers other than time that could be handy- on vacuum,
> on db start, on db quit, on NOTIFY
>
> Unfortunately, the limitation I came across was for 2). There is no
> way to use "set session authorization" or "set role" safely because
> the wrapped code could always exit from the sandbox. So my timer only
> works for db superusers.
>
> -M
>
> ---------------------------(end of
> broadcast)---------------------------
>
>
> None of those are good reasons to have timer functionality in the DB.
> Portability can be achieved having your cron job written in a portable
> language, like java, ruby or perl. Consistent permissions can be
> handled by having the java/whatever script connect to the db as a
> particular user; it doesn't matter what user executes the cron job
> provided it can run the script. #3 has nothing to do with timer
> functionality.
>
> Glen was right about solving this problem with some basic date math:
> IF (now - startdate) > '30 days' THEN EXPIRED. This could be
> implemented at the application level, or in postgresql as a function
> that the application calls whenever it wants to know about possible
> expirations. So this particular problem may be better solved without
> any timer functionality either in OR out of the database... if you did
> have a cron job run to check, you would probably just have it set a
> boolean field on expired records or something of that sort, and run it
> a little after midnight, at the start of each day, assuming durations
> were always being measured in days.
>
> Best of luck,
> --
> Wes Sheldahl
> wes(dot)sheldahl(at)gmail(dot)com <mailto:wes(dot)sheldahl(at)gmail(dot)com>

--
Email: louis(dot)gonzales(at)linuxlouis(dot)net
WebSite: http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka