Re: Is there anyway to... - Mailing list pgsql-general

Glen,
Two things:
1) The instructor should only get a flag when the date conflict occurs,
once they create this student profile account, they shouldn't have to
manually check, I want the program to do, what programs should do,
automate as much as possible!  So the suggestion about 'generating a
query' is _wrong_ for the solution I want.
2) If you review all of the previous threads about this, I was using the
incrementing/decrementing 'task' as an example, as in, yes, probably
would've done some simple 'date math' to achieve what I want.

The problem in particular that I'm looking for a solution to is, having
an internal mechanism within the database that itself doesn't need an
event to drive it's behavior, but that this mechanism's behavior will
drive other events.

If there was a sleep() function, I could write a function that would
call sleep for, perhaps 24hrs and calculate the date difference on a
table of dates and put the difference of the dates, into a
'remainder_date' attribute.  A second sleep() function could check at
the turn of midnight every night, to check if something has
expired/elapsed and then trigger a write-out of specific data that would
then show up, upon instructor login, but the login itself shouldn't be
the trigger to generate the data.  What if the instructor logs in 15
times in one day, they don't need to have the same information presented
to them every time by virtue of the trigger, nor should the computer
have to run the function redundantly.



Glen Parker wrote:

> louis gonzales wrote:
>
>> Fine so let's say when the instructor creates the user profile, this
>> can trigger the creation of a sequence, say, but IF the instructor
>> doesn't visit that student's profile for a month, I want something
>> that, independent of any other action, will be either incrementing or
>> decrementing that sequence.  So when the instructor does go back to
>> visit the student profile it will only show/flag the status if  the
>> time has elapsed.  Is there like a sleep() function that postgresql
>> has?  That could be part of the plan.  So sleep(24hours) - pseudo
>> code - wake up and increment something initiate trigger to see if a
>> requirement has been met - say 10day or 30day or 1year has elapsed -
>> if so, set a flag attribute to the student profile.
>>
>> During that 30 days, the instructor won't care what's going on behind
>> the scenes, they just care when the time has elapsed, that they will
>> get a flag, to say, "hey buddy, over here, this student hasn't
>> fulfilled the agreement."
>
>
>
> I really think you're approaching this wrong by wanting to increment
> something every day.  You should generate a query that can run at any
> time and determine if any accounts are past due.  You can do the whole
> job using some simple date math.  If you do it that way, you could run
> the query every time the instructor logs in (not what I would do, but
> it would work), and at no other time.
>
> What I would do is run it every evening, say, and send a email
> notification to someone (like the instructor) whenever past due
> accounts were found.
>
> The problem with your increment design is, what happens if it fails
> for some reason?  Maybe the server was down for a memory upgrade, who
> knows?  You'll be a day off for every time the job failed to run.
>
> If your logic works even if it skips a few days, as soon as it runs
> again, everything will be back up to date.
>
> my .025...
>
> -Glen
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings



--
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