Message-ID: <454AAD82.9060202@linuxlouis.net>
Date: Thu, 02 Nov 2006 21:46:26 -0500
From: louis gonzales <gonzales@linuxlouis.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.7.12) Gecko/20050915
MIME-Version: 1.0
To: Wes Sheldahl <wes.sheldahl@gmail.com>
CC: AgentM <agentm@themactionfaction.com>, pgsql-general@postgresql.org
Subject: Re: Is there anyway to...
References: <20061102171624.GE21312@localhost>	
	<454A357E.6010409@linuxlouis.net>
	<454A394C.7020102@zijn-digital.com>	
	<454A3EB6.8080806@linuxlouis.net> <454A40E3.40007@nwlink.com>	
	<26B58EAC-4BCC-470E-AC5E-477A5AA4CA52@themactionfaction.com>
	<c0a118d70611021836h61b71f66m3c6a58175126b9b6@mail.gmail.com>
In-Reply-To: <c0a118d70611021836h61b71f66m3c6a58175126b9b6@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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