Re: pgagent hangs forever in "r" state - Mailing list pgadmin-support
| From | Sanket Mehta |
|---|---|
| Subject | Re: pgagent hangs forever in "r" state |
| Date | |
| Msg-id | CA+yw=mOr+g_3KdpkUmxWBn6_mq1g275umxq2K1O-Bny0=oR=Lw@mail.gmail.com Whole thread Raw |
| In response to | Re: pgagent hangs forever in "r" state (Sanket Mehta <sanket.mehta@enterprisedb.com>) |
| Responses |
Re: pgagent hangs forever in "r" state
|
| List | pgadmin-support |
Hi Dave,
I have tried to reproduce the same in my system by resetting the pgagent sequences to 1.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.
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.mehta@enterprisedb.com> wrote:
Sure Dave,I will try and let you know.Regards,Sanket MehtaSr Software engineerEnterprisedbOn Mon, Jul 13, 2015 at 7:44 PM, Dave Page <dpage@pgadmin.org> wrote:Sanket, can you reproduce this?
On Fri, Jul 10, 2015 at 5:41 PM, Josh Berkus <josh@agliodbs.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
pgadmin-support by date: