Обсуждение: 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
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
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
On Mon, Nov 16, 2009 at 8:25 PM, Milen A. Radev <milen@radev.net> wrote:
Take a look at pgAgent (http://www.pgadmin.org/docs/1.10/pgagent.html).
--
Milen A. Radev
> -- 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! :-)
You are right. I had actually selected the database when creating the step, but i had assumed that to be a 'target' for the step to run on, rather than a reference for the sql. And i had forgotten about it. Anyways, my mistake. Sorry!
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
On Wed, Nov 18, 2009 at 8:41 PM, Jure Kobal <j.kobal@gmx.com> wrote:
The problem is that sites like the above and
> -- While running the pgagent daemon, what dbname should i specify? Is itpostgres. Since that is the database, pgagent is only a schema inside it.
> postgres or pgadmin (as the example in
> http://www.enterprisedb.com/docs/en/8.3/tools/pgadmin/1.8.2/pgagent-install
> .html) ?
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!
> -- I managed to create a 'Job' in pgAdmin with the Definition specifying aHow did you manage this? When you select SQL as the type of the step it won't
> call to my stored proc (without referencing the database).
let you create the step without the database name.
You are right. I had actually selected the database when creating the step, but i had assumed that to be a 'target' for the step to run on, rather than a reference for the sql. And i had forgotten about it. Anyways, my mistake. Sorry!
If you wan't to debug it you can run it with "-f" and "-l 2". With that itThis 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!
will run in foreground(-f) and debug mode(-l 2). That way you can see what
it's doing.
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