success reported on failure

[jhoherz]

Summary: PG Cron executes procedure that pulls data via foreign wrapper to another PostgreSQL database. Connection exceeds idle in transaction timeout limit and foreign server closes connection. Local server tries to pull more data and fails. Transaction rolls back but PG Cron results in a success status, hiding the error from processing monitoring for issues.

ENV:
PostgreSQL 14.18 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-17), 64-bit
AWS GovCloud
pg_cron 1.6

** Outline of steps to reproduce: **

1. On local db, setup a table as a foreign data wrapper to another PG server using a connection role where the idle in transaction timeout is 5 seconds.
2. Procedure pulls data from table A, sleeps for 10 seconds, then pulls data from table A again.
3. Cron runs proceedure
4. Check on PG cron status results in succeeded but an abort message can be seen as well.

SQL:

-- on second server set idle timeout for role used in FWD:

ALTER ROLE fdw_role SET idle_in_transaction_session_timeout = '5s';

-- on local, create FDW (not sure I have all the steps and whitewashed it):

CREATE EXTENSION postgres_fdw;

CREATE SERVER f_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '{host name}', port '{NNNN}', dbname '{dbname}', updatable 'false', truncatable 'false');

CREATE USER MAPPING FOR local_role_name
SERVER f_server
OPTIONS (user 'fdw_role', password '{duh}');

-- proc for PG to run:

CREATE OR REPLACE PROCEDURE public.test_pg_cron()
LANGUAGE plpgsql
AS $procedure$

BEGIN

create table test_local as select * from foreign_ops.vlstatuses;
perform pg_sleep(10);
create table test_local2 as select * from foreign_ops.vlstatuses;

END;
$procedure$
;

** Execution and Results **
Setup PG Cron to run procedure "test_pg_cron".

check result of execution:

SELECT status, start_time, return_message
FROM cron.job_run_details
WHERE command like '%test_pg_cron%'
;

status = succeeded
return message= WARNING: no connection to the server CONTEXT: remote SQL command: ABORT TRANSACTION