| -- |
| -- DISTRIBUTED TRANSACTIONS |
| -- |
| --SET debug_print_full_dtm=true; |
| -- |
| -- start_matchsubs |
| -- |
| -- # create a match/subs expression |
| -- |
| -- m/(ERROR|WARNING|CONTEXT|NOTICE):.*The previous session was reset because its gang was disconnected/ |
| -- s/session id \=\s*\d+/session id \= DUMMY/gm |
| -- |
| -- end_matchsubs |
| -- |
| -- |
| -- We want to have an error between the point where all segments are prepared and our decision |
| -- to write the Distributed Commit record. |
| -- |
| -- start_ignore |
| BEGIN; |
| DROP TABLE IF EXISTS distxact1_1; |
| NOTICE: table "distxact1_1" does not exist, skipping |
| DROP TABLE IF EXISTS distxact1_2; |
| NOTICE: table "distxact1_2" does not exist, skipping |
| DROP TABLE IF EXISTS distxact1_3; |
| NOTICE: table "distxact1_3" does not exist, skipping |
| DROP TABLE IF EXISTS distxact1_4; |
| NOTICE: table "distxact1_4" does not exist, skipping |
| CREATE TABLE distxact1_1 (a int) DISTRIBUTED BY (a); |
| CREATE TABLE distxact1_2 (a int) DISTRIBUTED BY (a); |
| CREATE TABLE distxact1_3 (a int) DISTRIBUTED BY (a); |
| CREATE TABLE distxact1_4 (a int) DISTRIBUTED BY (a); |
| COMMIT; |
| -- end_ignore |
| set optimizer_print_missing_stats = off; |
| BEGIN; |
| INSERT INTO distxact1_1 VALUES (1); |
| INSERT INTO distxact1_1 VALUES (2); |
| INSERT INTO distxact1_1 VALUES (3); |
| INSERT INTO distxact1_1 VALUES (4); |
| INSERT INTO distxact1_1 VALUES (5); |
| INSERT INTO distxact1_1 VALUES (6); |
| INSERT INTO distxact1_1 VALUES (7); |
| INSERT INTO distxact1_1 VALUES (8); |
| SET debug_abort_after_distributed_prepared = true; |
| COMMIT; |
| ERROR: Raise an error as directed by Debug_abort_after_distributed_prepared |
| RESET debug_abort_after_distributed_prepared; |
| SELECT * FROM distxact1_1; |
| a |
| --- |
| (0 rows) |
| |
| -- |
| -- We want to have an error during the prepare which will cause a Abort-Some-Prepared broadcast |
| -- to cleanup. |
| -- |
| BEGIN; |
| INSERT INTO distxact1_2 VALUES (21); |
| INSERT INTO distxact1_2 VALUES (22); |
| INSERT INTO distxact1_2 VALUES (23); |
| INSERT INTO distxact1_2 VALUES (24); |
| INSERT INTO distxact1_2 VALUES (25); |
| INSERT INTO distxact1_2 VALUES (26); |
| INSERT INTO distxact1_2 VALUES (27); |
| INSERT INTO distxact1_2 VALUES (28); |
| SET debug_dtm_action_segment=1; |
| SET debug_dtm_action = "fail_begin_command"; |
| SET debug_dtm_action_target = "protocol"; |
| SET debug_dtm_action_protocol = "prepare"; |
| COMMIT; |
| ERROR: Raise ERROR for debug_dtm_action = 2, debug_dtm_action_protocol = Distributed Prepare (seg1 127.0.0.1:40001 pid=25677) |
| RESET debug_dtm_action; |
| RESET debug_dtm_action_target; |
| RESET debug_dtm_action_protocol; |
| SELECT * FROM distxact1_2; |
| a |
| --- |
| (0 rows) |
| |
| -- |
| -- We want to have an error during the commit-prepared broadcast which will cause a |
| -- Retry-Commit-Prepared broadcast to cleanup. |
| -- |
| BEGIN; |
| INSERT INTO distxact1_3 VALUES (31); |
| INSERT INTO distxact1_3 VALUES (32); |
| INSERT INTO distxact1_3 VALUES (33); |
| INSERT INTO distxact1_3 VALUES (34); |
| INSERT INTO distxact1_3 VALUES (35); |
| INSERT INTO distxact1_3 VALUES (36); |
| INSERT INTO distxact1_3 VALUES (37); |
| INSERT INTO distxact1_3 VALUES (38); |
| SET debug_dtm_action_segment=1; |
| SET debug_dtm_action = "fail_begin_command"; |
| SET debug_dtm_action_target = "protocol"; |
| SET debug_dtm_action_protocol = "commit_prepared"; |
| COMMIT; |
| WARNING: the distributed transaction 'Commit Prepared' broadcast failed to one or more segments. Retrying ... try 1 |
| DETAIL: gid=66802167, state=Retry Commit Prepared |
| NOTICE: Releasing segworker group to retry broadcast. |
| SELECT * FROM distxact1_3; |
| a |
| ---- |
| 38 |
| 32 |
| 31 |
| 33 |
| 34 |
| 35 |
| 36 |
| 37 |
| (8 rows) |
| |
| RESET debug_dtm_action_segment; |
| RESET debug_dtm_action; |
| RESET debug_dtm_action_target; |
| RESET debug_dtm_action_protocol; |
| -- |
| -- VARIANT of we want to have an error between the point where all segments are prepared and our decision |
| -- to write the Distributed Commit record. Cause problem during abort-prepared broadcast. |
| -- |
| BEGIN; |
| INSERT INTO distxact1_4 VALUES (41); |
| INSERT INTO distxact1_4 VALUES (42); |
| INSERT INTO distxact1_4 VALUES (43); |
| INSERT INTO distxact1_4 VALUES (44); |
| INSERT INTO distxact1_4 VALUES (45); |
| INSERT INTO distxact1_4 VALUES (46); |
| INSERT INTO distxact1_4 VALUES (47); |
| INSERT INTO distxact1_4 VALUES (48); |
| SET debug_dtm_action_segment=1; |
| SET debug_abort_after_distributed_prepared = true; |
| SET debug_dtm_action = "fail_begin_command"; |
| SET debug_dtm_action_target = "protocol"; |
| SET debug_dtm_action_protocol = "abort_prepared"; |
| COMMIT; |
| WARNING: the distributed transaction broadcast failed to one or more segments |
| DETAIL: gid=66802193, state=Notifying Abort Prepared |
| ERROR: Raise an error as directed by Debug_abort_after_distributed_prepared |
| SELECT * FROM distxact1_4; |
| a |
| --- |
| (0 rows) |
| |
| RESET debug_dtm_action_segment; |
| RESET debug_abort_after_distributed_prepared; |
| RESET debug_dtm_action; |
| RESET debug_dtm_action_target; |
| RESET debug_dtm_action_protocol; |
| -- |
| -- Fail general commands |
| -- |
| -- |
| -- Invoke a failure during a CREATE TABLE command. |
| -- |
| --SET debug_print_full_dtm=true; |
| SET debug_dtm_action_segment=1; |
| SET debug_dtm_action = "fail_begin_command"; |
| SET debug_dtm_action_target = "sql"; |
| SET debug_dtm_action_sql_command_tag = "MPPEXEC UTILITY"; |
| CREATE TABLE distxact2_1 (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. |
| ERROR: Raise ERROR for debug_dtm_action = 2, commandTag = MPPEXEC UTILITY (seg1 127.0.1.1:25433 pid=1127) |
| RESET debug_dtm_action_segment; |
| RESET debug_dtm_action_sql_command_tag; |
| RESET debug_dtm_action; |
| RESET debug_dtm_action_target; |
| SELECT * FROM distxact2_1; |
| ERROR: relation "distxact2_1" does not exist |
| LINE 1: SELECT * FROM distxact2_1; |
| ^ |
| -- Should succeed |
| CREATE TABLE distxact2_1 (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. |
| DROP TABLE distxact2_1; |
| -- |
| -- Invoke a failure during a CREATE TABLE command. |
| -- Action_Target = 2 is SQL. |
| -- |
| SET debug_dtm_action_segment=1; |
| SET debug_dtm_action = "fail_end_command"; |
| SET debug_dtm_action_target = "sql"; |
| SET debug_dtm_action_sql_command_tag = "MPPEXEC UTILITY"; |
| CREATE TABLE distxact2_2 (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. |
| ERROR: Raise ERROR for debug_dtm_action = 3, commandTag = MPPEXEC UTILITY (seg1 127.0.1.1:25433 pid=1127) |
| RESET debug_dtm_action_segment; |
| RESET debug_dtm_action_sql_command_tag; |
| RESET debug_dtm_action; |
| RESET debug_dtm_action_target; |
| SELECT * FROM distxact2_2; |
| ERROR: relation "distxact2_2" does not exist |
| LINE 1: SELECT * FROM distxact2_2; |
| ^ |
| -- Should succeed |
| CREATE TABLE distxact2_2 (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. |
| DROP TABLE distxact2_2; |
| -- |
| -- xact.c DTM related dispatches |
| -- |
| -- |
| -- Invoke a failure during a SAVEPOINT command. |
| -- |
| --SET debug_print_full_dtm=true; |
| SET debug_dtm_action_segment=1; |
| SET debug_dtm_action = "fail_begin_command"; |
| SET debug_dtm_action_target = "sql"; |
| SET debug_dtm_action_sql_command_tag = "SAVEPOINT"; |
| BEGIN; |
| CREATE TABLE distxact3_1 (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. |
| SAVEPOINT s; |
| ERROR: Raise ERROR for debug_dtm_action = 2, commandTag = SAVEPOINT (seg1 127.0.1.1:25433 pid=1127) |
| ROLLBACK; |
| RESET debug_dtm_action_segment; |
| RESET debug_dtm_action_sql_command_tag; |
| RESET debug_dtm_action; |
| RESET debug_dtm_action_target; |
| SELECT * FROM distxact3_1; |
| ERROR: relation "distxact3_1" does not exist |
| LINE 1: SELECT * FROM distxact3_1; |
| ^ |
| -- Should succeed |
| CREATE TABLE distxact3_1 (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. |
| DROP TABLE distxact3_1; |
| -- |
| -- Invoke a failure during a RELEASE SAVEPOINT command. |
| -- |
| --SET debug_print_full_dtm=true; |
| SET debug_dtm_action_segment=1; |
| SET debug_dtm_action = "fail_begin_command"; |
| SET debug_dtm_action_target = "sql"; |
| SET debug_dtm_action_sql_command_tag = "RELEASE"; |
| BEGIN; |
| CREATE TABLE distxact3_2 (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. |
| SAVEPOINT s; |
| INSERT INTO distxact3_2 VALUES (21); |
| INSERT INTO distxact3_2 VALUES (22); |
| INSERT INTO distxact3_2 VALUES (23); |
| INSERT INTO distxact3_2 VALUES (24); |
| INSERT INTO distxact3_2 VALUES (25); |
| INSERT INTO distxact3_2 VALUES (26); |
| INSERT INTO distxact3_2 VALUES (27); |
| INSERT INTO distxact3_2 VALUES (28); |
| RELEASE SAVEPOINT s; |
| ERROR: Raise ERROR for debug_dtm_action = 2, commandTag = RELEASE (seg1 127.0.1.1:25433 pid=1127) |
| ROLLBACK; |
| RESET debug_dtm_action_segment; |
| RESET debug_dtm_action_sql_command_tag; |
| RESET debug_dtm_action; |
| RESET debug_dtm_action_target; |
| SELECT * FROM distxact3_2; |
| ERROR: relation "distxact3_2" does not exist |
| LINE 1: SELECT * FROM distxact3_2; |
| ^ |
| -- Should succeed |
| CREATE TABLE distxact3_2 (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. |
| DROP TABLE distxact3_2; |
| -- |
| -- Invoke a failure during a ROLLBACK TO SAVEPOINT command. |
| -- |
| --SET debug_print_full_dtm=true; |
| SET debug_dtm_action_segment=1; |
| SET debug_dtm_action = "fail_begin_command"; |
| SET debug_dtm_action_target = "sql"; |
| SET debug_dtm_action_sql_command_tag = "ROLLBACK"; |
| BEGIN; |
| CREATE TABLE distxact3_3 (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. |
| SAVEPOINT s; |
| INSERT INTO distxact3_3 VALUES (31); |
| INSERT INTO distxact3_3 VALUES (32); |
| INSERT INTO distxact3_3 VALUES (33); |
| INSERT INTO distxact3_3 VALUES (34); |
| INSERT INTO distxact3_3 VALUES (35); |
| INSERT INTO distxact3_3 VALUES (36); |
| INSERT INTO distxact3_3 VALUES (37); |
| INSERT INTO distxact3_3 VALUES (38); |
| ROLLBACK TO SAVEPOINT s; |
| ERROR: Raise ERROR for debug_dtm_action = 2, commandTag = ROLLBACK (seg1 127.0.1.1:25433 pid=1127) |
| ROLLBACK; |
| RESET debug_dtm_action_segment; |
| RESET debug_dtm_action_sql_command_tag; |
| RESET debug_dtm_action; |
| RESET debug_dtm_action_target; |
| SELECT * FROM distxact3_3; |
| ERROR: relation "distxact3_3" does not exist |
| LINE 1: SELECT * FROM distxact3_3; |
| ^ |
| -- Should succeed |
| CREATE TABLE distxact3_3 (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. |
| DROP TABLE distxact3_3; |
| RESET debug_print_full_dtm; |
| -- Test cursor/serializable interaction. |
| -- MPP-3227: pg_dump does this exact sequence. |
| -- for each table in a database. |
| drop table if exists dtmcurse_foo; |
| NOTICE: table "dtmcurse_foo" does not exist, skipping |
| drop table if exists dtmcurse_bar; |
| NOTICE: table "dtmcurse_bar" does not exist, skipping |
| create table dtmcurse_foo (a int, b 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. |
| insert into dtmcurse_foo values (1,2); |
| insert into dtmcurse_foo values (2,2); |
| create table dtmcurse_bar as select * from dtmcurse_foo distributed by (b); |
| begin; |
| SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
| DECLARE cursor1 CURSOR FOR SELECT * FROM ONLY dtmcurse_foo order by a; |
| fetch 1 from cursor1; |
| a | b |
| ---+--- |
| 1 | 2 |
| (1 row) |
| |
| close cursor1; |
| -- MPP-3227: second declare would hang waiting for snapshot, |
| -- should work just like the first. |
| DECLARE cursor1 CURSOR FOR SELECT * FROM ONLY dtmcurse_bar order by a; |
| fetch 1 from cursor1; |
| a | b |
| ---+--- |
| 1 | 2 |
| (1 row) |
| |
| close cursor1; |
| abort; |
| -- MPP-4504: cursor + InitPlan |
| begin; |
| declare c1 cursor for select * from dtmcurse_foo where a = (select min(a) from dtmcurse_foo); |
| fetch 1 from c1; |
| a | b |
| ---+--- |
| 1 | 2 |
| (1 row) |
| |
| close c1; |
| end; |
| drop table if exists dtmcurse_foo; |
| drop table if exists dtmcurse_bar; |
| -- Test distribute transaction if 'COMMIT/END' is included in a multi-queries command. |
| \! psql postgres -Xc "begin;end; create table dtx_test1(c1 int); drop table dtx_test1;" |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' 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. |
| DROP TABLE |
| -- Test two phase commit for extended query |
| \! ./twophase_pqexecparams dbname=regression |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' 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. |
| WARNING: the distributed transaction 'Commit Prepared' broadcast failed to one or more segments. Retrying ... try 1 |
| DETAIL: gid=66802449, state=Retry Commit Prepared |
| NOTICE: Releasing segworker group to retry broadcast. |
| result: INSERT 0 2 |
| -- |
| -- Subtransactions with partition table DDLs. |
| -- |
| BEGIN; |
| Create table subt_alter_part_tab_ao1 ( |
| i int, x text, c char, v varchar, d date, n numeric, |
| t timestamp without time zone, tz time with time zone) |
| with (appendonly=true, orientation=column) distributed by (i) |
| partition by range (i) |
| (partition p1 start(1) end(5), |
| partition p2 start(5) end(8), |
| partition p3 start(8) end(10)); |
| Savepoint sp1; |
| Alter table subt_alter_part_tab_ao1 add partition p4 start(10) end(14); |
| Alter table subt_alter_part_tab_ao1 add default partition def_part; |
| Insert into subt_alter_part_tab_ao1 values( |
| generate_series(1,15), 'create table with subtransactions', 's', |
| 'subtransaction table', '12-11-2012', 3, '2012-10-09 10:23:54', |
| '2011-08-19 10:23:54+02'); |
| Savepoint sp2; -- child of sp1 |
| Select count(*) from subt_alter_part_tab_ao1; |
| count |
| ------- |
| 15 |
| (1 row) |
| |
| Alter table subt_alter_part_tab_ao1 drop partition p3; |
| Select count(*) from subt_alter_part_tab_ao1; |
| count |
| ------- |
| 13 |
| (1 row) |
| |
| select count(*) = 0 as passed from subt_alter_part_tab_ao1 |
| where i >= 8 and i < 10; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| Savepoint sp3; -- child of sp2 |
| Alter table subt_alter_part_tab_ao1 drop default partition; |
| release savepoint sp3; -- commit sp3 |
| Select count(*) from subt_alter_part_tab_ao1; |
| count |
| ------- |
| 11 |
| (1 row) |
| |
| select count(*) = 0 as passed from subt_alter_part_tab_ao1 where i > 13; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| savepoint sp4; -- child of sp2 |
| Create table exg_pt_ao1(i int, x text,c char,v varchar, d date, n numeric, |
| t timestamp without time zone, tz time with time zone) distributed by (i); |
| Insert into exg_pt_ao1 values ( |
| 7, 'to be exchanged', 's', 'partition table', '12-11-2012', 3, |
| '2012-10-09 10:23:54', '2011-08-19 10:23:54+02'); |
| Alter table subt_alter_part_tab_ao1 exchange partition p2 |
| with table exg_pt_ao1; |
| select count(*) = 1 as passed from subt_alter_part_tab_ao1 |
| where i >= 5 and i < 8; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| rollback to sp4; |
| select count(*) > 1 as passed from subt_alter_part_tab_ao1 |
| where i >= 5 and i < 8; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| Alter table subt_alter_part_tab_ao1 split partition p4 at (13) into |
| (partition splita,partition splitb); |
| select count(*) from subt_alter_part_tab_ao1; |
| count |
| ------- |
| 11 |
| (1 row) |
| |
| savepoint sp5; -- child of sp4 |
| Alter table subt_alter_part_tab_ao1 drop partition splita; |
| Select count(*) from subt_alter_part_tab_ao1; |
| count |
| ------- |
| 8 |
| (1 row) |
| |
| rollback to sp5; |
| Select count(*) from subt_alter_part_tab_ao1; |
| count |
| ------- |
| 11 |
| (1 row) |
| |
| Alter table subt_alter_part_tab_ao1 drop partition splita; |
| rollback to sp2; -- should abort committed child sp3 |
| COMMIT; |
| Select count(*) = 15 as passed from subt_alter_part_tab_ao1; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) > 0 as passed from subt_alter_part_tab_ao1 where i > 13; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| -- DML should work after the subtransaction business. |
| Insert into subt_alter_part_tab_ao1 values( |
| generate_series(1,15), 'create table with subtransactions', 's', |
| 'subtransaction table', '12-11-2012', 3, '2012-10-09 10:23:54', |
| '2011-08-19 10:23:54+02'); |
| Select count(*) = 30 as passed from subt_alter_part_tab_ao1; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| -- |
| -- Subtransactions with DDL/DMLs on append-optimized tables. |
| -- |
| BEGIN; |
| Create table subt_alter_table_ao1 (col0 int) |
| with (appendonly=true) distributed by (col0); |
| Create table subt_alter_table_co1 (col0 int) |
| with (appendonly=true, orientation=column) distributed by (col0); |
| Insert into subt_alter_table_ao1 values(generate_series(1,5)); |
| Insert into subt_alter_table_co1 values(generate_series(1,5)); |
| Savepoint sp1; |
| -- Add column, update, commit subtransaction. |
| Alter table subt_alter_table_ao1 add column col1 int default 10; |
| update subt_alter_table_ao1 set col1 = col0; |
| Alter table subt_alter_table_co1 add column col1 int default 10; |
| update subt_alter_table_co1 set col1 = col0; |
| release savepoint sp1; |
| -- Alter column type, update, rollback subtransaction. |
| Savepoint sp2; |
| Alter table subt_alter_table_ao1 drop column col1; |
| insert into subt_alter_table_ao1 values (6), (7); |
| select count(attnum) = 1 as passed from pg_attribute |
| where attrelid = 'subt_alter_table_ao1'::regclass |
| and attisdropped = true; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| Alter table subt_alter_table_co1 alter column col1 type float; |
| update subt_alter_table_co1 set col1 = col0/10::float; |
| select count(*) = 5 as passed from subt_alter_table_co1 where col1 < 1; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| rollback to sp2; |
| select count(attnum) = 0 as passed from pg_attribute |
| where attrelid = 'subt_alter_table_ao1'::regclass |
| and attisdropped = true; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 0 as passed from subt_alter_table_co1 where col1 < 1; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| COMMIT; |
| select count(attnum) = 0 as passed from pg_attribute |
| where attrelid = 'subt_alter_table_ao1'::regclass |
| and attisdropped = true; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 0 as passed from subt_alter_table_co1 where col1 < 1; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| -- |
| -- Subtransactions with reindex and truncate. |
| -- |
| BEGIN; |
| -- Enforce index usage for this test. |
| set enable_seqscan=false; |
| set enable_indexscan=true; |
| set enable_bitmapscan=true; |
| Create table subt_reindex_heap (i int, x text, n numeric, b box) |
| distributed by (i); |
| Create index bt_ri_heap on subt_reindex_heap (x); |
| Create index bm_ri_heap on subt_reindex_heap using bitmap (n); |
| Create index gist_ri_heap on subt_reindex_heap using gist (b); |
| Create Unique index unique_ri_heap on subt_reindex_heap (i); |
| Create table subt_reindex_ao (i int, x text, n numeric, b box) |
| with(appendonly=true) distributed by (i); |
| Create index bt_ri_ao on subt_reindex_ao (x); |
| Create index bm_ri_ao on subt_reindex_ao using bitmap (n); |
| Create index gist_ri_ao on subt_reindex_ao using gist (b); |
| Create table subt_reindex_co (i int, x text, n numeric, b box) |
| with(appendonly=true, orientation=column) distributed by (i); |
| Create index bt_ri_co on subt_reindex_co (x); |
| Create index bm_ri_co on subt_reindex_co using bitmap (n); |
| Create index gist_ri_co on subt_reindex_co using gist (b); |
| savepoint sp1; |
| Insert into subt_reindex_heap select i, 'heap '||i, 2, |
| ('(0,'||i||', 1,'||i+1||')')::box from generate_series(1,5)i; |
| Insert into subt_reindex_ao select i, 'AO '||i, 2, |
| ('(0,'||i||', 1,'||i+1||')')::box from generate_series(1,5)i; |
| Insert into subt_reindex_co select i, 'CO '||i, 2, |
| ('(0,'||i||', 1,'||i+1||')')::box from generate_series(1,5)i; |
| savepoint sp2; -- child of sp1 |
| Insert into subt_reindex_heap values |
| (6, 'heap 6', 3, '((0,0), (1,1))'); |
| Insert into subt_reindex_ao values |
| (5, 'AO 5', 3, '((0,0), (1,1))'); |
| Insert into subt_reindex_co values |
| (5, 'CO 5', 3, '((0,0), (1,1))'); |
| update subt_reindex_heap set n = -i where n = 3; |
| update subt_reindex_ao set n = -i where n = 3; |
| update subt_reindex_co set n = -i where n = 3; |
| savepoint sp3; -- child of sp2; |
| REINDEX index bm_ri_heap; |
| REINDEX index bm_ri_ao; |
| REINDEX index bm_ri_co; |
| select count(*) = 1 as passed from subt_reindex_heap where n < 0; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 1 as passed from subt_reindex_ao where n < 0; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 1 as passed from subt_reindex_co where n < 0; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| release savepoint sp3; -- commit sp3 |
| savepoint sp4; -- child of sp2 |
| REINDEX index unique_ri_heap; |
| REINDEX index bt_ri_ao; |
| REINDEX index bm_ri_ao; |
| REINDEX index gist_ri_ao; |
| REINDEX index bt_ri_co; |
| REINDEX index bm_ri_co; |
| REINDEX index gist_ri_co; |
| savepoint sp5; -- child of sp4 |
| select count(*) = 1 as passed from subt_reindex_heap where x = 'heap 2'; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 1 as passed from subt_reindex_ao where x = 'AO 3'; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 1 as passed from subt_reindex_co where x = 'CO 4'; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select 0/0; |
| ERROR: division by zero |
| rollback to sp4; |
| select count(*) = 1 as passed from subt_reindex_heap where i = 1; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 2 as passed from subt_reindex_ao where i = 5; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 2 as passed from subt_reindex_co where i = 5; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| update subt_reindex_heap set x = 'heap sp4', b = '((1,1),(4,4))' |
| where i = 2; |
| update subt_reindex_ao set x = 'AO sp4', b = '((1,1),(4,4))' |
| where i = 2; |
| update subt_reindex_co set x = 'CO sp4', b = '((1,1),(4,4))' |
| where i = 2; |
| savepoint sp6; -- child of sp4 |
| REINDEX index bt_ri_heap; |
| REINDEX index bm_ri_heap; |
| REINDEX index gist_ri_heap; |
| REINDEX index unique_ri_heap; |
| REINDEX index bt_ri_ao; |
| REINDEX index bt_ri_ao; |
| REINDEX index gist_ri_ao; |
| REINDEX index bt_ri_co; |
| REINDEX index bt_ri_co; |
| REINDEX index gist_ri_co; |
| release savepoint sp6; |
| select count(*) = 1 as passed from subt_reindex_heap |
| where b = '((1,1), (4,4))'; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 1 as passed from subt_reindex_ao |
| where b = '((1,1), (4,4))'; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 1 as passed from subt_reindex_co |
| where b = '((1,1), (4,4))'; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| rollback to sp2; |
| select count(*) = 5 as passed from subt_reindex_heap |
| where n = 2; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 5 as passed from subt_reindex_ao |
| where n = 2; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 5 as passed from subt_reindex_co |
| where n = 2; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 0 as passed from subt_reindex_ao |
| where x = 'AO sp4'; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| -- truncate cases |
| savepoint sp7; -- child of sp2 |
| truncate subt_reindex_heap; |
| truncate subt_reindex_ao; |
| savepoint sp8; -- child of sp7 |
| truncate subt_reindex_co; |
| select count(*) = 0 as passed from subt_reindex_heap where i < 7; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 0 as passed from subt_reindex_ao where i < 6; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 0 as passed from subt_reindex_co where i < 6; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| rollback to sp8; |
| update subt_reindex_co set x = 'CO sp8', b = '((1,1),(8,8))' |
| where i = 2; |
| release savepoint sp7; -- commit sp7 |
| -- Test rollback of truncate in a committed subtransaction. |
| rollback to sp2; |
| COMMIT; |
| select count(*) = 5 as passed from subt_reindex_heap; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 5 as passed from subt_reindex_ao; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| select count(*) = 5 as passed from subt_reindex_co; |
| passed |
| -------- |
| t |
| (1 row) |
| |
| -- GPDB has a limitation on REINDEX of catalog tables: you cannot do it in |
| -- a transaction block. Check for that. |
| \c postgres |
| begin; |
| reindex table pg_class; |
| ERROR: REINDEX of a catalog table cannot run inside a transaction block |
| commit; |
| \c regression |
| -- |
| -- Check that committing a subtransaction releases the lock on the |
| -- subtransaction's XID. |
| -- |
| -- It's not too bad if it doesn't, because if anyone wants to wait for the |
| -- subtransaction and sees that it's been committed already, they will wait |
| -- for the top transaction XID instead. So even though the lock on the sub-XID |
| -- is released at RELEASE SAVEPOINT, logically it's held until the end of |
| -- the top transaction anyway. But releasing the lock early saves space in |
| -- the lock table. (We had a silly bug once upon a time in GPDB where we failed |
| -- to release the lock.) |
| -- |
| BEGIN; |
| CREATE TEMPORARY TABLE foo (i integer); |
| DO $$ |
| declare |
| i int; |
| begin |
| for i in 1..100 loop |
| begin |
| insert into foo values (i); |
| exception |
| when others then raise 'got error'; |
| end; |
| end loop; |
| end; |
| $$; |
| SELECT CASE WHEN count(*) < 50 THEN 'not many XID locks' |
| ELSE 'lots of XID locks: ' || count(*) END |
| FROM pg_locks WHERE locktype='transactionid'; |
| case |
| -------------------- |
| not many XID locks |
| (1 row) |
| |
| ROLLBACK; |
| -- Test that exported snapshots are cleared upon abort. In Cloudberry, |
| -- exported snapshots are cleared earlier than PostgreSQL during |
| -- abort. |
| begin; |
| select count(1) = 1 from pg_catalog.pg_export_snapshot(); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select pg_cancel_backend(pg_backend_pid()); |
| ERROR: canceling statement due to user request |
| rollback; |
| -- Test a bug that a two-phase subtransaction is considered as one-phase. |
| set optimizer = off; -- orca optimizes value scan so the output is different between orca and postgres optimizer. |
| truncate distxact1_4; |
| set test_print_direct_dispatch_info = true; |
| begin; |
| savepoint sp1; |
| insert into distxact1_4 values (2),(1); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to SINGLE content |
| release sp1; |
| end; |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| reset test_print_direct_dispatch_info; |
| reset optimizer; |
| select count(gp_segment_id) from distxact1_4 group by gp_segment_id; -- sanity check: tuples should be in > 1 segments |
| count |
| ------- |
| 1 |
| 1 |
| (2 rows) |
| |
| -- Tests for AND CHAIN |
| CREATE TABLE abc (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. |
| -- set nondefault value so we have something to override below |
| SET default_transaction_read_only = on; |
| START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE; |
| SHOW transaction_isolation; |
| transaction_isolation |
| ----------------------- |
| repeatable read |
| (1 row) |
| |
| SHOW transaction_read_only; |
| transaction_read_only |
| ----------------------- |
| off |
| (1 row) |
| |
| SHOW transaction_deferrable; |
| transaction_deferrable |
| ------------------------ |
| on |
| (1 row) |
| |
| INSERT INTO abc VALUES (1); |
| INSERT INTO abc VALUES (2); |
| COMMIT AND CHAIN; -- TBLOCK_END |
| SHOW transaction_isolation; |
| transaction_isolation |
| ----------------------- |
| repeatable read |
| (1 row) |
| |
| SHOW transaction_read_only; |
| transaction_read_only |
| ----------------------- |
| off |
| (1 row) |
| |
| SHOW transaction_deferrable; |
| transaction_deferrable |
| ------------------------ |
| on |
| (1 row) |
| |
| INSERT INTO abc VALUES ('error'); |
| ERROR: invalid input syntax for type integer: "error" |
| LINE 1: INSERT INTO abc VALUES ('error'); |
| ^ |
| INSERT INTO abc VALUES (3); -- check it's really aborted |
| ERROR: current transaction is aborted, commands ignored until end of transaction block |
| COMMIT AND CHAIN; -- TBLOCK_ABORT_END |
| SHOW transaction_isolation; |
| transaction_isolation |
| ----------------------- |
| repeatable read |
| (1 row) |
| |
| SHOW transaction_read_only; |
| transaction_read_only |
| ----------------------- |
| off |
| (1 row) |
| |
| SHOW transaction_deferrable; |
| transaction_deferrable |
| ------------------------ |
| on |
| (1 row) |
| |
| INSERT INTO abc VALUES (4); |
| COMMIT; |
| START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE; |
| SHOW transaction_isolation; |
| transaction_isolation |
| ----------------------- |
| repeatable read |
| (1 row) |
| |
| SHOW transaction_read_only; |
| transaction_read_only |
| ----------------------- |
| off |
| (1 row) |
| |
| SHOW transaction_deferrable; |
| transaction_deferrable |
| ------------------------ |
| on |
| (1 row) |
| |
| SAVEPOINT x; |
| INSERT INTO abc VALUES ('error'); |
| ERROR: invalid input syntax for type integer: "error" |
| LINE 1: INSERT INTO abc VALUES ('error'); |
| ^ |
| COMMIT AND CHAIN; -- TBLOCK_ABORT_PENDING |
| SHOW transaction_isolation; |
| transaction_isolation |
| ----------------------- |
| repeatable read |
| (1 row) |
| |
| SHOW transaction_read_only; |
| transaction_read_only |
| ----------------------- |
| off |
| (1 row) |
| |
| SHOW transaction_deferrable; |
| transaction_deferrable |
| ------------------------ |
| on |
| (1 row) |
| |
| INSERT INTO abc VALUES (5); |
| COMMIT; |
| START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE; |
| SHOW transaction_isolation; |
| transaction_isolation |
| ----------------------- |
| repeatable read |
| (1 row) |
| |
| SHOW transaction_read_only; |
| transaction_read_only |
| ----------------------- |
| off |
| (1 row) |
| |
| SHOW transaction_deferrable; |
| transaction_deferrable |
| ------------------------ |
| on |
| (1 row) |
| |
| SAVEPOINT x; |
| COMMIT AND CHAIN; -- TBLOCK_SUBCOMMIT |
| SHOW transaction_isolation; |
| transaction_isolation |
| ----------------------- |
| repeatable read |
| (1 row) |
| |
| SHOW transaction_read_only; |
| transaction_read_only |
| ----------------------- |
| off |
| (1 row) |
| |
| SHOW transaction_deferrable; |
| transaction_deferrable |
| ------------------------ |
| on |
| (1 row) |
| |
| COMMIT; |
| -- not allowed outside a transaction block |
| COMMIT AND CHAIN; -- error |
| ERROR: COMMIT AND CHAIN can only be used in transaction blocks |
| ROLLBACK AND CHAIN; -- error |
| ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks |
| SELECT * FROM abc ORDER BY 1; |
| a |
| --- |
| 1 |
| 2 |
| 4 |
| 5 |
| (4 rows) |
| |
| RESET default_transaction_read_only; |
| |
| DROP TABLE abc; |
| -- Explicit transaction block will send Distributed Commit, even if there is only SET command in it. |
| -- On the other hand, implicit transaction block involving only SET command will not send it. |
| create table tbl_dtx(a int, b int) distributed by (a); |
| insert into tbl_dtx values(1,1); |
| create or replace function dtx_set_bug() |
| returns void |
| language plpgsql |
| as $function$ |
| begin |
| execute 'update tbl_dtx set b = 1 where a = 1;'; |
| set optimizer=off; |
| end; |
| $function$; |
| set Test_print_direct_dispatch_info = true; |
| -- 1. explicit BEGIN/END |
| begin; |
| set optimizer=false; |
| end; |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| -- 2. implicit transaction block with just SET |
| set optimizer=false; |
| -- 3. still implicit transaction block, but with UPDATE that will send DTX protocol command to *some* QEs |
| -- due to direct dispatch. Planner needs to be used for direct dispatch here. |
| -- This is to verify that the QEs that are not involved in the UDPATE won't receive DTX protocol command |
| -- that they are not supposed to see. |
| select dtx_set_bug(); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| dtx_set_bug |
| ------------- |
| |
| (1 row) |
| |
| reset Test_print_direct_dispatch_info; |
