after reinstalling postgresql and brand new install of pgAgent, i can not run my jobs, what 's the error?

i need to workd with jobs in postgresql,
i've installed pgAgent, script, service....create user postgres to logon....start it ....i have followed tutorials.

i can see the pgAgent Catalog in my postgres database with 8 tables,
as a test i've created a function in a database ( not postgres)
i then created a job for the Database i created the function in, this job contains only one step, the function call.

question :
if my database (not postgres) contains also the Catalog pgAgent, why after creating my job, infos are in the postgres pgAgent Catalog
and not in my database(notpostgres) pgAgent Catalog? even when creating my job i tell that the function is for my database(not postgres).

is it normal?

do i have to create all functions in my postgres database and create  jobs for my postgres database?


Erwin PombettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pgagent will use catalog specified by the service installation. So if you created a pgagent catalog in different database (lets call it "notpostgres") then when installing the pgagent service you should have something like this (for windows):

"C:\Program Files\pgAdmin III\pgAgent" INSTALL pgAgent -u postgres -p secret hostaddr= dbname=notpostgres user=postgres

or this (for *nix):

/path/to/pgagent hostaddr= dbname=notpostgres user=postgres

Read through the documentation if you need more info on how to install the service:
Erwin PombettAuthor Commented:
the thing is that i had install the catalogue in a database that i backed up and restored after my  new installation.
for the new installation i pass the command as follow, so i have two databases with the catalog.

"C:\Program Files\pgAdmin III\pgAgent" INSTALL pgAgent -u postgres -p myPass hostaddr= dbname=postgres user=postgres

i have my service running on windows, log on with the same user : postgres,
in total i have two services: one for the database and one for pgAgent.

i  created a job, the postgres catalog receive the infos in the tables as you explained to check in another post.

now, why is my job not running the only step i wrote , the call of a function : level_of_day(); ?
i know my function does the jos, as i can pass "select level_of_day();" in my sql interface (pgAdmin)
and verify in my tables.

thank you to pay attention to my question ivanovn.
whatever is necessary in order to find out why the thing is not working for me i can provide it...i think...
as i set my log to the maximum info.

thank you in advance.
Erwin PombettAuthor Commented:
this is what i have in logs,
after ask jobs  to "run now", forced in pgAdmin by a command in the contextual menu.
what is wrong with my jobs?

note: my job is create with one step, the step call a function that is declared in a database "not postgres", the job has to write in a table in the host database, the function name is write_email.

thank you for all info concerning this

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Erwin PombettAuthor Commented:
I am not sure I 100% follow what you are trying to do. Are you having a job in database A that is supposed to call a function defined in database B?

I am guessing now you have your job created correctly and it's in the database A catalog? When you run it though you are not getting your database B tables populated?

If that's the case, then you just have to make sure that when you create your step, you select which database step applies to. It should be in drop down menu of databases. Also you may want to add some print outs (using RAISE NOTICE) in your function to see if it's getting executed at all or if it's failing somewhere within the function. I'm not seeing anything in your log in regards with your function running.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Erwin PombettAuthor Commented:
hello ivanovn, thanks for reply

i have installed pgAgent as explained in pgAgent tutorial,
created the user,
run the script for having the catalog created in the principal database "postgres",
and all the necessary to have the service started on my windows machine.

now about the job, i have created a function that i know is working as i when i fire it from
my sql pgAdmin interface i see the work's done,

i created a job for the database that contain the function,
the job contains 1 step, the step is a call to the function.

my problem:
i have followed the procedure to be able to create jobs, but i can not make them work.

my job's name is write_email, if you parse the pgadmin.log with this string you'll come across

how to find out what's the problem?

thank you for reply,

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.