Re: PgAgent daemon terminated itself when it was running a time-consuming job.

Hi Neel and PgAdmin,

Finally, I have come back on this issue now. Thank you Neel for replying. I
tried to reproduce this issue in debug mode in the first time, but it then
got passed. Now, in the second time, I am able to reproduce this error in
DEBUG modes. Before I showed you the logs, let me briefly describe what the
PgAgent job is doing.

PgAgent Job Description: I want to periodically synchronize a few tables
stored in MySQL to PostgreSQL. Now I am using mysql_fdw FDW plugin to let
Postgres talk to MySQL. I wrote a few stored procedures for each one of
those tables in order to pull, index and rename new tables and delete old
tables. These functions worked when I ran them without PgAgent. Since these
databases are proprietary to our company, I am sorry that I am not able to
make a test case for reproduction, but, as you can imagine, they are simple
functions except that one or two have 10M+ records but I do not think it
will be an issue in this case.

In this PgAgent Job, I have three steps: 1st step is calling 4 functions to
synchronize 4 tables; 2nd step is calling 1 function to synchronize a large
table named as "md_location", and 3rd step is to vacuum and analyze those
tables. The problem

The problem happened at the beginning of the 2nd step.

PgAgent Logs （I have deleted meaningless repetitive logs:):

Tue Nov 13 11:26:58 2018 DEBUG: Sleeping...

Tue Nov 13 11:27:03 2018 DEBUG: Clearing inactive connections

Tue Nov 13 11:27:03 2018 DEBUG: Connection stats: total - 1, free - 0,
deleted - 0

Tue Nov 13 11:27:03 2018 DEBUG: Checking for jobs to run

Tue Nov 13 11:27:03 2018 DEBUG: Creating job thread for job 6

Tue Nov 13 11:27:03 2018 DEBUG: Destroying job thread for job 6

Tue Nov 13 11:27:03 2018 DEBUG: Destroying job thread for job 6

Tue Nov 13 11:27:03 2018 DEBUG: Destroying job thread for job 6

Tue Nov 13 11:27:03 2018 DEBUG: Sleeping...

Tue Nov 13 11:27:03 2018 DEBUG: Creating DB connection:
user=pgagent_user hostaddr=127.0.0.1
port=5432 dbname=postgres

Tue Nov 13 11:27:03 2018 DEBUG: Parsing connection information...

Tue Nov 13 11:27:03 2018 DEBUG: Allocating new connection for the database
with connection string: user=pgagent_user dbname=postgres
hostaddr=127.0.0.1 port=5432...

Tue Nov 13 11:27:03 2018 DEBUG: Starting job: 6

Tue Nov 13 11:27:03 2018 DEBUG: Creating DB connection:
user=pgagent_user hostaddr=127.0.0.1
port=5432 dbname=gis_dev

Tue Nov 13 11:27:03 2018 DEBUG: Parsing connection information...

Tue Nov 13 11:27:03 2018 DEBUG: Allocating new connection for the database
with connection string: user=pgagent_user dbname=gis_dev hostaddr=127.0.0.1
port=5432...

Tue Nov 13 11:27:03 2018 DEBUG: Executing SQL step 10(part of job 6)

NOTICE: table "md_location_classification_raw" does not exist, skipping

Tue Nov 13 11:29:03 2018 DEBUG: Sleeping...

NOTICE: table "md_location_classification_type_raw" does not exist,
skipping

Tue Nov 13 11:30:28 2018 DEBUG: Sleeping...

NOTICE: identifier
"md_location_classification_type_local_md_location_classification_id_idx"
will be truncated to
"md_location_classification_type_local_md_location_classificatio"

Tue Nov 13 11:30:38 2018 DEBUG: Sleeping...

NOTICE: table "md_entity_raw" does not exist, skipping

Tue Nov 13 11:40:58 2018 DEBUG: Sleeping...

NOTICE: table "md_entity_location_raw" does not exist, skipping

Tue Nov 13 11:50:14 2018 DEBUG: Sleeping...

Tue Nov 13 11:50:17 2018 DEBUG: Parsing connection information...

Tue Nov 13 11:50:17 2018 DEBUG: Returning the connection to the connection
pool: 'user=pgagent_user dbname=gis_dev hostaddr=127.0.0.1 port=5432'...

Tue Nov 13 11:50:17 2018 DEBUG: Parsing connection information...

Tue Nov 13 11:50:17 2018 DEBUG: Using the existing connection
'user=pgagent_user dbname=gis_dev hostaddr=127.0.0.1 port=5432'...

Tue Nov 13 11:50:17 2018 DEBUG: Executing SQL step 11(part of job 6)

Tue Nov 13 11:50:18 2018 WARNING: Query error: PANIC: ERRORDATA_STACK_SIZE
exceeded

CONTEXT: SQL statement "import foreign schema schema_abc

limit to (md_location)

from SERVER c2f_prod

into fd_mysql"

PL/pgSQL function eugene.extract_md_location_raw() line 6 at SQL statement

SSL SYSCALL error: EOF detected

Tue Nov 13 11:50:18 2018 DEBUG: Parsing connection information...

Tue Nov 13 11:50:18 2018 DEBUG: Returning the connection to the connection
pool: 'user=pgagent_user dbname=gis_dev hostaddr=127.0.0.1 port=5432'...

WARNING: terminating connection because of crash of another server process

DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.

HINT: In a moment you should be able to reconnect to the database and
repeat your command.

Tue Nov 13 11:50:18 2018 WARNING: Query error: server closed the connection
unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

Tue Nov 13 11:50:18 2018 DEBUG: Parsing connection information...

Tue Nov 13 11:50:18 2018 DEBUG: Returning the connection to the connection
pool: 'user=pgagent_user dbname=postgres hostaddr=127.0.0.1 port=5432'...

Tue Nov 13 11:50:18 2018 DEBUG: Completed job: 6

Tue Nov 13 11:50:18 2018 DEBUG: Destroying job thread for job 6

Tue Nov 13 11:50:19 2018 DEBUG: Clearing inactive connections

Tue Nov 13 11:50:19 2018 DEBUG: Connection stats: total - 3, free - 2,
deleted - 2

Tue Nov 13 11:50:19 2018 DEBUG: Checking for jobs to run

WARNING: terminating connection because of crash of another server process

DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.

HINT: In a moment you should be able to reconnect to the database and
repeat your command.

Tue Nov 13 11:50:19 2018 WARNING: Query error: server closed the connection
unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

Tue Nov 13 11:50:19 2018 ERROR: Failed to query jobs table!

Thank you.

Yujie Wang (Welcome to call me Eugene, for your pronunciation convenience)
GIS Developer
Connected2fiber
Linkedin : https://www.linkedin.com/in/yujie-wang-61a129a0/

On Fri, Sep 7, 2018 at 12:37 AM Neel Patel <neel(dot)patel(at)enterprisedb(dot)com>
wrote:

> Hi,
>
> Can you please share the pgAgent logs in debug mode ? If possible, also
> share the job details or screenshots so that we can reproduce at our end.
>
> Thanks,
> Neel Patel
>
> On Thu, Sep 6, 2018 at 11:30 PM, Eugene Wang <eugenewangfw(at)gmail(dot)com>
> wrote:
>
>> My environment is Ubuntu 16.04 LTS, Postgres 9.6.8 with PgAgent 4.0
>> installed and setup successfully.
>>
>> I tried to use a PgAgent Job to pull data from a MySQL table using FDW on
>> a routine basis in order to keep a updated copy of the mysql table. This
>> job has two steps. When I run those steps directly, the first one would
>> take less than 30 mins and the second one usually takes 44 minutes.
>> However, when I try to run it with PgAgent, my logs showed me that only the
>> first step was finished, and PgAgent daemon was terminated during this job
>> and it would never execute the second step.
>>
>> Is there a solution on this issue? If it is beyond the capability of
>> PgAgent, what solution would help me? the system cron? I really like the
>> feature of PgAgent that steps could be executed one by one if the previous
>> is done, so I would hope I could just set up some configuration and get
>> this fixed.
>>
>> Thank you.
>>
>>
>> Yujie Wang (Welcome to call me Eugene, for your pronunciation
>> convenience)
>> GIS Developer
>> Connected2fiber
>> Linkedin : https://www.linkedin.com/in/yujie-wang-61a129a0/
>>
>
>