Thread: Is there anyway to...

Hello all,
Is there an existing mechanism is postgresql that can automatically
increment/decrement on a daily basis w/out user interaction?  The use
case I'm considering is where a student is in some type of contract with
an instructor of some sort, and that contract puts a time limit on the
student requiring her to pay a fee by a certain day.  IF that day comes
to pass - or a certain number of days elapse - and that payment
requirement hasn't been met, I want to trigger a function.

The one requirement I want to impose is, that no end user of the DB
application, needs to do anything to set the trigger, other than the
initialization of making the student of this type.

An example would be:
Day1 - Application user(typically the instructor) creates a profile for
a new student - John Doe, which sets a 30 day time limit for John Doe to
pay $100.00
Day2 -> Day31 - John Doe didn't make the payment
Day 31 - Trigger of event occurs when the instructor logs in.

Basically on Day 1 when John Doe's profile was created, I want a
decrement counter to occur daily on his profile(some attribute/timer)
and nothing should happen until day 31 when he doesn't pay.

Any ideas?

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

am  Thu, dem 02.11.2006, um 13:14:22 -0500 mailte louis gonzales folgendes:
> Hello all,
> Is there an existing mechanism is postgresql that can automatically
> increment/decrement on a daily basis w/out user interaction?  The use

You can use CRON for such tasks. I hope for, you have a operating system
with a CRON...


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

louis gonzales wrote:
> Hello all,
> Is there an existing mechanism is postgresql that can automatically
> increment/decrement on a daily basis w/out user interaction?  The use
> case I'm considering is where a student is in some type of contract with
> an instructor of some sort, and that contract puts a time limit on the
> student requiring her to pay a fee by a certain day.  IF that day comes
> to pass - or a certain number of days elapse - and that payment
> requirement hasn't been met, I want to trigger a function.
>
> The one requirement I want to impose is, that no end user of the DB
> application, needs to do anything to set the trigger, other than the
> initialization of making the student of this type.
>
> An example would be:
> Day1 - Application user(typically the instructor) creates a profile for
> a new student - John Doe, which sets a 30 day time limit for John Doe to
> pay $100.00
> Day2 -> Day31 - John Doe didn't make the payment
> Day 31 - Trigger of event occurs when the instructor logs in.
>
> Basically on Day 1 when John Doe's profile was created, I want a
> decrement counter to occur daily on his profile(some attribute/timer)
> and nothing should happen until day 31 when he doesn't pay.
>

Further to Andreas' suggestion to use CRON, you don't require a
decrement of anything. When the profile is created, your date_created
(or whatever) column will be set. Then your script (called by CRON) only
needs to test for rows that a) have not paid, and b) are outside the
bounds set in the script (eg. MAX_GRACE_PERIOD = 30).

brian

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?

Thanks all!

brian wrote:

> louis gonzales wrote:
>
>> Hello all,
>> Is there an existing mechanism is postgresql that can automatically
>> increment/decrement on a daily basis w/out user interaction?  The use
>> case I'm considering is where a student is in some type of contract
>> with an instructor of some sort, and that contract puts a time limit
>> on the student requiring her to pay a fee by a certain day.  IF that
>> day comes to pass - or a certain number of days elapse - and that
>> payment requirement hasn't been met, I want to trigger a function.
>>
>> The one requirement I want to impose is, that no end user of the DB
>> application, needs to do anything to set the trigger, other than the
>> initialization of making the student of this type.
>>
>> An example would be:
>> Day1 - Application user(typically the instructor) creates a profile
>> for a new student - John Doe, which sets a 30 day time limit for John
>> Doe to pay $100.00
>> Day2 -> Day31 - John Doe didn't make the payment
>> Day 31 - Trigger of event occurs when the instructor logs in.
>>
>> Basically on Day 1 when John Doe's profile was created, I want a
>> decrement counter to occur daily on his profile(some attribute/timer)
>> and nothing should happen until day 31 when he doesn't pay.
>>
>
> Further to Andreas' suggestion to use CRON, you don't require a
> decrement of anything. When the profile is created, your date_created
> (or whatever) column will be set. Then your script (called by CRON)
> only needs to test for rows that a) have not paid, and b) are outside
> the bounds set in the script (eg. MAX_GRACE_PERIOD = 30).
>
> brian
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org/



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

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

-Glen

Or you can probably use a PostgreSQL administration tool for scheduled
jobs. I know a number of such tools which provide this feature and
EnterpriseDB Management Server is one of them.

--Imad
www.EnterpriseDB.com


On 11/2/06, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
> am  Thu, dem 02.11.2006, um 13:14:22 -0500 mailte louis gonzales folgendes:
> > Hello all,
> > Is there an existing mechanism is postgresql that can automatically
> > increment/decrement on a daily basis w/out user interaction?  The use
>
> You can use CRON for such tasks. I hope for, you have a operating system
> with a CRON...
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

louis gonzales <gonzales@linuxlouis.net> schrieb:

> Hey Brian,
> Yeah I had considered this, using cron, I just feel like that is too dirty.

Why?


>
> Actually I didn't see Andreas' post, can someone forward that?

Sorry, i posted to the list, and i can see my mail.


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

Take a look at http://pgfoundry.org/projects/pgjob/


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

I'm using PG on Linux-systems and use CRON for any tasks, without any
problems. I can't understand your dislike...


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

To all who replied, first, thank you!  Second, I simply said 'dirty'
never said dislike or anything like that.  I've used and use cron for
different OS related operations and have for years.  I LOVE cron itself.

What I mean with 'dirty' is that I'm trying to take away as much as
possible from external influences to the database action I want to happen.

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

Based on what everyone is saying, there is no such internal mechanism
function/trigger or otherwise, that can do independent actions.


Andreas Kretschmer wrote:

>louis gonzales <gonzales@linuxlouis.net> schrieb:
>
>
>
>>Hey Brian,
>>Yeah I had considered this, using cron, I just feel like that is too dirty.
>>
>>
>
>Why?
>
>
>
>
>>Actually I didn't see Andreas' post, can someone forward that?
>>
>>
>
>Sorry, i posted to the list, and i can see my mail.
>
>
>
>
>>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?
>>
>>
>
>Take a look at http://pgfoundry.org/projects/pgjob/
>
>
>
>
>>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?
>>
>>
>
>I'm using PG on Linux-systems and use CRON for any tasks, without any
>problems. I can't understand your dislike...
>
>
>Andreas
>
>


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

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

Apparently this isn't the first time someone else thought a sleep or
timer mechanism, independent of user action would be of great value and
didn't want to use external programs to accomplish it.

http://developer.*postgresql*.org/pgdocs/postgres/release-8-2.html
* Add a server-side *sleep* *function* pg_sleep() (Joachim Wieland):
SELECT pg_sleep(1);



AgentM 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)---------------------------
> TIP 6: explain analyze is your friend



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

am  Thu, dem 02.11.2006, um 14:24:20 -0500 mailte louis gonzales folgendes:
> 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

Read the release notes for 8.2:
http://developer.postgresql.org/pgdocs/postgres/release-8-2.html :
Add a server-side sleep function pg_sleep()  (Joachim Wieland)

>
>
> Andreas Kretschmer wrote:
>
> >louis gonzales <gonzales@linuxlouis.net> schrieb:

I'm sorry, but i have a big dislike for Top Posting, Below Quoting.
It breaks the normal reading of text.

Hint:

A: Top-posting.
Q: What is the most annoying thing on usenet?


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

imad wrote:
> Or you can probably use a PostgreSQL administration tool for scheduled
> jobs. I know a number of such tools which provide this feature and
> EnterpriseDB Management Server is one of them.

As is pgAdmin's pgAgent.

Regards, Dave

On Thu, 2 Nov 2006, AgentM wrote:
> >
> > 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

...This type of need is exactly what custom written daemons are for.
They're surely database and OS portable (or can be, at least), there's no
need for any super-user capability of any kind, you can use any kind of
trigger you like, and there's no permission leakage problem, either... I
guess all you need is functioning nohup capability (which Windows systems
may have trouble with, I don't know).

Richard

--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy@ScienceTools.com, http://ScienceTools.com/

On Nov 2, 2006, at 15:00 , Richard Troy wrote:

>
>
> On Thu, 2 Nov 2006, AgentM wrote:
>>>
>>> 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
>
> ...This type of need is exactly what custom written daemons are for.
> They're surely database and OS portable (or can be, at least),
> there's no
> need for any super-user capability of any kind, you can use any
> kind of
> trigger you like, and there's no permission leakage problem,
> either... I
> guess all you need is functioning nohup capability (which Windows
> systems
> may have trouble with, I don't know).

Sure- I wrote a custom daemon. But it has general usefulness. Instead
of ten clients listening on ten notifications (and holding open
connections for little reason), I would like to have one connection
handle all the notification events- based on which notification or
timer event, it could call a different stored procedure with
different roles. That way, I wouldn't need one connection open for
ever user that needs to listen and react. That simply doesn't scale.

Cheers,
M

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 11/02/06 13:24, louis gonzales wrote:
[snip]
>>> 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?

That's turning the RDBMS into the job scheduler.

>> Take a look at http://pgfoundry.org/projects/pgjob/

I did.

> Latest File Releases
> Package     Version     Date     Notes / Monitor Download
> This Project Has Not Released Any Files

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFSlTtS9HxQb37XmcRAmbWAKCqqJjaFK+mYdTQ7evixH0hxVS+qACgsdTN
fXyzElhkNILqpSUHk+WRFcQ=
=kPMa
-----END PGP SIGNATURE-----

Ron Johnson <ron.l.johnson@cox.net> schrieb:
> >> Take a look at http://pgfoundry.org/projects/pgjob/
>
> I did.
>
> > Latest File Releases
> > Package     Version     Date     Notes / Monitor Download
> > This Project Has Not Released Any Files

OMG. Sorry.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

A. Kretschmer wrote:

>am  Thu, dem 02.11.2006, um 14:24:20 -0500 mailte louis gonzales folgendes:
>
>
>>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
>>
>>
>
>Read the release notes for 8.2:
>http://developer.postgresql.org/pgdocs/postgres/release-8-2.html :
>Add a server-side sleep function pg_sleep()  (Joachim Wieland)
>
>
>
>>Andreas Kretschmer wrote:
>>
>>
>>
>>>louis gonzales <gonzales@linuxlouis.net> schrieb:
>>>
>>>
>
>I'm sorry, but i have a big dislike for Top Posting, Below Quoting.
>It breaks the normal reading of text.
>
>Hint:
>
>A: Top-posting.
>
>
    Besser Antwort: Unsolicited opinions!

>Q: What is the most annoying thing on usenet?
>
>
>Andreas
>
>


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

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

On 11/2/06, AgentM <agentm@themactionfaction.com> wrote:
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

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

Wes Sheldahl wrote:
> 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.

Exactly.  You flag when you discover an expired condition, and you flag
again when the condition is acted upon.  Easy.  The instructor doesn't
need to be bothered any more than you choose.  You could even set a
timestamp indicating the last time the instructor was harrassed about
it, so you can re-harrass on a daily or weekly basis :-)

Now, my example that had the expire query run when the instructor logs
on was just to illustrate that if you do this right, it doesn't matter
when the code runs.  Personally, I'd have a cron job do it at midnight,
but whenever it runs, even if it's multiple times a day, the outcome
should still be correct. That's where your status flags come in.  Once a
record has been expired, there's no reason for your expire code to ever
look at that record again, right?

-Glen

I suppose I'll just concede now :)  Thanks for putting up with my
sarcasm and humoring my other ideas.  I really wanted to see if there
were any other methods out there and do appreciate everyone's ideas.

Thanks again,  CRON it is.

Glen Parker wrote:

> Wes Sheldahl wrote:
>
>> 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.
>
>
> Exactly.  You flag when you discover an expired condition, and you
> flag again when the condition is acted upon.  Easy.  The instructor
> doesn't need to be bothered any more than you choose.  You could even
> set a timestamp indicating the last time the instructor was harrassed
> about it, so you can re-harrass on a daily or weekly basis :-)
>
> Now, my example that had the expire query run when the instructor logs
> on was just to illustrate that if you do this right, it doesn't matter
> when the code runs.  Personally, I'd have a cron job do it at
> midnight, but whenever it runs, even if it's multiple times a day, the
> outcome should still be correct. That's where your status flags come
> in.  Once a record has been expired, there's no reason for your expire
> code to ever look at that record again, right?
>
> -Glen
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend



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

On 11/2/06, louis gonzales <gonzales@linuxlouis.net> 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?
>

Your original scenario had an external action, instructor login.   The
daily increment is the days since account creation.

Since we don't have triggers based on login, you maybe could tickle a
function from your front-end that would simply look for students with
account age > 30 and not paid whenever the instructor logs in.

Short of that, an OS based scheduler (AT, cron, etc.)  is your only
choice I can think of

Moving to -general (and please start a new thread instead of hijacking
an existing one).

On Thu, Nov 02, 2006 at 01:14:22PM -0500, louis gonzales wrote:
> Hello all,
> Is there an existing mechanism is postgresql that can automatically
> increment/decrement on a daily basis w/out user interaction?  The use
> case I'm considering is where a student is in some type of contract with
> an instructor of some sort, and that contract puts a time limit on the
> student requiring her to pay a fee by a certain day.  IF that day comes
> to pass - or a certain number of days elapse - and that payment
> requirement hasn't been met, I want to trigger a function.
>
> The one requirement I want to impose is, that no end user of the DB
> application, needs to do anything to set the trigger, other than the
> initialization of making the student of this type.
>
> An example would be:
> Day1 - Application user(typically the instructor) creates a profile for
> a new student - John Doe, which sets a 30 day time limit for John Doe to
> pay $100.00
> Day2 -> Day31 - John Doe didn't make the payment
> Day 31 - Trigger of event occurs when the instructor logs in.
>
> Basically on Day 1 when John Doe's profile was created, I want a
> decrement counter to occur daily on his profile(some attribute/timer)
> and nothing should happen until day 31 when he doesn't pay.

While you could setup a cron job to decrement some counter every day, I
think that's not the best approach. Instead, I'd run a query once a day
that finds all students that are past-due and takes some kind of action.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

> While you could setup a cron job to decrement some
> counter every day, I
> think that's not the best approach. Instead, I'd run
> a query once a day
> that finds all students that are past-due and takes
> some kind of action.
> --
> Jim Nasby
> jim@nasby.net
> EnterpriseDB      http://enterprisedb.com
> 512.569.9461 (cell)
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map
> settings

i'm wanting to learn something here so i'm going to
chime in.

the way i read what you are saying is that you'd have
start_date and number_days columns in your table.

each day a query would run and pull the start_date and
numbers_days columns.

the application (or postgresql function) would then
take the current date, subtract starte date and
compare it to number of days.  if it is above that
number, the code will take some sort of action.

is that about it or have i missed something?

tia...




____________________________________________________________________________________
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail.
http://new.mail.yahoo.com

operationsengineer1@yahoo.com wrote:
> i'm wanting to learn something here so i'm going to
> chime in.
>
> the way i read what you are saying is that you'd have
> start_date and number_days columns in your table.
>
> each day a query would run and pull the start_date and
> numbers_days columns.
>
> the application (or postgresql function) would then
> take the current date, subtract starte date and
> compare it to number of days.  if it is above that
> number, the code will take some sort of action.
>
> is that about it or have i missed something?

I think that's what Jim's talking about. Databases are good at filtering
large numbers of rows.

You'll also want some sort of status code or "processed" flag, so you
know not to look at rows you've already handled.

The other alternative would be an "expiry_list" table that stores the
target row's key and when it expires. Add a row when the contract is
created. Delete the row when the contract is paid. Keep it all
up-to-date with triggers.

--
   Richard Huxton
   Archonet Ltd