Pivotal Knowledge Base

Follow

Plpgsql and Exception handling

Environment

Product Version
Pivotal Greenplum All Versions

Goal

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

Solution

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.

Comments

  • Avatar
    Ayub M

    Which version exhibits above behavior? I ran in 4.3.3.1 and it works as below -
    emcas_dev01=# create table ttt2 (col1 int, col2 int) distributed by (col1);
    CREATE TABLE
    emcas_dev01=# INSERT INTO ttt2 select i,i from generate_series(1,5)i;
    INSERT 0 5
    emcas_dev01=# SELECT * from ttt2 order by col1;
    col1 | col2
    ------+------
    1 | 1
    2 | 2
    3 | 3
    4 | 4
    5 | 5
    (5 rows)

    emcas_dev01=# create or replace function testdmlexception() returns void as
    emcas_dev01-# $$
    emcas_dev01$# begin
    emcas_dev01$# update ttt2 set col2 = 5 where col1 > 0;
    emcas_dev01$# select 1/0;
    emcas_dev01$# exception when others then
    emcas_dev01$# raise notice 'Exception';
    emcas_dev01$# end
    emcas_dev01$# $$ language plpgsql;
    CREATE FUNCTION
    emcas_dev01=# SELECT testdmlexception();

    testdmlexception

    (1 row)

    emcas_dev01=# SELECT * from ttt2 order by col1;
    col1 | col2
    ------+------
    1 | 1
    2 | 2
    3 | 3
    4 | 4
    5 | 5
    (5 rows)

  • Avatar
    Gurupreet Singh Bhatia

    I got same error
    WARNING: The distributed transaction 'Abort [Prepared]' broadcast failed to one or more segments for gid = 1443076134-0004882834.
    NOTICE: Releasing segworker groups to retry broadcast.
    NOTICE: Retry of the distributed transaction 'Abort Prepared' broadcast succeeded to the segments for gid = 1443076134-0004882834.

    ERROR: The distributed transaction 'Prepare' broadcast failed to one or more segments for gid = 1443076134-0004882834. (cdbtm.c:630)

    ********** Error **********

    ERROR: The distributed transaction 'Prepare' broadcast failed to one or more segments for gid = 1443076134-0004882834. (cdbtm.c:630)
    SQL state: XX000

    Resolution:
    It was data issue at my end, developer casting some field to timestamp which was not castable

    When i run a simple select
    ERROR: date/time field value out of range: "213123" (seg7 slice1 SRDCB0003GPM03:40001 pid=25918)
    SQL state: 22008

Powered by Zendesk