Postgres-R

It's basically pre-production-ready software to date. But see the
excellent presentation materials at

http://conference.postgresql.org/Program

(last presentation in the left-hand column). I hope to have audio
for that session available soon.

A

On Thu, Nov 02, 2006 at 07:16:24PM +0200, Anton P. Linevich wrote:

Hello.

Just interesting, anyone from this list ever try Postgres-R for
cluster/replication?

Sorry for OT.

--
Anton P. Linevich

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Andrew Sullivan | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie

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

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

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

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�

am Fri, dem 03.11.2006, um 0:04:50 +0500 mailte imad folgendes:

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

^^^^^^^^^^^^^^^^^^^^

Yeah! ;-)

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

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

Yeah, and EnterpriseDB Management Server is a community project and
can be used for free, off course!

--Imad
www.EnterpriseDB.com

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

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/