| -- Test exception handling in UDFs |
| -- ALTER table success and failure case in function with exception |
| CREATE TABLE test_exception_error (a INTEGER NOT NULL); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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. |
| INSERT INTO test_exception_error select * from generate_series(1, 100); |
| -- SUCCESS case |
| CREATE OR REPLACE FUNCTION test_plpgsql() RETURNS VOID AS |
| $$ |
| BEGIN |
| BEGIN |
| ALTER TABLE test_exception_error set with ( reorganize='true') distributed randomly; |
| EXCEPTION |
| WHEN OTHERS THEN |
| BEGIN |
| RAISE NOTICE 'catching the exception ...'; |
| END; |
| END; |
| END; |
| $$ |
| LANGUAGE plpgsql; |
| SELECT test_plpgsql(); |
| test_plpgsql |
| -------------- |
| |
| (1 row) |
| |
| SELECT count(*) FROM test_exception_error; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| INSERT INTO test_exception_error SELECT * FROM generate_series(101, 200); |
| -- FAILURE scenario |
| CREATE OR REPLACE FUNCTION test_plpgsql() RETURNS VOID AS |
| $$ |
| BEGIN |
| BEGIN |
| ALTER TABLE test_exception_error set with ( reorganize='true') distributed randomly; |
| INSERT INTO test_exception_error(a) VALUES(NULL); |
| EXCEPTION |
| WHEN OTHERS THEN |
| BEGIN |
| RAISE NOTICE 'catching the exception ...'; |
| END; |
| END; |
| END; |
| $$ |
| LANGUAGE plpgsql; |
| -- Raises expected Exception |
| SELECT test_plpgsql(); |
| NOTICE: catching the exception ... |
| test_plpgsql |
| -------------- |
| |
| (1 row) |
| |
| SELECT count(*) FROM test_exception_error; |
| count |
| ------- |
| 200 |
| (1 row) |
| |
| -- INSERT into table case in function with exception |
| -- |
| DROP TABLE IF EXISTS test_exception_error; |
| DROP FUNCTION IF EXISTS test_plpgsql(); |
| CREATE TABLE test_exception_error (a INTEGER NOT NULL); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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. |
| CREATE OR REPLACE FUNCTION test_plpgsql() RETURNS VOID AS |
| $$ |
| BEGIN |
| BEGIN |
| INSERT INTO test_exception_error(a) VALUES(1), (2), (3), (NULL), (4), (5), (6), (7), (8), (9); |
| EXCEPTION |
| WHEN OTHERS THEN |
| BEGIN |
| RAISE NOTICE 'catching the exception ...'; |
| END; |
| END; |
| END; |
| $$ |
| LANGUAGE plpgsql; |
| -- Raises unexpected Exception but should not fail the command |
| SELECT test_plpgsql(); |
| NOTICE: catching the exception ... |
| test_plpgsql |
| -------------- |
| |
| (1 row) |
| |
| -- Tests with create table failure in function with exception |
| -- |
| -- Create schema needed |
| CREATE SCHEMA s1; |
| CREATE SCHEMA s2; |
| -- Creating function |
| CREATE OR REPLACE FUNCTION s1.column_type_override_gp() RETURNS integer AS |
| $$ |
| declare |
| v_source_schema character varying(30); |
| v_source_table character varying(30); |
| begin |
| v_source_schema:='s2' ; |
| v_source_table:='corrupted_catalog' ; |
| |
| -- Issue was not observed if we use schemaname with the newly renamed table in the below query |
| execute('ALTER TABLE ' ||v_source_schema || '.'|| v_source_table || ' rename to ' || v_source_table ||'temp'); |
| -- The below query has raised exception |
| execute('CREATE TABLE ' ||v_source_schema || '.'|| v_source_table ||'(feed_no char(10),feed_no char(10))WITH ( OIDS=FALSE ) DISTRIBUTED BY ( feed_no )'); |
| execute('DROP TABLE ' ||v_source_schema || '.'|| v_source_table ||'temp'); |
| RETURN 1; |
| -- when exception section has not been used then the cleanup done successfully and the table has been created successfully from the next section |
| exception |
| WHEN OTHERS THEN |
| RAISE NOTICE 'EXCEPTION HIT !!!'; |
| RETURN 0; |
| end; |
| $$ |
| LANGUAGE plpgsql SECURITY DEFINER; |
| |
| CREATE TABLE s2.corrupted_catalog |
| ( |
| feed_no character varying(4000) |
| ) |
| WITH ( OIDS=FALSE ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'feed_no' as the Apache Cloudberry 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. |
| SELECT s1.column_type_override_gp(); |
| NOTICE: EXCEPTION HIT !!! |
| column_type_override_gp |
| ------------------------- |
| 0 |
| (1 row) |
| |
| SELECT oid, relname, relnatts FROM pg_class WHERE relname = 'corrupted_catalogtemp' |
| AND relnamespace = (SELECT oid from pg_namespace where nspname = 's2') ; |
| oid | relname | relnatts |
| -----+---------+---------- |
| (0 rows) |
| |
| -- Tests exception not generated by sql statement, explicit BEGIN and COMMIT/ABORT in function with exception |
| -- |
| CREATE TABLE test_trans_tab ( a int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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. |
| -- Exception hit case |
| CREATE OR REPLACE FUNCTION test_trans() RETURNS INT AS |
| $$ |
| DECLARE |
| ct bigint; |
| BEGIN |
| INSERT INTO test_trans_tab VALUES (1); |
| ct = 1/0; |
| INSERT INTO test_trans_tab VALUES (2); |
| EXCEPTION WHEN OTHERS THEN |
| RAISE NOTICE 'EXCEPTION HIT !!!'; |
| RETURN 0; |
| END; |
| $$ |
| LANGUAGE 'plpgsql'; |
| -- Function called which hits exception in explicit transaction block which commits |
| BEGIN; |
| SELECT test_trans(); |
| NOTICE: EXCEPTION HIT !!! |
| test_trans |
| ------------ |
| 0 |
| (1 row) |
| |
| INSERT INTO test_trans_tab VALUES (3); |
| COMMIT; |
| SELECT * FROM test_trans_tab; |
| a |
| --- |
| 3 |
| (1 row) |
| |
| -- Function called which hits exception in explicit transaction block which aborts |
| BEGIN; |
| SELECT test_trans(); |
| NOTICE: EXCEPTION HIT !!! |
| test_trans |
| ------------ |
| 0 |
| (1 row) |
| |
| INSERT INTO test_trans_tab VALUES (4); |
| ABORT; |
| SELECT * FROM test_trans_tab; |
| a |
| --- |
| 3 |
| (1 row) |
| |
| -- Tests loop and update in function with exception |
| -- |
| CREATE TABLE test_loop_tab (a INT NOT NULL, b TEXT); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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. |
| CREATE FUNCTION insert_db(key INT, data TEXT) RETURNS VOID AS |
| $$ |
| BEGIN |
| LOOP |
| -- first try to update the key |
| UPDATE test_loop_tab SET b = data WHERE a = key; |
| IF found THEN |
| RETURN; |
| END IF; |
| -- not there, so try to insert the key |
| BEGIN |
| INSERT INTO test_loop_tab(a,b) VALUES (NULL, data); |
| RETURN; |
| EXCEPTION WHEN OTHERS THEN |
| INSERT INTO test_loop_tab(a,b) VALUES (2, 'dummy'); |
| INSERT INTO test_loop_tab(a,b) VALUES (key, 'dummy'); |
| END; |
| END LOOP; |
| END; |
| $$ |
| LANGUAGE plpgsql; |
| SELECT insert_db(1, 'david'); |
| insert_db |
| ----------- |
| |
| (1 row) |
| |
| SELECT * from test_loop_tab; |
| a | b |
| ---+------- |
| 1 | david |
| 2 | dummy |
| (2 rows) |
| |
| INSERT INTO test_loop_tab select *,'abcdefghijklmnopqrstuvwxyz' from generate_series(1, 100); |
| CREATE OR REPLACE FUNCTION insert_db(flag INT) RETURNS INT AS |
| $$ |
| DECLARE |
| rec record; |
| x INT; |
| BEGIN |
| FOR rec IN |
| SELECT * |
| FROM test_loop_tab |
| WHERE a % 2 = 0 |
| LOOP |
| x = rec.a * flag; |
| IF x = 400 THEN |
| INSERT INTO test_loop_tab VALUES (NULL); |
| ELSE |
| INSERT INTO test_loop_tab VALUES (rec.a); |
| END IF; |
| END LOOP; |
| RETURN 1; |
| EXCEPTION WHEN OTHERS THEN |
| BEGIN |
| INSERT INTO test_loop_tab VALUES (999), (9999), (99999); |
| RAISE NOTICE 'Exception Hit !!!'; |
| RETURN -1; |
| END; |
| END; |
| $$ |
| LANGUAGE plpgsql; |
| SELECT insert_db(1); |
| insert_db |
| ----------- |
| 1 |
| (1 row) |
| |
| SELECT count(*) FROM test_loop_tab; |
| count |
| ------- |
| 153 |
| (1 row) |
| |
| SELECT insert_db(200); |
| NOTICE: Exception Hit !!! |
| insert_db |
| ----------- |
| -1 |
| (1 row) |
| |
| SELECT count(*) FROM test_loop_tab; |
| count |
| ------- |
| 156 |
| (1 row) |
| |
| -- Tests nested function blocks with exception |
| -- |
| DROP FUNCTION IF EXISTS test_plpgsql() CASCADE; |
| CREATE TABLE test_nested_blocks_tab (a INTEGER NOT NULL); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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. |
| INSERT INTO test_nested_blocks_tab SELECT * from generate_series(1, 100); |
| CREATE OR REPLACE FUNCTION test_plpgsql() RETURNS VOID AS |
| $$ |
| BEGIN |
| BEGIN |
| INSERT INTO test_nested_blocks_tab SELECT * from test_nested_blocks_tab; |
| BEGIN |
| INSERT INTO test_nested_blocks_tab(a) VALUES(1000),(NULL),(1001),(1002); |
| EXCEPTION |
| WHEN OTHERS THEN |
| BEGIN |
| RAISE NOTICE 'catching the exception ...1'; |
| END; |
| END; |
| BEGIN |
| INSERT INTO test_nested_blocks_tab VALUES (1), (NULL); |
| EXCEPTION |
| WHEN OTHERS THEN |
| BEGIN |
| RAISE NOTICE 'catching the exception ...2'; |
| END; |
| END; |
| INSERT INTO test_nested_blocks_tab SELECT * from test_nested_blocks_tab; |
| EXCEPTION |
| WHEN OTHERS THEN |
| BEGIN |
| RAISE NOTICE 'catching the exception ...3'; |
| END; |
| END; |
| END; |
| $$ |
| LANGUAGE plpgsql; |
| -- Raises unexpected Exception in function but cmd still should be successful |
| SELECT test_plpgsql(); |
| NOTICE: catching the exception ...1 |
| NOTICE: catching the exception ...2 |
| test_plpgsql |
| -------------- |
| |
| (1 row) |
| |
| SELECT count(*) from test_nested_blocks_tab; |
| count |
| ------- |
| 400 |
| (1 row) |
| |
| -- Tests master only function execution with exception block |
| -- |
| DROP FUNCTION IF EXISTS test_plpgsql() CASCADE; |
| CREATE TABLE test_master_only_function_tab (a INTEGER NOT NULL); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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. |
| CREATE OR REPLACE FUNCTION test_plpgsql() RETURNS VOID AS |
| $$ |
| BEGIN |
| BEGIN |
| PERFORM * from pg_class; |
| EXCEPTION |
| WHEN OTHERS THEN |
| BEGIN |
| RAISE NOTICE 'catching the exception ...'; |
| END; |
| END; |
| END; |
| $$ |
| LANGUAGE plpgsql; |
| INSERT INTO test_master_only_function_tab SELECT * FROM generate_series(1, 100); |
| SELECT test_plpgsql(); |
| test_plpgsql |
| -------------- |
| |
| (1 row) |
| |
| SELECT count(*) FROM test_master_only_function_tab; |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| -- Tests exception raised on master instead of segment in function with |
| -- exception |
| -- |
| CREATE OR REPLACE FUNCTION test_exception_block_fn() RETURNS VOID AS |
| $$ |
| BEGIN |
| CREATE TABLE test_exception_block(a int) DISTRIBUTED BY (a); |
| VACUUM pg_type; |
| EXCEPTION WHEN OTHERS THEN |
| RAISE NOTICE '%', SQLSTATE; |
| end; |
| $$ LANGUAGE plpgsql; |
| SELECT test_exception_block_fn(); |
| NOTICE: 25001 |
| test_exception_block_fn |
| ------------------------- |
| |
| (1 row) |
| |
| SELECT relname FROM pg_class WHERE relname = 'test_exception_block'; |
| relname |
| --------- |
| (0 rows) |
| |
| SELECT relname FROM gp_dist_random('pg_class') WHERE relname = 'test_exception_block'; |
| relname |
| --------- |
| (0 rows) |
| |
| -- Test explicit function call inside SAVEPOINT (sub-transaction) |
| BEGIN; |
| SAVEPOINT SP1; |
| SELECT test_exception_block_fn(); |
| NOTICE: 25001 |
| test_exception_block_fn |
| ------------------------- |
| |
| (1 row) |
| |
| ROLLBACK to SP1; |
| COMMIT; |
| SELECT relname FROM pg_class WHERE relname = 'test_exception_block'; |
| relname |
| --------- |
| (0 rows) |
| |
| SELECT relname FROM gp_dist_random('pg_class') WHERE relname = 'test_exception_block'; |
| relname |
| --------- |
| (0 rows) |
| |
| -- Test explicit function call inside SAVEPOINT (sub-transaction) |
| BEGIN; |
| SAVEPOINT SP1; |
| SELECT test_exception_block_fn(); |
| NOTICE: 25001 |
| test_exception_block_fn |
| ------------------------- |
| |
| (1 row) |
| |
| RELEASE SP1; |
| COMMIT; |
| SELECT relname FROM pg_class WHERE relname = 'test_exception_block'; |
| relname |
| --------- |
| (0 rows) |
| |
| SELECT relname FROM gp_dist_random('pg_class') WHERE relname = 'test_exception_block'; |
| relname |
| --------- |
| (0 rows) |
| |
| -- Tests invalid schema creation from function with exception |
| -- |
| DROP FUNCTION IF EXISTS test_exception_block_fn() CASCADE; |
| -- create base table |
| CREATE TABLE test_invalid_schema_creation_tab |
| ( |
| f1 smallint, |
| f2 smallint, |
| f3 smallint |
| ) |
| WITH ( OIDS=FALSE ) DISTRIBUTED BY (f1); |
| -- create function execution of which post fix avoids catalog corruption |
| -- without the fix, VIEW v1 used to get commited on segment and not on master |
| CREATE OR REPLACE FUNCTION test_exception_block_fn() RETURNS integer AS |
| $$ |
| begin |
| -- this view definition is valid |
| CREATE VIEW test_invalid_schema_creation_tab_v1 AS SELECT f1 FROM test_invalid_schema_creation_tab; |
| -- this view definition is invalid |
| CREATE VIEW test_invalid_schema_creation_tab_v2 AS SELECT f4 FROM test_invalid_schema_creation_tab; |
| return 1; |
| exception |
| WHEN OTHERS THEN |
| RAISE NOTICE 'EXCEPTION HIT !!!'; |
| return 0; |
| RAISE EXCEPTION 'ERROR 0'; |
| end; |
| $$ |
| LANGUAGE plpgsql SECURITY DEFINER; |
| --execute the function |
| SELECT * FROM test_exception_block_fn(); |
| NOTICE: EXCEPTION HIT !!! |
| test_exception_block_fn |
| ------------------------- |
| 0 |
| (1 row) |
| |
| SELECT relname FROM pg_class WHERE relname IN ('test_invalid_schema_creation_tab_v1','test_invalid_schema_creation_tab_v2'); |
| relname |
| --------- |
| (0 rows) |
| |
| -- no record returned i.e. v1 does not exist in the catalog |
| --let's try and create v1 |
| CREATE VIEW test_invalid_schema_creation_tab_v1 AS SELECT f1 FROM test_invalid_schema_creation_tab; |
| -- Should not throw ERROR: relation "test_invalid_schema_creation_tab_v1" already exists |
| DROP VIEW test_invalid_schema_creation_tab_v1; |
| -- Function call from savepoint which commits |
| BEGIN; |
| SAVEPOINT SP1; |
| SELECT * FROM test_exception_block_fn(); |
| NOTICE: EXCEPTION HIT !!! |
| test_exception_block_fn |
| ------------------------- |
| 0 |
| (1 row) |
| |
| RELEASE SP1; |
| COMMIT; |
| SELECT relname FROM pg_class WHERE relname IN ('test_invalid_schema_creation_tab_v1','test_invalid_schema_creation_tab_v2'); |
| relname |
| --------- |
| (0 rows) |
| |
| -- no record returned i.e. v1 does not exist in the catalog |
| --let's try and create v1 |
| CREATE VIEW test_invalid_schema_creation_tab_v1 AS SELECT f1 FROM test_invalid_schema_creation_tab; |
| -- Should not throw ERROR: relation "test_invalid_schema_creation_tab_v1" already exists |
| DROP VIEW test_invalid_schema_creation_tab_v1; |
| -- create function execution of which post fix avoids catalog corruption |
| CREATE OR REPLACE FUNCTION test_exception_block_fn() RETURNS integer AS |
| $$ |
| begin |
| -- this view definition is valid |
| CREATE VIEW test_invalid_schema_creation_tab_v1 AS SELECT f1 FROM test_invalid_schema_creation_tab; |
| return 1; |
| exception |
| WHEN OTHERS THEN |
| RAISE NOTICE 'EXCEPTION HIT !!!'; |
| return 0; |
| RAISE EXCEPTION 'ERROR 0'; |
| end; |
| $$ |
| LANGUAGE plpgsql SECURITY DEFINER; |
| -- Function call from savepoint which aborts |
| BEGIN; |
| SAVEPOINT SP1; |
| SELECT * FROM test_exception_block_fn(); |
| test_exception_block_fn |
| ------------------------- |
| 1 |
| (1 row) |
| |
| ROLLBACK TO SP1; |
| COMMIT; |
| SELECT relname FROM pg_class WHERE relname IN ('test_invalid_schema_creation_tab_v1','test_invalid_schema_creation_tab_v2'); |
| relname |
| --------- |
| (0 rows) |
| |
| -- no record returned i.e. v1 does not exist in the catalog |
| --let's try and create v1 |
| CREATE VIEW test_invalid_schema_creation_tab_v1 AS SELECT f1 FROM test_invalid_schema_creation_tab; |
| -- Should not throw ERROR: relation "test_exception_block_v1" already exists |
| -- Tests truncate table success and failure case in function with exception |
| -- |
| DROP FUNCTION IF EXISTS test_plpgsql() CASCADE; |
| CREATE TABLE test_truncate_tab (a INTEGER NOT NULL); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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. |
| INSERT INTO test_truncate_tab select * from generate_series(1, 100); |
| -- SUCCESS case |
| CREATE OR REPLACE FUNCTION test_plpgsql() RETURNS VOID AS |
| $$ |
| BEGIN |
| TRUNCATE TABLE test_truncate_tab; |
| INSERT INTO test_truncate_tab select * from generate_series(100, 150); |
| EXCEPTION WHEN OTHERS THEN |
| RAISE NOTICE 'catching the exception ...'; |
| END; |
| $$ |
| LANGUAGE plpgsql; |
| SELECT test_plpgsql(); |
| test_plpgsql |
| -------------- |
| |
| (1 row) |
| |
| SELECT count(*) FROM test_truncate_tab; |
| count |
| ------- |
| 51 |
| (1 row) |
| |
| INSERT INTO test_truncate_tab SELECT * FROM generate_series(150, 200); |
| -- FAILURE scenario |
| CREATE OR REPLACE FUNCTION test_plpgsql() RETURNS VOID AS |
| $$ |
| BEGIN |
| TRUNCATE TABLE test_truncate_tab; |
| INSERT INTO test_truncate_tab select * from generate_series(200, 250); |
| INSERT INTO test_truncate_tab(a) VALUES(NULL); |
| EXCEPTION WHEN OTHERS THEN |
| RAISE NOTICE 'catching the exception ...'; |
| END; |
| $$ |
| LANGUAGE plpgsql; |
| -- Raises Exception |
| SELECT test_plpgsql(); |
| NOTICE: catching the exception ... |
| test_plpgsql |
| -------------- |
| |
| (1 row) |
| |
| SELECT count(*) FROM test_truncate_tab; |
| count |
| ------- |
| 102 |
| (1 row) |
| |
| INSERT INTO test_truncate_tab select * from generate_series(250, 300); |
| -- FAILURE scenario |
| CREATE OR REPLACE FUNCTION test_plpgsql() RETURNS VOID AS |
| $$ |
| BEGIN |
| INSERT INTO test_truncate_tab select * from generate_series(300, 350); |
| INSERT INTO test_truncate_tab(a) VALUES(NULL); |
| EXCEPTION WHEN OTHERS THEN |
| BEGIN |
| TRUNCATE TABLE test_truncate_tab; |
| RAISE NOTICE 'catching the exception ...'; |
| END; |
| END; |
| $$ |
| LANGUAGE plpgsql; |
| -- Raises Exception |
| SELECT test_plpgsql(); |
| NOTICE: catching the exception ... |
| test_plpgsql |
| -------------- |
| |
| (1 row) |
| |
| SELECT count(*) FROM test_truncate_tab; |
| count |
| ------- |
| 0 |
| (1 row) |
| |
| INSERT INTO test_truncate_tab select * from generate_series(350, 400); |
| SELECT count(*) FROM test_truncate_tab; |
| count |
| ------- |
| 51 |
| (1 row) |
| |
| -- Tests exception handling of GPDB PL/PgSQL UDF |
| -- It exercises: |
| -- 1. PROTOCOL or SQL type of dtm_action_target |
| -- 2. Various levels of sub-transactions |
| -- 3. dtm_action_protocol(PROTOCOL): subtransaction_begin, subtransaction_rollback or subtransaction_release |
| -- 4. dtm_action: fail_begin_command, fail_end_command or panic_begin_comand |
| -- |
| -- debug_dtm_action: Using this can specify what action to be |
| -- triggered/simulated and at what point like error / panic / delay |
| -- and at start or end command after receiving by the segment. |
| -- debug_dtm_action_segment: Using this can specify segment number to |
| -- trigger the specified dtm_action. |
| -- debug_dtm_action_target: Allows to set target for specified |
| -- dtm_action should it be DTM protocol command or SQL command from |
| -- master to segment. |
| -- debug_dtm_action_protocol: Allows to specify sub-type of DTM |
| -- protocol for which to perform specified dtm_action (like prepare, |
| -- abort_no_prepared, commit_prepared, abort_prepared, |
| -- subtransaction_begin, subtransaction_release, |
| -- subtransaction_rollback, etc... |
| -- |
| -- debug_dtm_action_sql_command_tag: If debug_dtm_action_target is sql |
| -- then this parameter can be used to set the type of sql that should |
| -- trigger the exeception. Ex: 'MPPEXEC UPDATE' |
| -- debug_dtm_action_nestinglevel: This allows to optional specify at |
| -- which specific depth level in transaction to take the specified |
| -- dtm_action. This apples only to target with protocol and not SQL. |
| -- |
| -- |
| -- start_matchsubs |
| -- s/\s+\(.*\.[ch]:\d+\)/ (SOMEFILE:SOMEFUNC)/ |
| -- m/ / |
| -- m/transaction \d+/ |
| -- s/transaction \d+/transaction / |
| -- end_matchsubs |
| CREATE OR REPLACE FUNCTION test_excep (arg INTEGER) RETURNS INTEGER |
| AS $$ |
| DECLARE res INTEGER; |
| BEGIN |
| res := 100 / arg; |
| RETURN res; |
| EXCEPTION |
| WHEN division_by_zero |
| THEN RETURN 999; |
| END; |
| $$ |
| LANGUAGE plpgsql; |
| CREATE OR REPLACE FUNCTION test_protocol_allseg(mid int, mshop int, mgender character) RETURNS VOID AS |
| $$ |
| DECLARE tfactor int default 0; |
| BEGIN |
| BEGIN |
| CREATE TABLE employees(id int, shop_id int, gender character) DISTRIBUTED BY (id); |
| |
| INSERT INTO employees VALUES (0, 1, 'm'); |
| END; |
| BEGIN |
| BEGIN |
| IF EXISTS (select 1 from employees where id = mid) THEN |
| RAISE EXCEPTION 'Duplicate employee id'; |
| ELSE |
| IF NOT (mshop between 1 AND 2) THEN |
| RAISE EXCEPTION 'Invalid shop id' ; |
| END IF; |
| END IF; |
| SELECT * INTO tfactor FROM test_excep(0); |
| BEGIN |
| INSERT INTO employees VALUES (mid, mshop, mgender); |
| EXCEPTION |
| WHEN OTHERS THEN |
| BEGIN |
| RAISE NOTICE 'catching the exception ...3'; |
| END; |
| END; |
| EXCEPTION |
| WHEN OTHERS THEN |
| RAISE NOTICE 'catching the exception ...2'; |
| END; |
| EXCEPTION |
| WHEN OTHERS THEN |
| RAISE NOTICE 'catching the exception ...1'; |
| END; |
| END; |
| $$ |
| LANGUAGE plpgsql; |
| -- |
| -- |
| SET debug_dtm_action_segment=0; |
| SET debug_dtm_action_target=protocol; |
| SET debug_dtm_action_protocol=subtransaction_rollback; |
| SET debug_dtm_action=fail_end_command; |
| SET debug_dtm_action_nestinglevel=0; |
| DROP TABLE IF EXISTS employees; |
| NOTICE: table "employees" does not exist, skipping |
| select test_protocol_allseg(1, 2,'f'); |
| ERROR: Raise error for debug_dtm_action = 3, debug_dtm_action_protocol = Rollback Current Subtransaction (seg0 127.0.1.1:25432 pid=24104) |
| CONTEXT: PL/pgSQL function "test_protocol_allseg" line 17 during exception cleanup |
| select * from employees; |
| ERROR: relation "employees" does not exist |
| LINE 1: select * from employees; |
| ^ |
| -- |
| -- |
| SET debug_dtm_action_segment=0; |
| SET debug_dtm_action_target=protocol; |
| SET debug_dtm_action_protocol=subtransaction_release; |
| SET debug_dtm_action=fail_begin_command; |
| SET debug_dtm_action_nestinglevel=0; |
| DROP TABLE IF EXISTS employees; |
| NOTICE: table "employees" does not exist, skipping |
| select test_protocol_allseg(1, 2,'f'); |
| NOTICE: catching the exception ...2 |
| ERROR: transaction 41615890 at level 1 already processed (current level 1) (cdbtm.c:2299) (seg2 192.168.235.128:7004 pid=654) (cdbtm.c:2299) |
| CONTEXT: PL/pgSQL function test_protocol_allseg(integer,integer,character) line 9 during exception cleanup |
| select * from employees; |
| ERROR: relation "employees" does not exist |
| LINE 1: select * from employees; |
| ^ |
| -- |
| -- |
| SET debug_dtm_action_segment=0; |
| SET debug_dtm_action_target=protocol; |
| SET debug_dtm_action_protocol=subtransaction_release; |
| SET debug_dtm_action=fail_begin_command; |
| SET debug_dtm_action_nestinglevel=4; |
| DROP TABLE IF EXISTS employees; |
| NOTICE: table "employees" does not exist, skipping |
| select test_protocol_allseg(1, 2,'f'); |
| NOTICE: catching the exception ...2 |
| test_protocol_allseg |
| ---------------------- |
| |
| (1 row) |
| |
| select * from employees; |
| id | shop_id | gender |
| ----+---------+-------- |
| 0 | 1 | m |
| (1 row) |
| |
| -- |
| -- |
| SET debug_dtm_action_segment=0; |
| SET debug_dtm_action_target=protocol; |
| SET debug_dtm_action_protocol=subtransaction_begin; |
| SET debug_dtm_action=fail_begin_command; |
| SET debug_dtm_action_nestinglevel=0; |
| DROP TABLE IF EXISTS employees; |
| select test_protocol_allseg(1, 2,'f'); |
| ERROR: Raise ERROR for debug_dtm_action = 2, debug_dtm_action_protocol = Begin Internal Subtransaction (seg0 127.0.1.1:7002 pid=1940526) |
| CONTEXT: PL/pgSQL function test_protocol_allseg(integer,integer,character) line 9 during statement block entry |
| select * from employees; |
| ERROR: relation "employees" does not exist |
| LINE 1: select * from employees; |
| ^ |
| -- |
| -- |
| SET debug_dtm_action_segment=0; |
| SET debug_dtm_action_target=protocol; |
| SET debug_dtm_action_protocol=subtransaction_release; |
| SET debug_dtm_action=fail_end_command; |
| SET debug_dtm_action_nestinglevel=4; |
| DROP TABLE IF EXISTS employees; |
| NOTICE: table "employees" does not exist, skipping |
| select test_protocol_allseg(1, 2,'f'); |
| NOTICE: catching the exception ...2 |
| test_protocol_allseg |
| ---------------------- |
| |
| (1 row) |
| |
| select * from employees; |
| id | shop_id | gender |
| ----+---------+-------- |
| 0 | 1 | m |
| (1 row) |
| |
| -- |
| -- |
| SET debug_dtm_action_segment=0; |
| SET debug_dtm_action_target=protocol; |
| SET debug_dtm_action_protocol=subtransaction_begin; |
| SET debug_dtm_action=fail_end_command; |
| SET debug_dtm_action_nestinglevel=0; |
| DROP TABLE IF EXISTS employees; |
| select test_protocol_allseg(1, 2,'f'); |
| ERROR: Raise error for debug_dtm_action = 3, debug_dtm_action_protocol = Begin Internal Subtransaction (seg0 127.0.1.1:7002 pid=1940526) |
| CONTEXT: PL/pgSQL function test_protocol_allseg(integer,integer,character) line 9 during statement block entry |
| select * from employees; |
| ERROR: relation "employees" does not exist |
| LINE 1: select * from employees; |
| ^ |
| -- |
| -- |
| SET debug_dtm_action_segment=0; |
| SET debug_dtm_action_target=protocol; |
| SET debug_dtm_action_protocol=subtransaction_rollback; |
| SET debug_dtm_action=fail_end_command; |
| SET debug_dtm_action_nestinglevel=3; |
| DROP TABLE IF EXISTS employees; |
| NOTICE: table "employees" does not exist, skipping |
| select test_protocol_allseg(1, 2,'f'); |
| NOTICE: catching the exception ...2 |
| test_protocol_allseg |
| ---------------------- |
| |
| (1 row) |
| |
| select * from employees; |
| id | shop_id | gender |
| ----+---------+-------- |
| 0 | 1 | m |
| (1 row) |
| |
| -- |
| -- |
| SET debug_dtm_action_segment=0; |
| SET debug_dtm_action_target=protocol; |
| SET debug_dtm_action_protocol=subtransaction_release; |
| SET debug_dtm_action=fail_end_command; |
| SET debug_dtm_action_nestinglevel=0; |
| DROP TABLE IF EXISTS employees; |
| select test_protocol_allseg(1, 2,'f'); |
| NOTICE: catching the exception ...2 |
| ERROR: transaction 41615928 at level 1 already processed (current level 1) (cdbtm.c:2299) (seg1 192.168.235.128:7003 pid=648) (cdbtm.c:2299) |
| CONTEXT: PL/pgSQL function test_protocol_allseg(integer,integer,character) line 9 during exception cleanup |
| select * from employees; |
| ERROR: relation "employees" does not exist |
| LINE 1: select * from employees; |
| ^ |
| -- |
| -- |
| SET debug_dtm_action_segment=0; |
| SET debug_dtm_action_target=protocol; |
| SET debug_dtm_action_protocol=subtransaction_begin; |
| SET debug_dtm_action=fail_begin_command; |
| SET debug_dtm_action_nestinglevel=3; |
| DROP TABLE IF EXISTS employees; |
| NOTICE: table "employees" does not exist, skipping |
| select test_protocol_allseg(1, 2,'f'); |
| NOTICE: catching the exception ...2 |
| test_protocol_allseg |
| ---------------------- |
| |
| (1 row) |
| |
| select * from employees; |
| id | shop_id | gender |
| ----+---------+-------- |
| 0 | 1 | m |
| (1 row) |
| |
| -- |
| -- |
| SET debug_dtm_action_segment=0; |
| SET debug_dtm_action_target=protocol; |
| SET debug_dtm_action_protocol=subtransaction_rollback; |
| SET debug_dtm_action=fail_begin_command; |
| SET debug_dtm_action_nestinglevel=0; |
| DROP TABLE IF EXISTS employees; |
| select test_protocol_allseg(1, 2,'f'); |
| ERROR: Raise ERROR for debug_dtm_action = 2, debug_dtm_action_protocol = Rollback Current Subtransaction (seg0 127.0.1.1:25432 pid=24104) |
| CONTEXT: PL/pgSQL function "test_protocol_allseg" line 17 during exception cleanup |
| select * from employees; |
| ERROR: relation "employees" does not exist |
| LINE 1: select * from employees; |
| ^ |
| -- |
| -- |
| SET debug_dtm_action_segment=0; |
| SET debug_dtm_action_target=protocol; |
| SET debug_dtm_action_protocol=subtransaction_rollback; |
| SET debug_dtm_action=fail_begin_command; |
| SET debug_dtm_action_nestinglevel=3; |
| DROP TABLE IF EXISTS employees; |
| NOTICE: table "employees" does not exist, skipping |
| select test_protocol_allseg(1, 2,'f'); |
| NOTICE: catching the exception ...2 |
| test_protocol_allseg |
| ---------------------- |
| |
| (1 row) |
| |
| select * from employees; |
| id | shop_id | gender |
| ----+---------+-------- |
| 0 | 1 | m |
| (1 row) |
| |
| -- |
| -- |
| SET debug_dtm_action_segment=0; |
| SET debug_dtm_action_target=protocol; |
| SET debug_dtm_action_protocol=subtransaction_begin; |
| SET debug_dtm_action=fail_end_command; |
| SET debug_dtm_action_nestinglevel=3; |
| DROP TABLE IF EXISTS employees; |
| select test_protocol_allseg(1, 2,'f'); |
| NOTICE: catching the exception ...2 |
| test_protocol_allseg |
| ---------------------- |
| |
| (1 row) |
| |
| select * from employees; |
| id | shop_id | gender |
| ----+---------+-------- |
| 0 | 1 | m |
| (1 row) |
| |
