Re: pgagent hangs forever in "r" state

Hi

On Mon, Jul 20, 2015 at 12:49 PM, Sanket Mehta
<sanket(dot)mehta(at)enterprisedb(dot)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