Using too many subtransactions in one transaction

Products

• VMware Tanzu Greenplum

Issue/Introduction

The error message below has been observed while running the following type of queries.

CONTEXT: PL/pgSQL function "is_number" line 2 during statement block entry
WARNING: Using too many subtransactions in one transaction. (seg1 slice4 xxx.xxx.xxx.xxx:40001 pid=21515)
HINT: Close open transactions soon to avoid wraparound problems.

begin;
query
~~~ snip
query
end;

Environment

Product Version: 5.0
OS: RHEL 7

Resolution

2020-04-02 08:55:10.286267 XXT,"xxxx","xxxx",p21515,th-216070368,"172.28.8.237","7219",2020-04-02 08:52:00
XXT,12790,con1868,cmd201,seg1,slice4,dx3396,x12790,sx1,"WARNING","01000","Using too many subtransactions in one transaction.",,"Close open transactions soon to avoid wraparound
problems.",,,"PL/pgSQL function ""is_number"" line 2 during statement block entry","

Looking at the log file under pg_log directory, the error message above has been observed while running the below query.

 CREATE OR REPLACE FUNCTION schdm.is_number (
  num text
)
RETURNS boolean AS
$body$
DECLARE x NUMERIC;
BEGIN
    x = num::NUMERIC;
    RETURN TRUE;
  EXCEPTION WHEN others THEN
    RETURN FALSE;
END;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

It means the EXCEPTION handing inside the function creates a SUBTRANSACTION for every tuple processed and it's resulted in the iterations on the number of tuples is exceeding the substransaction warning limit.

Increase the value of gp_subtrans_warn_limit or rewrite the query in order to avoid performing both the query and the function in the same outer transaction.