Plpgsql and Exception Handling

Products

• VMware Tanzu Greenplum

Issue/Introduction

Here is a quick article on how exception works on Pivotal Greenplum.

 

Resolution

PLPGSQL functions in Greenplum work a bit different than that of Postgres.

Because there is a master/slaves architecture, transaction is handled at two levels, master and segments. Thus, an exception is only respected at master whereas is invisible to segments which can cause catalog/data issues.

Here are some examples to demonstrate the issues 

• Exceptions: EXCEPTION Error with CREATE within PLPGSQL (Causes catalog issue

  generic=# create or replace function testddlexception() returns void as
  generic-# $$
  generic$# begin
  generic$# create table ttt1(a int);
  generic$# select 1/0; -- force exception
  generic$# exception when others then
  generic$# raise notice 'Exception';
  generic$# end
  generic$# $$ language plpgsql;
  CREATE FUNCTION
  Time: 212.074 ms
  
  generic=# SELECT testddlexception();
  NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
  HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
  CONTEXT: SQL statement "create table ttt1(a int)"
  PL/pgSQL function "testddlexception" line 2 at SQL statement
  NOTICE: Exception
  WARNING: The distributed transaction 'Abort [Prepared]' broadcast failed to one or more segments for gid = 1394146823-0000015237.
  NOTICE: Releasing segworker groups to retry broadcast.
  NOTICE: Retry of the distributed transaction 'Abort Prepared' broadcast succeeded to the segments for gid = 1394146823-0000015237.
  ERROR: The distributed transaction 'Prepare' broadcast failed to one or more segments for gid = 1394146823-0000015237. (cdbtm.c:630)

• EXCEPTION error with UPDATE within plpgsql (causes data issues)

  generic=# create or replace function testdmlexception() returns void as
  generic-# $$
  generic$# begin
  generic$# update ttt2 set col2 = 5 where col1 > 0;
  generic$# select 1/0;
  generic$# exception when others then
  generic$# raise notice 'Exception';
  generic$# end
  generic$# $$ language plpgsql;
  CREATE FUNCTION
  Time: 30.218 ms
  
  generic=# create table ttt2 (col1 int, col2 int) distributed by (col1);
  CREATE TABLE
  Time: 312.121 ms
  generic=# INSERT INTO ttt2 select i,i from generate_series(1,5)i;
  INSERT 0 5
  Time: 349.232 ms
  generic=# SELECT * from ttt2 order by col1;
  col1 | col2
  ------+------
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5
  (5 rows)
  
  Time: 2.638 ms
  generic=# SELECT testdmlexception();
  NOTICE: Exception
  testdmlexception
  ------------------
  
  (1 row)
  Time: 28.008 ms
  
  generic=# SELECT * from ttt2 order by col1; <<<<< Even after exception, values are updated and not rolled back
  col1 | col2
  ------+------
  1 | 5
  2 | 5
  3 | 5
  4 | 5
  5 | 5
  (5 rows)

• EXCEPTION error with DELETE within plpgsql (causes data issues)

  generic=# create or replace function testdmlexception1() returns void as
  generic-# $$
  generic$# begin
  generic$# delete from ttt2 where col1 > 0;
  generic$# select 1/0;
  generic$# exception when others then
  generic$# raise notice 'Exception';
  generic$# end
  generic$# $$ language plpgsql;
  CREATE FUNCTION
  Time: 209.182 ms
  generic=# SELECT * from ttt2;
  col1 | col2
  ------+------
  3 | 5
  2 | 5
  4 | 5
  1 | 5
  5 | 5
  (5 rows)
  
  Time: 19.987 ms
  generic=# SELECT testdmlexception1();
  NOTICE: Exception
  testdmlexception1
  -------------------
  
  (1 row)
  
  Time: 35.937 ms
  generic=# SELECT * from ttt2; <<<<< Even after exception, values are deleted and transaction is not rolled back
  col1 | col2
  ------+------
  (0 rows)
  
  Time: 32.480 ms

• EXCEPTION error with truncate within plpgsql (causes data issues)

  generic=# create or replace function testdmlexception2() returns void as
  generic-# $$
  generic$# begin
  generic$# truncate table ttt2;
  generic$# select 1/0;
  generic$# exception when others then
  generic$# raise notice 'Exception';
  generic$# end
  generic$# $$ language plpgsql;
  CREATE FUNCTION
  Time: 35.845 ms
  generic=# SELECT * from ttt2
  generic-# ;
  col1 | col2
  ------+------
  3 | 3
  1 | 1
  4 | 4
  5 | 5
  2 | 2
  (5 rows)
  
  Time: 38.022 ms
  generic=# select testdmlexception2();
  NOTICE: Exception
  testdmlexception2
  -------------------
  
  (1 row)
  
  Time: 115.848 ms
  generic=# SELECT * from ttt2; <<<<< Even after exception, values are truncated and transaction is not rolled back
  
  col1 | col2
  ------+------
  (0 rows)
  
  Time: 22.310 ms

• EXCEPTION error with INSERT within plpgsql (causes data issues)

  generic=# truncate table ttt2;
  TRUNCATE TABLE
  Time: 138.454 ms
  generic=# create or replace function test_exception() returns void as
  generic-# $$
  generic$# begin
  generic$# insert into ttt2 values (1);
  generic$# select 1/0; -- force exception, need to rollback everything
  generic$# exception when others then
  generic$# raise notice 'Exception';
  generic$# end
  generic$# $$ language plpgsql;
  CREATE FUNCTION
  Time: 58.896 ms
  generic=#
  generic=# SELECT * from ttt2;
  col1 | col2
  ------+------
  (0 rows)
  
  Time: 24.103 ms
  generic=# SELECT test_exception();
  NOTICE: Exception
  test_exception
  ----------------
  
  (1 row)
  
  Time: 29.628 ms
  generic=# SELECT * from ttt2; <<<<< Even after exception, values are inserted and transaction is not rolled back
  col1 | col2
  ------+------
  1 |
  (1 row)
  
  Time: 25.342 ms

Note: Only metadata/catalog issues can be reported/found using gpcheckcat. Data issues cannot be found as there is no way to check. Hence, use Exception cautiously or better ignore it until its fully supported.

Additional Information

+ Environment:

Pivotal Greenplum Database (GPDB) all versions