Continue loop when dblink SQL statement timeout occurs

January 19, 2023

I’m writing a procedure that loops over several remote Databases using dblink, I want to include statement timeout to prevent queries hanging too long. However if such timeout occurs, whole procedure fails with:

ERROR: canceling statement due to statement timeout
SQL state: 57014
Context: while executing query on dblink connection named xxx

I want to ignore it and continue the loop.

Normally such code allows to skip exception throwing notice only, but not with dblink query canceled.

do $$
declare
exceptionMsg text;
BEGIN
 select * from foo;
 exception when others then get stacked diagnostics exceptionMsg = message_text;
     raise notice ' ******EXCEPTION*******
     %
     **************', exceptionMsg;  
END;
 $$

It’s too long to include whole procedure here, but it loops over database and commits results after each database. Everything works fine, except handling these timeouts, part of the code looks like that:

for rec in (select dbc.db_name, dbc.con_string || ' options = ''-c statement_timeout='||_queryTimeout*1000||''' ' as con_string
            from db_connections dbc
            )
LOOP

PERFORM dblink_connect(rec.db_name, rec.con_string); 

raise notice '% start',  rec.db_name ;

BEGIN
    insert into results_tbl (db_name, value, query_text)
        select rec.db_name, value, _queryText
        from dblink(rec.db_name, format($query$
            select json_agg(x.*)::text from (%1$s)x -- it's like this to avoid declaring every column used in the query
            $query$, _queryText 
        ) ) r (value text);

exception when others then get stacked diagnostics exceptionMsg = message_text;
     raise notice ' ******EXCEPTION*******
     %
     **************', exceptionMsg;  
END;

PERFORM dblink_disconnect( rec.db_name );
COMMIT;

raise notice '% done',  rec.db_name ;
END LOOP;