Postgres Pro
Downloads
Home   >   mailing lists

Thread: Time-based trigger/procedure

Time-based trigger/procedure

From
André Silva
Date:
I have a table that defines a programmed service that does something automatically every time interval (days, weeks, months) from a beginning date to a ending date.
How can I do this in postgresql? I have no access to the OS, so cron is not an option.

I'm sorry if my english is a bit confusing but it is not my main language.

Regards,
André.

Re: Time-based trigger/procedure

From
"A. Kretschmer"
Date:
In response to André Silva :
> I have a table that defines a programmed service that does something
> automatically every time interval (days, weeks, months) from a beginning date
> to a ending date.
> How can I do this in postgresql? I have no access to the OS, so cron is not an
> option.

To use the CRON or something similar is the way to do that. An other
dirty solution: use pg_sleep() to sleep, for instance, 1 minute, and
then detect the actual time and do the work.


>
> I'm sorry if my english is a bit confusing but it is not my main language.

The same for me...


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

auto vacuum question

From
"Wright, George"
Date:
I have auto vacuum turned on in the postgresql.conf file and I noticed
template1 and postgres databases being vacuumed repeatedly.

Is that necessary? If not, how can I turn it off for just those
databases but not my project's database?



Here are my postgresql.conf autovaccum settings:

autovacuum = on                         # enable autovacuum subprocess?
#autovacuum_naptime = 60                # time between autovacuum runs,
in secs
#autovacuum_vacuum_threshold = 1000     # min # of tuple updates before
                                        # vacuum
#autovacuum_analyze_threshold = 500     # min # of tuple updates before
                                        # analyze
#autovacuum_vacuum_scale_factor = 0.4   # fraction of rel size before
                                        # vacuum
#autovacuum_analyze_scale_factor = 0.2  # fraction of rel size before
                                        # analyze
#autovacuum_vacuum_cost_delay = -1      # default vacuum cost delay for
                                        # autovac, -1 means use
                                        # vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for
                                        # autovac, -1 means use
                                        # vacuum_cost_limit

Re: auto vacuum question

From
"A. Kretschmer"
Date:
In response to Wright, George :
> I have auto vacuum turned on in the postgresql.conf file and I noticed
> template1 and postgres databases being vacuumed repeatedly.

Please, in your own interest, don't hijack other threads by replaying a
mail and changing the subject and start a new question, your mail still
contains a references-header pointing to the other, old thread.


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

Re: Time-based trigger/procedure

From
"Rupinder.Singh"
Date:
You can use pgagent or pgjobs.

--Rupinder
EnterpriseDB
www.enterprisedb.com

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of A. Kretschmer
Sent: Thursday, April 09, 2009 10:44 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Time-based trigger/procedure

In response to André Silva :
> I have a table that defines a programmed service that does something
> automatically every time interval (days, weeks, months) from a beginning
date
> to a ending date.
> How can I do this in postgresql? I have no access to the OS, so cron is
not an
> option.

To use the CRON or something similar is the way to do that. An other
dirty solution: use pg_sleep() to sleep, for instance, 1 minute, and
then detect the actual time and do the work.


>
> I'm sorry if my english is a bit confusing but it is not my main language.

The same for me...


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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: Time-based trigger/procedure

From
Jasen Betts
Date:
On 2009-04-09, André Silva <andre.beat@gmail.com> wrote:
> --0015174c0df051ba710467141cad
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: quoted-printable
>
> I have a table that defines a programmed service that does something
> automatically every time interval (days, weeks, months) from a beginning
> date to a ending date.
> How can I do this in postgresql? I have no access to the OS,
> so cron is not an option.

timed events need OS support.

what do you have access to ?
If you have shell access to the server
(or the ability to execute scripts on it) you can use the crontab
command to create cron tasks that run in your name

else you may need to set up a cron job on some other server that
connects to your database and runs the stuff for you.