Обсуждение: Can i write a time-trigger?

Hello,

Is it possible in postgres to write a trigger that is fired at a certain pre-defined time?   ie I want to write a trigger that checks if the current time is one of a certain list of dates, and if yes, it should be executed.  (I want it to execute only once on a matching date.)

I'm using EnterpriseDB Postgres Plus 8.3

Please help!
Thanks,
Shruthi

On Mon, Nov 16, 2009 at 2:42 PM, Shruthi A <shruthi.iisc@gmail.com> wrote:

> Is it possible in postgres to write a trigger that is fired at a certain
> pre-defined time?   ie I want to write a trigger that checks if the current
> time is one of a certain list of dates, and if yes, it should be executed.
> (I want it to execute only once on a matching date.)
>
> I'm using EnterpriseDB Postgres Plus 8.3

AFAIK, no, you cannot. But you can use a cron job that executes that
trigger via psql, same way you would do in the command console.

--
Jaume Sabater
http://linuxsilo.net/

"Ubi sapientas ibi libertas"

Shruthi A написа:
> Hello,
>
> Is it possible in postgres to write a trigger that is fired at a certain
> pre-defined time?   ie I want to write a trigger that checks if the current
> time is one of a certain list of dates, and if yes, it should be executed.
> (I want it to execute only once on a matching date.)
>
> I'm using EnterpriseDB Postgres Plus 8.3

Take a look at pgAgent (http://www.pgadmin.org/docs/1.10/pgagent.html).


--
Milen A. Radev

Thanks,

This pgagent seems to be a good interactive tool.  I tried using it, but i ran into some problems and extensive search didnt yield enough help or documentation on this.  This is a brief list of my doubts.

-- I connected to the 'postgres' database in my pgAdmin and ran the pgagent.sql file.  It claimed to have created a schema called 'pgagent' and a lot of tables inside it. When i try to run the sql file again, it tells me the elements already exist.  But i couldn't see either the schema or these tables anywhere.  Where did they go?

-- While running the pgagent daemon, what dbname should i specify? Is it postgres or pgadmin (as the example in   http://www.enterprisedb.com/docs/en/8.3/tools/pgadmin/1.8.2/pgagent-install.html )  ?

-- I created a stored proc in another database and want to schedule a call to this proc.  Am i allowed to do this? Since postgres doesnt allow cross-database references.  But i didnt find any error msg indicating this.

-- I managed to create a 'Job' in pgAdmin with the Definition specifying a call to my stored proc (without referencing the database).   But the 'schedule' here doesnt seem to accept any start date other than "1st jan 1970"!  And it selects some random start time which i again have no control on. Why is this?  The calender is brought up when we click on a very narrow strip inside the 'date' field, where apparently the arrow-mark should have been.  And the OK button in the Schedule dialog always seems to be greyed out but strangely responds to the 'Enter' key!

-- Ok, i really dont mind the start date, and i managed to schedule it daily as i wanted.  But why does it require me to click on 'Run now' even after the daemon is running and i've added a properly scheduled job?

-- Is there no command line interface for pgagent?  That would be nice since the GUI doesnt seem to be stable and doesnt throw useful error messages when there is a problem.


Anyways i achieved my objective in a very easy way using crontab.  So i'm really not going back to pgagent.   But just for curiosity, it'll be nice to know how to make pgagent work for me..

Thanks
Shruthi

> -- I connected to the 'postgres' database in my pgAdmin and ran the
> pgagent.sql file.  It claimed to have created a schema called 'pgagent' and
> a lot of tables inside it. When i try to run the sql file again, it tells
>  me the elements already exist.  But i couldn't see either the schema or
>  these tables anywhere.  Where did they go?

You can find it inside Catalogs if you're using pgAdmin.

> -- While running the pgagent daemon, what dbname should i specify? Is it
> postgres or pgadmin (as the example in
> http://www.enterprisedb.com/docs/en/8.3/tools/pgadmin/1.8.2/pgagent-install
> .html) ?

postgres. Since that is the database, pgagent is only a schema inside it.

> -- I created a stored proc in another database and want to schedule a call
> to this proc.  Am i allowed to do this? Since postgres doesnt allow
> cross-database references.  But i didnt find any error msg indicating this.

When you create a new step inside a job you have to specify the databse in
which the step should be executed. With that you then can use only tables,
functions and stuff in that databse since everything else will give you an
error.

> -- I managed to create a 'Job' in pgAdmin with the Definition specifying a
> call to my stored proc (without referencing the database).   But the
> 'schedule' here doesnt seem to accept any start date other than "1st jan
> 1970"!  And it selects some random start time which i again have no control
> on. Why is this?  The calender is brought up when we click on a very narrow
> strip inside the 'date' field, where apparently the arrow-mark should have
> been.  And the OK button in the Schedule dialog always seems to be greyed
> out but strangely responds to the 'Enter' key!

How did you manage this? When you select SQL as the type of the step it won't
let you create the step without the database name.
But for some reason what you described about the date field reminds me of some
problems I had. Since that time I first create an empty job(no steps or
schedules) and create them later. That way I had no problems and everything
works as it should.
I think the start date is important only if the job should start executing
sometime in the future else you just set what you want inside the "Days" and
"Times" tabs.
About the OK button, have to say I don't know what it could be.

> -- Ok, i really dont mind the start date, and i managed to schedule it
>  daily as i wanted.  But why does it require me to click on 'Run now' even
>  after the daemon is running and i've added a properly scheduled job?

> -- Is there no command line interface for pgagent?  That would be nice
>  since the GUI doesnt seem to be stable and doesnt throw useful error
>  messages when there is a problem.

Put them together since the're somehow related.
Is pgAgent running in the background and is it connected to the right
database?
If you wan't to debug it you can run it with "-f" and "-l 2". With that it
will run in foreground(-f) and debug mode(-l 2). That way you can see what
it's doing.

Hope I was able to answer some of your questions.

--
Regards,
Jure Kobal

Wow!  Thanks a lot!  That was a great deal of help! :-)

http://www.pgadmin.org/docs/1.8/pgagent-install.html are misleading, giving dbname=pgadmin in their example, while they clearly mention that we should create the pgagent catalogs in the 'postgres' database.  Really careless of them!
 

> -- I managed to create a 'Job' in pgAdmin with the Definition specifying a
> call to my stored proc (without referencing the database).  

How did you manage this? When you select SQL as the type of the step it won't
let you create the step without the database name.

If you wan't to debug it you can run it with "-f" and "-l 2". With that it
will run in foreground(-f) and debug mode(-l 2). That way you can see what
it's doing.

This is amazingly helpful.  More than i had imagined.  Thanks!  What i had initially thought by looking at pgagent --help which claims that with the -l option,  0 is error, 1 is warning, 2 is debug and 0 is default.   So i had thought that any error should be automatically flagged by the process when i run it in foreground (-f).  Including 'database pgadmin does not exist'.  But it didn't.  On further investigation after your suggestion, i found that this is considered only a 'warning' and not an 'error'!  So it is flagged with -l 1 or 2, but not with 0.  Isn't this ridiculous!

So now i know why my job wasn't running! 
And the -l 2 option (debug mode) is really great!  It gives me so many details i'm thrilled!  :-)

Anyways thanks a lot!  Now i'm up and running!  :-)
Thanks,
Shruthi

Le jeudi 19 novembre 2009 à 07:01:57, Shruthi A a écrit :
> Wow!  Thanks a lot!  That was a great deal of help! :-)
>
> On Wed, Nov 18, 2009 at 8:41 PM, Jure Kobal <j.kobal@gmx.com> wrote:
> > > -- While running the pgagent daemon, what dbname should i specify? Is
> > > it postgres or pgadmin (as the example in
> >
> > http://www.enterprisedb.com/docs/en/8.3/tools/pgadmin/1.8.2/pgagent-insta
> >ll
> >
> > > .html) ?
> >
> > postgres. Since that is the database, pgagent is only a schema inside it.
>
> The problem is that sites like the above and
>
> http://www.pgadmin.org/docs/1.8/pgagent-install.html
> are misleading, giving dbname=pgadmin in their example, while they clearly
> mention that we should create the pgagent catalogs in the 'postgres'
> database.  Really careless of them!
>

This is fixed right now.

BTW, rather than complaining only, you should send us an email so that we can
fix those issues ASAP.

> [...]
> initially thought by looking at pgagent --help which claims that with the
>  -l option,  0 is error, 1 is warning, 2 is debug and 0 is default.   So i
>  had thought that any error should be automatically flagged by the process
>  when i run it in foreground (-f).  Including 'database pgadmin does not
>  exist'. But it didn't.  On further investigation after your suggestion, i
>  found that this is considered only a 'warning' and not an 'error'!  So it
>  is flagged with -l 1 or 2, but not with 0.  Isn't this ridiculous!
>

Dave's answer to this:

It's a warning because it doesn't cause a hard failure. It'll keep
trying to connect, and then give up with an error after 10 or so
attempts over a period of time. It's done that way to allow for race
conditions in init scripts, or systems where PostgreSQL starts in a
timely fashion, but doesn't come up immediately because it goes into
recovery for example.


--
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com