Обсуждение: pgagent using one connection to each job
Greetings!
I don't know if it is good mailing list but I really need help from people with more experience.
I've tried to use pgagent as job scheduler but I found two significant(for me) problems.
First: You can't execute job every for example 30 seconds, is there a way to do this? I know that one minute is often but if job will execute at 18:00 and will last for 2 seconds the next job will be executed at 18:02 it's 1:58 seconds later, for me it's rather big problem.
I'm working on online transmissions of data between postgres and Oracle. Checking if there is modified data should be every 30-20 seconds, postgres is database for big marketplaces and all data from all postgres is in one central Oracle database.
I've written lot of functions in plperlu using DBD::Oracle module to connect and get data from Oracle. All work well and really fast because of prepared_cached queries and saving connection to ora in plperlu session variable.
But when I tried to execute functions in pgagent steps they weren't work so fast.
pgagent is creating new session for each executed job and clearing session pl/perlu variables where connection to Oracle and cached queries are stored.
So my questions are:
1. Is there a way that you can execute job more often that one minute?
2. Can pgagent use the same session to the same job?
3. Is it very difficult to add seconds to schedule?
I'm grateful of any of your help, if I don't solve this problem i will have to write application to run this functions, but I really want to use pgagent.
Regards
Tomasz Olszak
I don't know if it is good mailing list but I really need help from people with more experience.
I've tried to use pgagent as job scheduler but I found two significant(for me) problems.
First: You can't execute job every for example 30 seconds, is there a way to do this? I know that one minute is often but if job will execute at 18:00 and will last for 2 seconds the next job will be executed at 18:02 it's 1:58 seconds later, for me it's rather big problem.
I'm working on online transmissions of data between postgres and Oracle. Checking if there is modified data should be every 30-20 seconds, postgres is database for big marketplaces and all data from all postgres is in one central Oracle database.
I've written lot of functions in plperlu using DBD::Oracle module to connect and get data from Oracle. All work well and really fast because of prepared_cached queries and saving connection to ora in plperlu session variable.
But when I tried to execute functions in pgagent steps they weren't work so fast.
pgagent is creating new session for each executed job and clearing session pl/perlu variables where connection to Oracle and cached queries are stored.
So my questions are:
1. Is there a way that you can execute job more often that one minute?
2. Can pgagent use the same session to the same job?
3. Is it very difficult to add seconds to schedule?
I'm grateful of any of your help, if I don't solve this problem i will have to write application to run this functions, but I really want to use pgagent.
Regards
Tomasz Olszak
On Wed, Mar 25, 2009 at 11:07 AM, Tomasz Olszak <tolszak@o2.pl> wrote: > 1. Is there a way that you can execute job more often that one minute? No. > 2. Can pgagent use the same session to the same job? Not without a pretty major redesign. > 3. Is it very difficult to add seconds to schedule? Yes, that would be rather painful. Sorry :-( -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Thank you for your quick answer David. So I'm starting to develope application for this jobs.
Regards
Tomasz Olszak
Dnia 25 marca 2009 12:40 Dave Page <dpage@pgadmin.org> napisał(a):
Regards
Tomasz Olszak
Dnia 25 marca 2009 12:40 Dave Page <dpage@pgadmin.org> napisał(a):
On Wed, Mar 25, 2009 at 11:07 AM, Tomasz Olszak <tolszak@o2.pl> wrote:
> 1. Is there a way that you can execute job more often that one minute?
No.
> 2. Can pgagent use the same session to the same job?
Not without a pretty major redesign.
> 3. Is it very difficult to add seconds to schedule?
Yes, that would be rather painful.
Sorry :-(
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com