Re: pgagent hangs forever in "r" state

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')

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.

Please provide your suggestion for the same.

Regards,
Sanket Mehta
Sr Software engineer
Enterprisedb

On Tue, Jul 14, 2015 at 11:07 AM, Sanket Mehta <
sanket(dot)mehta(at)enterprisedb(dot)com> wrote:

> Sure Dave,
>
> I will try and let you know.
>
> Regards,
> Sanket Mehta
> Sr Software engineer
> Enterprisedb
>
> On Mon, Jul 13, 2015 at 7:44 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>
>> Sanket, can you reproduce this?
>>
>> On Fri, Jul 10, 2015 at 5:41 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> > On 07/10/2015 07:16 AM, Dave Page wrote:
>> >> Are you able to get a stacktrace from a running instance that's hung?
>> >
>> > Yes, but it's not doing anything, it's just polling for the next job.
>> >
>> >> Also, does setting the logging level to debug reveal anything useful
>> >> in the filesystem logs? (pgagent -l2 ....)
>> >
>> > Nope. And I scanned the postgresql log for SQL errors, and there's
>> nothing.
>> >
>> > What appears to be happening is:
>> >
>> > 1. pgagent picks up job
>> >
>> > 2. pgagent finds first step in that job
>> >
>> > 3. pgagent executes first step (successfully, according to the
>> > postgresql log, and according to a fake "touch file" job I added).
>> >
>> > 4. pgagent abandons the job without updating the status of either the
>> > job or the step.
>> >
>> > 5. pgagent starts polling for new jobs.
>> >
>> > 6. Repeat 1-5 until all jobs are in "r" state with an assigned agent,
>> > but not running.
>> >
>> > Note that this particular user has now abandoned pgagent and is
>> > switching to centrally managed cron, so my ability to get further
>> > troubleshooting information will be limited.
>> >
>> > According to apt-get, this is the latest pgagent package.
>> >
>> > --
>> > Josh Berkus
>> > PostgreSQL Experts Inc.
>> > http://pgexperts.com
>>
>>
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>
>