Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 682
  • Last Modified:

i need help in order to have my jobs working in postgresql.

Hello experts,

i'm running postgresql on windows 2003 server.
i have installed pgAgent,
 
i have created a function in a database,
my function output values in a  table of the same database,
i have tested my function, she does the job as expected, when fire
it from the sql input interface in pgAdmin.

i have created a job that should fire the function,
the problem is that i have no idea why the job doesn't fire the function.
i don't know what 's the erorr,  

this is the output in my pgadmin.log when i "run now" my job.

2008-10-20 12:40:46 QUERY  : Void query (localhost:5432): UPDATE pgagent.pga_job SET jobnextrun = now() WHERE jobid=2
2008-10-20 12:40:46 STATUS : Refreshing pgAgent Job...
2008-10-20 12:40:46 QUERY  : Set query (localhost:5432): SELECT j.*, cl.*, ag.*, sub.jlgstatus AS joblastresult   FROM pgagent.pga_job j JOIN  pgagent.pga_jobclass cl ON cl.jclid=jobjclid LEFT OUTER JOIN  pgagent.pga_jobagent ag ON ag.jagpid=jobagentid LEFT OUTER JOIN  (SELECT DISTINCT ON (jlgjobid) jlgstatus, jlgjobid   FROM pgagent.pga_joblog  ORDER BY jlgjobid, jlgid desc) sub ON sub.jlgjobid = j.jobid
   WHERE j.jobid=2ORDER BY jobname;
2008-10-20 12:40:46 INFO   : Deleting pgAgent Job level_to_mail for refresh
2008-10-20 12:40:46 INFO   : Replacing with new node pgAgent Job level_to_mail for refresh
2008-10-20 12:40:46 INFO   : Displaying properties for pgAgent Job level_to_mail
2008-10-20 12:40:46 INFO   : Adding child objects to Job.
2008-10-20 12:40:46 QUERY  : Set query (localhost:5432): SELECT * FROM pgagent.pga_schedule
 WHERE jscjobid=2
 ORDER BY jscname
2008-10-20 12:40:46 QUERY  : Set query (localhost:5432): SELECT * FROM pgagent.pga_exception
 WHERE jexscid=3

help......help....
0
toshi_
Asked:
toshi_
  • 4
  • 2
1 Solution
 
toshi_Author Commented:
the step definition in my job is as follow (only a function call)

select level_of_day();
0
 
toshi_Author Commented:
in my database, after installing pgAgent
i have a Catalogs with pgAgent which contains 8 tables,
except the pgagent.pga_jobclass all tables are empty, is it normal?
.....
argh, argh...help
0
 
ivanovnCommented:
Your pgagent tables should have some data (if you created a job after installing the pgagent). You should have data in pga_job, pga_jobstep, etc. So for some reason the job you're creating is not getting inserted into the tables correctly. You may want to check if the pgagent user has correct privileges.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
toshi_Author Commented:
greate, a step forward

0
 
toshi_Author Commented:
i know now how to check if my job is correctly entered.


0
 
ivanovnCommented:
The other thing you may want to check is whether your pgagent service is running. If the service is not running, the jobs will not be executed.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now