Re: pgagent hangs forever in "r" state

Поиск
Список
Период
Сортировка
От Sanket Mehta
Тема Re: pgagent hangs forever in "r" state
Дата
Msg-id CA+yw=mOr+g_3KdpkUmxWBn6_mq1g275umxq2K1O-Bny0=oR=Lw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: pgagent hangs forever in "r" state  (Sanket Mehta <sanket.mehta@enterprisedb.com>)
Ответы Re: pgagent hangs forever in "r" state  (Dave Page <dpage@pgadmin.org>)
Список pgadmin-support
Дерево обсуждения
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.mehta@enterprisedb.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@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 по дате отправления:

Предыдущее
От: Dave Page
Дата:
Сообщение: Re: Please add timestamps to pgagent log
Следующее
От: Erwan Begoc
Дата:
Сообщение: PGadmin 1.20.0 / Apple MBP 13 (early 2015) SwissGerman Keyboard [!¨][^`] keys bug