Re: Is there anyway to... - Mailing list pgsql-general
From | louis gonzales |
---|---|
Subject | Re: Is there anyway to... |
Date | |
Msg-id | 454AAD82.9060202@linuxlouis.net Whole thread Raw |
In response to | Re: Is there anyway to... ("Wes Sheldahl" <wes.sheldahl@gmail.com>) |
List | pgsql-general |
Wes, Thanks. That is fair. Wes Sheldahl wrote: > On 11/2/06, *AgentM* <agentm@themactionfaction.com > <mailto:agentm@themactionfaction.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.sheldahl@gmail.com <mailto:wes.sheldahl@gmail.com> -- Email: louis.gonzales@linuxlouis.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
pgsql-general by date: