Hi On Mon, Jul 20, 2015 at 12:49 PM, Sanket Mehta <sanket.me...@enterprisedb.com> wrote: > Hi Dave, > > I have tried to reproduce the same in my system by resetting the pgagent > sequences to 1. > > Below is the results I have came across: > > the job is not getting executed and its current status is "i" (no > steps found ) although I have specified 2 steps for that job. > > Below is the log entry in postgreSQL logs: > > 2015-07-20 15:35:14 IST ERROR: duplicate key value violates unique > constraint "pga_joblog_pkey" > 2015-07-20 15:35:14 IST DETAIL: Key (jlgid)=(3) already exists. > 2015-07-20 15:35:14 IST STATEMENT: INSERT INTO > pgagent.pga_joblog(jlgid, jlgjobid, jlgstatus) VALUES (3, 1, 'r')
OK - I'm not sure that's related to the issue in this thread though. > So my analysis is: > > as sequences are getting reset before the job starts, it tries to > insert jlgid directly from nextvalue of its sequence which is already > present in the table. same thing happens for jobsteplog and joblog tables > also. > > Suggestion to resolve the issue: > > get the max value of ID from table before insert query and run the > sequence till we get the max(ID)+1 and then we execute the insert statement > with this new value as ID. We're not doing that - it'll be a performance killer. The user needs to not muck about with their sequences. The real questions are: 1) Why doesn't the insert error appear in the pgAgent log? 2) Can you reproduce Josh's issue where the first step in a job runs, but the second doesn't (without touching the sequences)? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
