Solved

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

Posted on 2008-10-20
6
657 Views
Last Modified: 2012-06-27
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
Comment
Question by:toshi_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 

Author Comment

by:toshi_
ID: 22756336
the step definition in my job is as follow (only a function call)

select level_of_day();
0
 

Author Comment

by:toshi_
ID: 22756350
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
 
LVL 10

Accepted Solution

by:
ivanovn earned 500 total points
ID: 22757786
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:toshi_
ID: 22758160
greate, a step forward

0
 

Author Comment

by:toshi_
ID: 22758370
i know now how to check if my job is correctly entered.


0
 
LVL 10

Expert Comment

by:ivanovn
ID: 22758426
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question