Re: Is there anyway to...

On 11/2/06, AgentM <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