| -- table to just store the master's data directory path on segment. |
| CREATE TABLE lockmodes_datadir(a int, dir text); |
| CREATE |
| INSERT INTO lockmodes_datadir select 1,datadir from gp_segment_configuration where role='p' and content=-1; |
| INSERT 1 |
| |
| 1: set optimizer = off; |
| SET |
| |
| create or replace view show_locks_lockmodes as select locktype, mode, granted, relation::regclass from pg_locks where gp_segment_id = -1 and locktype = 'relation' and relation::regclass::text like 't_lockmods%'; |
| CREATE |
| |
| show gp_enable_global_deadlock_detector; |
| gp_enable_global_deadlock_detector |
| ------------------------------------ |
| off |
| (1 row) |
| |
| |
| -- 1. The firs part of test is with |
| -- gp_enable_global_deadlock_detector off |
| |
| -- 1.1 test for heap tables |
| create table t_lockmods (c int) distributed randomly; |
| CREATE |
| insert into t_lockmods select * from generate_series(1, 5); |
| INSERT 5 |
| analyze t_lockmods; |
| ANALYZE |
| |
| create table t_lockmods1 (c int) distributed randomly; |
| CREATE |
| |
| create table t_lockmods_rep(c int) distributed replicated; |
| CREATE |
| |
| -- See github issue: https://github.com/greenplum-db/gpdb/issues/9449 |
| -- upsert may lock tuples on segment, so we should upgrade lock level |
| -- on QD if GDD is disabled. |
| create table t_lockmods_upsert(a int, b int) distributed by (a); |
| CREATE |
| create unique index uidx_t_lockmodes_upsert on t_lockmods_upsert(a, b); |
| CREATE |
| -- add analyze to avoid auto vacuum when executing first insert |
| analyze t_lockmods_upsert; |
| ANALYZE |
| |
| -- 1.1.1 select for (update|share|key share|no key update) should hold ExclusiveLock on range tables |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods for update; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..3.05 rows=5 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..3.05 rows=2 width=10) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| 1: select * from t_lockmods for update; |
| c |
| --- |
| 1 |
| 2 |
| 5 |
| 3 |
| 4 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------ |
| relation | AccessShareLock | t | t_lockmods |
| relation | ExclusiveLock | t | t_lockmods |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods for no key update; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..3.05 rows=5 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..3.05 rows=2 width=10) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| 1: select * from t_lockmods for no key update; |
| c |
| --- |
| 1 |
| 2 |
| 5 |
| 3 |
| 4 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------ |
| relation | AccessShareLock | t | t_lockmods |
| relation | ExclusiveLock | t | t_lockmods |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods for share; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..3.05 rows=5 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..3.05 rows=2 width=10) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| 1: select * from t_lockmods for share; |
| c |
| --- |
| 1 |
| 2 |
| 5 |
| 3 |
| 4 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------ |
| relation | AccessShareLock | t | t_lockmods |
| relation | ExclusiveLock | t | t_lockmods |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods for key share; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..3.05 rows=5 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..3.05 rows=2 width=10) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| 1: select * from t_lockmods for key share; |
| c |
| --- |
| 1 |
| 2 |
| 5 |
| 3 |
| 4 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------ |
| relation | AccessShareLock | t | t_lockmods |
| relation | ExclusiveLock | t | t_lockmods |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods, t_lockmods1 for update; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000008862.58 rows=481500 width=20) |
| -> Nested Loop (cost=10000000000.00..10000002442.58 rows=160500 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.08 rows=5 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..1.02 rows=2 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods, t_lockmods1 for update; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------- |
| relation | AccessShareLock | t | t_lockmods1 |
| relation | ExclusiveLock | t | t_lockmods1 |
| relation | AccessShareLock | t | t_lockmods |
| relation | ExclusiveLock | t | t_lockmods |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods, t_lockmods1 for no key update; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000008862.58 rows=481500 width=20) |
| -> Nested Loop (cost=10000000000.00..10000002442.58 rows=160500 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.08 rows=5 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..1.02 rows=2 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods, t_lockmods1 for no key update; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------- |
| relation | AccessShareLock | t | t_lockmods1 |
| relation | ExclusiveLock | t | t_lockmods1 |
| relation | AccessShareLock | t | t_lockmods |
| relation | ExclusiveLock | t | t_lockmods |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods, t_lockmods1 for share; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000008862.58 rows=481500 width=20) |
| -> Nested Loop (cost=10000000000.00..10000002442.58 rows=160500 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.08 rows=5 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..1.02 rows=2 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods, t_lockmods1 for share; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------- |
| relation | AccessShareLock | t | t_lockmods1 |
| relation | ExclusiveLock | t | t_lockmods1 |
| relation | AccessShareLock | t | t_lockmods |
| relation | ExclusiveLock | t | t_lockmods |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods, t_lockmods1 for key share; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000008862.58 rows=481500 width=20) |
| -> Nested Loop (cost=10000000000.00..10000002442.58 rows=160500 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.08 rows=5 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..1.02 rows=2 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods, t_lockmods1 for key share; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------- |
| relation | AccessShareLock | t | t_lockmods1 |
| relation | ExclusiveLock | t | t_lockmods1 |
| relation | AccessShareLock | t | t_lockmods |
| relation | ExclusiveLock | t | t_lockmods |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| -- 1.1.2 update | delete should hold ExclusiveLock on result relations |
| 1: begin; |
| BEGIN |
| 1: update t_lockmods set c = c + 0; |
| UPDATE 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+------------ |
| relation | ExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: delete from t_lockmods; |
| DELETE 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+------------ |
| relation | ExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| -- 1.1.3 insert should hold RowExclusiveLock on result relations |
| 1: begin; |
| BEGIN |
| 1: insert into t_lockmods select * from generate_series(1, 5); |
| INSERT 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+------------ |
| relation | RowExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| -- 1.1.4 upsert should hold ExclusiveLock on result relations |
| 1: begin; |
| BEGIN |
| 1: insert into t_lockmods_upsert values (1, 1) on conflict(a, b) do update set b = 99; |
| INSERT 1 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+------------------- |
| relation | ExclusiveLock | t | t_lockmods_upsert |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| -- 1.1.5 use cached plan should be consistent with no cached plan |
| 1: prepare select_for_update as select * from t_lockmods for update; |
| PREPARE |
| 1: prepare select_for_nokeyupdate as select * from t_lockmods for no key update; |
| PREPARE |
| 1: prepare select_for_share as select * from t_lockmods for share; |
| PREPARE |
| 1: prepare select_for_keyshare as select * from t_lockmods for key share; |
| PREPARE |
| 1: prepare update_tlockmods as update t_lockmods set c = c + 0; |
| PREPARE |
| 1: prepare delete_tlockmods as delete from t_lockmods; |
| PREPARE |
| 1: prepare insert_tlockmods as insert into t_lockmods select * from generate_series(1, 5); |
| PREPARE |
| 1: prepare upsert_tlockmods as insert into t_lockmods_upsert values (1, 1) on conflict(a, b) do update set b = 99; |
| PREPARE |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_update; |
| c |
| --- |
| 1 |
| 2 |
| 5 |
| 4 |
| 3 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+------------ |
| relation | ExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_nokeyupdate; |
| c |
| --- |
| 4 |
| 1 |
| 2 |
| 5 |
| 3 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+------------ |
| relation | ExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_share; |
| c |
| --- |
| 4 |
| 1 |
| 2 |
| 5 |
| 3 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+------------ |
| relation | ExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_keyshare; |
| c |
| --- |
| 4 |
| 1 |
| 2 |
| 5 |
| 3 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+------------ |
| relation | ExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute update_tlockmods; |
| EXECUTE 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+------------ |
| relation | ExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute delete_tlockmods; |
| EXECUTE 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+------------ |
| relation | ExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute insert_tlockmods; |
| EXECUTE 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+------------ |
| relation | RowExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute upsert_tlockmods; |
| EXECUTE 1 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+------------------- |
| relation | ExclusiveLock | t | t_lockmods_upsert |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| -- 1.2 test for AO table |
| create table t_lockmods_ao (c int) with (appendonly=true) distributed randomly; |
| CREATE |
| insert into t_lockmods_ao select * from generate_series(1, 8); |
| INSERT 8 |
| analyze t_lockmods_ao; |
| ANALYZE |
| create table t_lockmods_ao1 (c int) with (appendonly=true) distributed randomly; |
| CREATE |
| |
| -- 1.2.1 select for (update|share|key share|no key update) should hold ExclusiveLock on range tables |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao for update; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1.08 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.08 rows=3 width=10) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| 1: select * from t_lockmods_ao for update; |
| c |
| --- |
| 4 |
| 7 |
| 2 |
| 6 |
| 8 |
| 1 |
| 3 |
| 5 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+--------------- |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao for no key update; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1.08 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.08 rows=3 width=10) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| 1: select * from t_lockmods_ao for no key update; |
| c |
| --- |
| 4 |
| 7 |
| 2 |
| 6 |
| 8 |
| 1 |
| 3 |
| 5 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+--------------- |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao for share; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1.08 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.08 rows=3 width=10) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| 1: select * from t_lockmods_ao for share; |
| c |
| --- |
| 2 |
| 6 |
| 8 |
| 1 |
| 3 |
| 5 |
| 4 |
| 7 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+--------------- |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao for key share; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1.08 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.08 rows=3 width=10) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| 1: select * from t_lockmods_ao for key share; |
| c |
| --- |
| 1 |
| 3 |
| 5 |
| 4 |
| 7 |
| 2 |
| 6 |
| 8 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+--------------- |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao, t_lockmods_ao1 for update; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000013918.38 rows=770400 width=20) |
| -> Nested Loop (cost=10000000000.00..10000003646.38 rows=256800 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.13 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.03 rows=3 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods_ao1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods_ao, t_lockmods_ao1 for update; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+---------------- |
| relation | AccessShareLock | t | t_lockmods_ao1 |
| relation | ExclusiveLock | t | t_lockmods_ao1 |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao, t_lockmods_ao1 for no key update; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000013918.38 rows=770400 width=20) |
| -> Nested Loop (cost=10000000000.00..10000003646.38 rows=256800 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.13 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.03 rows=3 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods_ao1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods_ao, t_lockmods_ao1 for no key update; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+---------------- |
| relation | AccessShareLock | t | t_lockmods_ao1 |
| relation | ExclusiveLock | t | t_lockmods_ao1 |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao, t_lockmods_ao1 for share; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000013918.38 rows=770400 width=20) |
| -> Nested Loop (cost=10000000000.00..10000003646.38 rows=256800 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.13 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.03 rows=3 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods_ao1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods_ao, t_lockmods_ao1 for share; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+---------------- |
| relation | AccessShareLock | t | t_lockmods_ao1 |
| relation | ExclusiveLock | t | t_lockmods_ao1 |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao, t_lockmods_ao1 for key share; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000013918.38 rows=770400 width=20) |
| -> Nested Loop (cost=10000000000.00..10000003646.38 rows=256800 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.13 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.03 rows=3 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods_ao1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods_ao, t_lockmods_ao1 for key share; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+---------------- |
| relation | AccessShareLock | t | t_lockmods_ao1 |
| relation | ExclusiveLock | t | t_lockmods_ao1 |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| -- 1.2.2 update | delete should hold ExclusiveLock on result relations |
| 1: begin; |
| BEGIN |
| 1: update t_lockmods_ao set c = c + 0; |
| UPDATE 8 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: delete from t_lockmods_ao; |
| DELETE 8 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| -- 1.2.3 insert should hold RowExclusiveLock on result relations |
| 1: begin; |
| BEGIN |
| 1: insert into t_lockmods_ao select * from generate_series(1, 5); |
| INSERT 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+--------------- |
| relation | RowExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| -- 1.2.4 use cached plan should be consistent with no cached plan |
| 1: prepare select_for_update_ao as select * from t_lockmods_ao for update; |
| PREPARE |
| 1: prepare select_for_nokeyupdate_ao as select * from t_lockmods_ao for no key update; |
| PREPARE |
| 1: prepare select_for_share_ao as select * from t_lockmods_ao for share; |
| PREPARE |
| 1: prepare select_for_keyshare_ao as select * from t_lockmods_ao for key share; |
| PREPARE |
| 1: prepare update_tlockmods_ao as update t_lockmods_ao set c = c + 0; |
| PREPARE |
| 1: prepare delete_tlockmods_ao as delete from t_lockmods_ao; |
| PREPARE |
| 1: prepare insert_tlockmods_ao as insert into t_lockmods_ao select * from generate_series(1, 5); |
| PREPARE |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_update_ao; |
| c |
| --- |
| 1 |
| 3 |
| 5 |
| 2 |
| 6 |
| 8 |
| 4 |
| 7 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_nokeyupdate_ao; |
| c |
| --- |
| 1 |
| 3 |
| 5 |
| 2 |
| 6 |
| 8 |
| 4 |
| 7 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_share_ao; |
| c |
| --- |
| 1 |
| 3 |
| 5 |
| 2 |
| 6 |
| 8 |
| 4 |
| 7 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_keyshare_ao; |
| c |
| --- |
| 1 |
| 3 |
| 5 |
| 2 |
| 6 |
| 8 |
| 4 |
| 7 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute update_tlockmods_ao; |
| EXECUTE 8 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute delete_tlockmods_ao; |
| EXECUTE 8 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute insert_tlockmods_ao; |
| EXECUTE 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+--------------- |
| relation | RowExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| -- 1.3 With limit clause, such case should |
| -- acquire ExclusiveLock on the whole table and do not generate lockrows node |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods order by c limit 1 for update; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------- |
| Limit (cost=2.07..2.10 rows=1 width=10) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=2.07..2.10 rows=1 width=10) |
| Merge Key: c |
| -> Limit (cost=2.07..2.08 rows=1 width=10) |
| -> Sort (cost=2.07..2.09 rows=2 width=10) |
| Sort Key: c |
| -> Seq Scan on t_lockmods (cost=0.00..2.05 rows=2 width=10) |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| 1: select * from t_lockmods order by c limit 1 for update; |
| c |
| --- |
| 1 |
| (1 row) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------ |
| relation | AccessShareLock | t | t_lockmods |
| relation | ExclusiveLock | t | t_lockmods |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| -- 1.4 For replicated table, we should lock the entire table on ExclusiveLock |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_rep for update; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------ |
| Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..1063.00 rows=96300 width=10) |
| -> Seq Scan on t_lockmods_rep (cost=0.00..1063.00 rows=96300 width=10) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| 1: select * from t_lockmods_rep for update; |
| c |
| --- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+---------------- |
| relation | AccessShareLock | t | t_lockmods_rep |
| relation | ExclusiveLock | t | t_lockmods_rep |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| -- 1.5 test order-by's plan |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods order by c for update; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=2.11..2.12 rows=5 width=10) |
| Merge Key: c |
| -> Sort (cost=2.11..2.12 rows=2 width=10) |
| Sort Key: c |
| -> Seq Scan on t_lockmods (cost=0.00..2.05 rows=2 width=10) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| 1: select * from t_lockmods order by c for update; |
| c |
| --- |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------ |
| relation | AccessShareLock | t | t_lockmods |
| relation | ExclusiveLock | t | t_lockmods |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| -- 1.6 select for update NOWAIT/SKIP LOCKED |
| -- NOWAIT/SKIP LOCKED should not affect the table-level lock |
| 1: begin; |
| BEGIN |
| 1: select * from t_lockmods for share; |
| c |
| --- |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| (5 rows) |
| 2&: select * from t_lockmods for update nowait; <waiting ...> |
| 1: abort; |
| ABORT |
| 2<: <... completed> |
| c |
| --- |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| (5 rows) |
| |
| 1: begin; |
| BEGIN |
| 1: select * from t_lockmods for share; |
| c |
| --- |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| (5 rows) |
| 2&: select * from t_lockmods for update skip locked; <waiting ...> |
| 1: abort; |
| ABORT |
| 2<: <... completed> |
| c |
| --- |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| (5 rows) |
| |
| 1q: ... <quitting> |
| 2q: ... <quitting> |
| |
| -- 1.8 Test on DML lock behavior on Partition tables on QDs. |
| -- This suite will test: |
| -- * DML on root |
| -- * DML on one specific leaf |
| -- For detailed behavior and notes, please refer below |
| -- cases's comments. |
| -- Details: https://groups.google.com/a/greenplum.org/g/gpdb-dev/c/wAPKpJzhbpM |
| -- Issue: https://github.com/greenplum-db/gpdb/issues/13652 |
| 1:DROP TABLE IF EXISTS t_lockmods_part_tbl_dml; |
| DROP |
| |
| 1:CREATE TABLE t_lockmods_part_tbl_dml (a int, b int, c int) PARTITION BY RANGE(b) (START(1) END(3) EVERY(1)); |
| CREATE |
| 1:INSERT INTO t_lockmods_part_tbl_dml SELECT i, 1, i FROM generate_series(1,10)i; |
| INSERT 10 |
| |
| -- |
| 1: BEGIN; |
| BEGIN |
| 1: DELETE FROM t_lockmods_part_tbl_dml; |
| DELETE 10 |
| -- on QD, there's a lock on the root and the target partition |
| 1: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------------------------- |
| relation | ExclusiveLock | t | t_lockmods_part_tbl_dml |
| relation | ExclusiveLock | t | t_lockmods_part_tbl_dml_1_prt_1 |
| relation | ExclusiveLock | t | t_lockmods_part_tbl_dml_1_prt_2 |
| (3 rows) |
| 1: ROLLBACK; |
| ROLLBACK |
| |
| 1: BEGIN; |
| BEGIN |
| 1: INSERT INTO t_lockmods_part_tbl_dml SELECT i, 1, i FROM generate_series(1,10)i; |
| INSERT 10 |
| -- without GDD, it will lock all leaf partitions on QD |
| 1: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+--------------------------------- |
| relation | RowExclusiveLock | t | t_lockmods_part_tbl_dml_1_prt_2 |
| relation | RowExclusiveLock | t | t_lockmods_part_tbl_dml_1_prt_1 |
| relation | RowExclusiveLock | t | t_lockmods_part_tbl_dml |
| (3 rows) |
| 1: ROLLBACK; |
| ROLLBACK |
| |
| -- |
| -- The session cannot be reused. |
| -- |
| -- The macro RELCACHE_FORCE_RELEASE is defined iff USE_ASSERT_CHECKING is |
| -- defined, and when RELCACHE_FORCE_RELEASE is defined the relcache is |
| -- forcefully released when closing the relation. |
| -- |
| -- The function generate_partition_qual() will behave differently depends on |
| -- the existence of the relcache. |
| -- |
| -- - if the relation is not cached, it will open it in AccessShareLock mode, |
| -- and save the relpartbound in the relcache; |
| -- - if the relation is already cached, it will load the relpartbound from the |
| -- cache directly without opening the relation; |
| -- |
| -- So as a result, in the following transactions we will see an extra |
| -- AccessShareLock lock in a --enable-cassert build compared to a |
| -- --disable-cassert build. |
| -- |
| -- To make the test results stable, we do not reuse the sessions in the test, |
| -- all the tests are performed without the relcache. |
| 1q: ... <quitting> |
| |
| 1: BEGIN; |
| BEGIN |
| 1: UPDATE t_lockmods_part_tbl_dml SET c = 1 WHERE c = 1; |
| UPDATE 1 |
| -- on QD, there's a lock on the root and the target partition |
| 1: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------------------------- |
| relation | ExclusiveLock | t | t_lockmods_part_tbl_dml |
| relation | ExclusiveLock | t | t_lockmods_part_tbl_dml_1_prt_1 |
| relation | ExclusiveLock | t | t_lockmods_part_tbl_dml_1_prt_2 |
| (3 rows) |
| 1: ROLLBACK; |
| ROLLBACK |
| 1q: ... <quitting> |
| |
| 1: BEGIN; |
| BEGIN |
| 1: DELETE FROM t_lockmods_part_tbl_dml_1_prt_1; |
| DELETE 10 |
| -- since the delete operation is on leaf part, there will be a lock on QD |
| 1: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+--------------------------------- |
| relation | AccessShareLock | t | t_lockmods_part_tbl_dml |
| relation | ExclusiveLock | t | t_lockmods_part_tbl_dml_1_prt_1 |
| (2 rows) |
| 1: ROLLBACK; |
| ROLLBACK |
| 1q: ... <quitting> |
| |
| 1: BEGIN; |
| BEGIN |
| 1: UPDATE t_lockmods_part_tbl_dml_1_prt_1 SET c = 1 WHERE c = 1; |
| UPDATE 1 |
| -- since the update operation is on leaf part, there will be a lock on QD |
| 1: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+--------------------------------- |
| relation | AccessShareLock | t | t_lockmods_part_tbl_dml |
| relation | ExclusiveLock | t | t_lockmods_part_tbl_dml_1_prt_1 |
| (2 rows) |
| 1: ROLLBACK; |
| ROLLBACK |
| 1q: ... <quitting> |
| |
| -- enable gdd |
| ALTER SYSTEM SET gp_enable_global_deadlock_detector TO on; |
| ALTER |
| -- Use utility session on seg 0 to restart master. This way avoids the |
| -- situation where session issuing the restart doesn't disappear |
| -- itself. |
| 1U:SELECT pg_ctl(dir, 'restart') from lockmodes_datadir; |
| pg_ctl |
| -------- |
| OK |
| (1 row) |
| |
| 1: show gp_enable_global_deadlock_detector; |
| gp_enable_global_deadlock_detector |
| ------------------------------------ |
| on |
| (1 row) |
| |
| 1: set optimizer = off; |
| SET |
| |
| 2: show gp_enable_global_deadlock_detector; |
| gp_enable_global_deadlock_detector |
| ------------------------------------ |
| on |
| (1 row) |
| |
| -- 2. The firs part of test is with |
| -- gp_enable_global_deadlock_detector on |
| |
| -- 2.1 test for heap tables |
| |
| -- 2.1.1 select for (update|share|no key update |key share) should hold ExclusiveLock on range tables |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods for update; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1.10 rows=5 width=10) |
| -> LockRows (cost=0.00..1.03 rows=2 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..1.02 rows=2 width=10) |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| 1: select * from t_lockmods for update; |
| c |
| --- |
| 1 |
| 2 |
| 5 |
| 4 |
| 3 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------ |
| relation | AccessShareLock | t | t_lockmods |
| relation | RowShareLock | t | t_lockmods |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods for no key update; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..3.10 rows=5 width=10) |
| -> LockRows (cost=0.00..3.10 rows=2 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..3.05 rows=2 width=10) |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| 1: select * from t_lockmods for no key update; |
| c |
| --- |
| 1 |
| 2 |
| 5 |
| 3 |
| 4 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------ |
| relation | AccessShareLock | t | t_lockmods |
| relation | RowShareLock | t | t_lockmods |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods for share; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..3.10 rows=5 width=10) |
| -> LockRows (cost=0.00..3.10 rows=2 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..3.05 rows=2 width=10) |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| 1: select * from t_lockmods for share; |
| c |
| --- |
| 1 |
| 2 |
| 5 |
| 3 |
| 4 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------ |
| relation | AccessShareLock | t | t_lockmods |
| relation | RowShareLock | t | t_lockmods |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods for key share; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..3.10 rows=5 width=10) |
| -> LockRows (cost=0.00..3.10 rows=2 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..3.05 rows=2 width=10) |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| 1: select * from t_lockmods for key share; |
| c |
| --- |
| 1 |
| 2 |
| 5 |
| 3 |
| 4 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------ |
| relation | AccessShareLock | t | t_lockmods |
| relation | RowShareLock | t | t_lockmods |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods, t_lockmods1 for update; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000008862.58 rows=481500 width=20) |
| -> Nested Loop (cost=10000000000.00..10000002442.58 rows=160500 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.08 rows=5 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..1.02 rows=2 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods, t_lockmods1 for update; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------- |
| relation | AccessShareLock | t | t_lockmods1 |
| relation | ExclusiveLock | t | t_lockmods1 |
| relation | AccessShareLock | t | t_lockmods |
| relation | ExclusiveLock | t | t_lockmods |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods, t_lockmods1 for no key update; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000008862.58 rows=481500 width=20) |
| -> Nested Loop (cost=10000000000.00..10000002442.58 rows=160500 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.08 rows=5 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..1.02 rows=2 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods, t_lockmods1 for no key update; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------- |
| relation | AccessShareLock | t | t_lockmods1 |
| relation | ExclusiveLock | t | t_lockmods1 |
| relation | AccessShareLock | t | t_lockmods |
| relation | ExclusiveLock | t | t_lockmods |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods, t_lockmods1 for share; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000008862.58 rows=481500 width=20) |
| -> Nested Loop (cost=10000000000.00..10000002442.58 rows=160500 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.08 rows=5 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..1.02 rows=2 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods, t_lockmods1 for share; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------- |
| relation | AccessShareLock | t | t_lockmods1 |
| relation | ExclusiveLock | t | t_lockmods1 |
| relation | AccessShareLock | t | t_lockmods |
| relation | ExclusiveLock | t | t_lockmods |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods, t_lockmods1 for key share; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000008862.58 rows=481500 width=20) |
| -> Nested Loop (cost=10000000000.00..10000002442.58 rows=160500 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.08 rows=5 width=10) |
| -> Seq Scan on t_lockmods (cost=0.00..1.02 rows=2 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods, t_lockmods1 for key share; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------- |
| relation | AccessShareLock | t | t_lockmods1 |
| relation | ExclusiveLock | t | t_lockmods1 |
| relation | AccessShareLock | t | t_lockmods |
| relation | ExclusiveLock | t | t_lockmods |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| |
| -- 2.1.2 update | delete should hold RowExclusiveLock on result relations |
| 1: begin; |
| BEGIN |
| 1: update t_lockmods set c = c + 0; |
| UPDATE 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+------------ |
| relation | RowExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: delete from t_lockmods; |
| DELETE 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+------------ |
| relation | RowExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| -- 2.1.3 insert should hold RowExclusiveLock on result relations |
| 1: begin; |
| BEGIN |
| 1: insert into t_lockmods select * from generate_series(1, 5); |
| INSERT 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+------------ |
| relation | RowExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| -- 2.1.4 upsert should hold RowExclusiveLock on result relations |
| 1: begin; |
| BEGIN |
| 1: insert into t_lockmods_upsert values (1, 1) on conflict(a, b) do update set b = 99; |
| INSERT 1 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+------------------- |
| relation | RowExclusiveLock | t | t_lockmods_upsert |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| -- 2.1.5 use cached plan should be consistent with no cached plan |
| 1: prepare select_for_update as select * from t_lockmods for update; |
| PREPARE |
| 1: prepare select_for_nokeyupdate as select * from t_lockmods for no key update; |
| PREPARE |
| 1: prepare select_for_share as select * from t_lockmods for share; |
| PREPARE |
| 1: prepare select_for_keyshare as select * from t_lockmods for key share; |
| PREPARE |
| 1: prepare update_tlockmods as update t_lockmods set c = c + 0; |
| PREPARE |
| 1: prepare delete_tlockmods as delete from t_lockmods; |
| PREPARE |
| 1: prepare insert_tlockmods as insert into t_lockmods select * from generate_series(1, 5); |
| PREPARE |
| 1: prepare upsert_tlockmods as insert into t_lockmods_upsert values (1, 1) on conflict(a, b) do update set b = 99; |
| PREPARE |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_update; |
| c |
| --- |
| 3 |
| 4 |
| 1 |
| 2 |
| 5 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+--------------+---------+------------ |
| relation | RowShareLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_nokeyupdate; |
| c |
| --- |
| 4 |
| 1 |
| 2 |
| 5 |
| 3 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+--------------+---------+------------ |
| relation | RowShareLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_share; |
| c |
| --- |
| 1 |
| 2 |
| 5 |
| 3 |
| 4 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+--------------+---------+------------ |
| relation | RowShareLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_keyshare; |
| c |
| --- |
| 1 |
| 2 |
| 5 |
| 3 |
| 4 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+--------------+---------+------------ |
| relation | RowShareLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute update_tlockmods; |
| EXECUTE 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+------------ |
| relation | RowExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute delete_tlockmods; |
| EXECUTE 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+------------ |
| relation | RowExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute insert_tlockmods; |
| EXECUTE 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+------------ |
| relation | RowExclusiveLock | t | t_lockmods |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute upsert_tlockmods; |
| EXECUTE 1 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+------------------- |
| relation | RowExclusiveLock | t | t_lockmods_upsert |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| -- 2.2 test for AO table |
| |
| -- 2.2.1 select for (update|share|key share|no key update) should hold ExclusiveLock on range tables |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao for update; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1.08 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.08 rows=3 width=10) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| 1: select * from t_lockmods_ao for update; |
| c |
| --- |
| 2 |
| 6 |
| 8 |
| 1 |
| 3 |
| 5 |
| 4 |
| 7 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+--------------- |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao for no key update; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1.08 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.08 rows=3 width=10) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| 1: select * from t_lockmods_ao for no key update; |
| c |
| --- |
| 1 |
| 3 |
| 5 |
| 4 |
| 7 |
| 2 |
| 6 |
| 8 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+--------------- |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao for share; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1.08 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.08 rows=3 width=10) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| 1: select * from t_lockmods_ao for share; |
| c |
| --- |
| 4 |
| 7 |
| 2 |
| 6 |
| 8 |
| 1 |
| 3 |
| 5 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+--------------- |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao for key share; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1.08 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.08 rows=3 width=10) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| 1: select * from t_lockmods_ao for key share; |
| c |
| --- |
| 4 |
| 7 |
| 2 |
| 6 |
| 8 |
| 1 |
| 3 |
| 5 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+--------------- |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao, t_lockmods_ao1 for update; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000013918.38 rows=770400 width=20) |
| -> Nested Loop (cost=10000000000.00..10000003646.38 rows=256800 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.13 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.03 rows=3 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods_ao1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods_ao, t_lockmods_ao1 for update; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+---------------- |
| relation | AccessShareLock | t | t_lockmods_ao1 |
| relation | ExclusiveLock | t | t_lockmods_ao1 |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao, t_lockmods_ao1 for no key update; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000013918.38 rows=770400 width=20) |
| -> Nested Loop (cost=10000000000.00..10000003646.38 rows=256800 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.13 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.03 rows=3 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods_ao1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods_ao, t_lockmods_ao1 for no key update; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+---------------- |
| relation | AccessShareLock | t | t_lockmods_ao1 |
| relation | ExclusiveLock | t | t_lockmods_ao1 |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao, t_lockmods_ao1 for share; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000013918.38 rows=770400 width=20) |
| -> Nested Loop (cost=10000000000.00..10000003646.38 rows=256800 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.13 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.03 rows=3 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods_ao1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods_ao, t_lockmods_ao1 for share; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+---------------- |
| relation | AccessShareLock | t | t_lockmods_ao1 |
| relation | ExclusiveLock | t | t_lockmods_ao1 |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_ao, t_lockmods_ao1 for key share; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000013918.38 rows=770400 width=20) |
| -> Nested Loop (cost=10000000000.00..10000003646.38 rows=256800 width=20) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.13 rows=8 width=10) |
| -> Seq Scan on t_lockmods_ao (cost=0.00..1.03 rows=3 width=10) |
| -> Materialize (cost=0.00..515.50 rows=32100 width=10) |
| -> Seq Scan on t_lockmods_ao1 (cost=0.00..355.00 rows=32100 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods_ao, t_lockmods_ao1 for key share; |
| c | c |
| ---+--- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+---------------- |
| relation | AccessShareLock | t | t_lockmods_ao1 |
| relation | ExclusiveLock | t | t_lockmods_ao1 |
| relation | AccessShareLock | t | t_lockmods_ao |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (4 rows) |
| 1: abort; |
| ABORT |
| |
| -- 2.2.2 update | delete should hold ExclusiveLock on result relations |
| 1: begin; |
| BEGIN |
| 1: update t_lockmods_ao set c = c + 0; |
| UPDATE 8 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: delete from t_lockmods_ao; |
| DELETE 8 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| -- 2.2.3 insert should hold RowExclusiveLock on result relations |
| 1: begin; |
| BEGIN |
| 1: insert into t_lockmods_ao select * from generate_series(1, 5); |
| INSERT 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+--------------- |
| relation | RowExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| -- 2.2.4 use cached plan should be consistent with no cached plan |
| 1: prepare select_for_update_ao as select * from t_lockmods_ao for update; |
| PREPARE |
| 1: prepare select_for_nokeyupdate_ao as select * from t_lockmods_ao for no key update; |
| PREPARE |
| 1: prepare select_for_share_ao as select * from t_lockmods_ao for share; |
| PREPARE |
| 1: prepare select_for_keyshare_ao as select * from t_lockmods_ao for key share; |
| PREPARE |
| 1: prepare update_tlockmods_ao as update t_lockmods_ao set c = c + 0; |
| PREPARE |
| 1: prepare delete_tlockmods_ao as delete from t_lockmods_ao; |
| PREPARE |
| 1: prepare insert_tlockmods_ao as insert into t_lockmods_ao select * from generate_series(1, 5); |
| PREPARE |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_update_ao; |
| c |
| --- |
| 2 |
| 6 |
| 8 |
| 1 |
| 3 |
| 5 |
| 4 |
| 7 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_nokeyupdate_ao; |
| c |
| --- |
| 2 |
| 6 |
| 8 |
| 1 |
| 3 |
| 5 |
| 4 |
| 7 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_share_ao; |
| c |
| --- |
| 2 |
| 6 |
| 8 |
| 1 |
| 3 |
| 5 |
| 4 |
| 7 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute select_for_keyshare_ao; |
| c |
| --- |
| 2 |
| 6 |
| 8 |
| 4 |
| 7 |
| 1 |
| 3 |
| 5 |
| (8 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute update_tlockmods_ao; |
| EXECUTE 8 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute delete_tlockmods_ao; |
| EXECUTE 8 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+---------------+---------+--------------- |
| relation | ExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| 1: begin; |
| BEGIN |
| 1: execute insert_tlockmods_ao; |
| EXECUTE 5 |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+--------------- |
| relation | RowExclusiveLock | t | t_lockmods_ao |
| (1 row) |
| 1: abort; |
| ABORT |
| |
| -- 2.3 With limit clause, such case should |
| -- acquire ExclusiveLock on the whole table and do not generate lockrows node |
| -- GPDB_96_MERGE_FIXME: It's not deterministic which row this returns. See |
| -- 2.5 test below. |
| 1: begin; |
| BEGIN |
| 1: explain select 'locked' as l from t_lockmods order by c limit 1 for update; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------- |
| Limit (cost=1.04..1.06 rows=1 width=42) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=1.04..1.09 rows=3 width=42) |
| Merge Key: c |
| -> Limit (cost=1.04..1.05 rows=1 width=42) |
| -> LockRows (cost=1.03..1.05 rows=2 width=42) |
| -> Sort (cost=1.03..1.03 rows=2 width=42) |
| Sort Key: c |
| -> Seq Scan on t_lockmods (cost=0.00..3.05 rows=2 width=42) |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| 1: select 'locked' as l from t_lockmods order by c limit 1 for update; |
| l |
| -------- |
| locked |
| (1 row) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------ |
| relation | AccessShareLock | t | t_lockmods |
| relation | RowShareLock | t | t_lockmods |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| -- 2.4 For replicated table, we should lock the entire table on ExclusiveLock |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods_rep for update; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------ |
| Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..1063.00 rows=96300 width=10) |
| -> Seq Scan on t_lockmods_rep (cost=0.00..1063.00 rows=96300 width=10) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| 1: select * from t_lockmods_rep for update; |
| c |
| --- |
| (0 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+---------------- |
| relation | AccessShareLock | t | t_lockmods_rep |
| relation | ExclusiveLock | t | t_lockmods_rep |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| -- 2.5 test order-by's plan |
| 1: begin; |
| BEGIN |
| 1: explain select * from t_lockmods order by c for update; |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=3.11..3.27 rows=5 width=10) |
| Merge Key: c |
| -> LockRows (cost=3.11..3.17 rows=2 width=10) |
| -> Sort (cost=3.11..3.12 rows=2 width=10) |
| Sort Key: c |
| -> Seq Scan on t_lockmods (cost=0.00..3.05 rows=2 width=10) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 1: select * from t_lockmods order by c for update; |
| c |
| --- |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| (5 rows) |
| 2: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+-----------------+---------+------------ |
| relation | AccessShareLock | t | t_lockmods |
| relation | RowShareLock | t | t_lockmods |
| (2 rows) |
| 1: abort; |
| ABORT |
| |
| -- 2.6 select for update NOWAIT/SKIP LOCKED |
| -- with GDD, select for update could be optimized to not upgrade lock. |
| 1: set optimizer = off; |
| SET |
| 2: set optimizer = off; |
| SET |
| 1: begin; |
| BEGIN |
| 1: select * from t_lockmods where c<3 for share; |
| c |
| --- |
| 2 |
| 1 |
| (2 rows) |
| 2: select * from t_lockmods for share; |
| c |
| --- |
| 2 |
| 5 |
| 3 |
| 1 |
| 4 |
| (5 rows) |
| 2: select * from t_lockmods for update skip locked; |
| c |
| --- |
| 3 |
| 5 |
| 4 |
| (3 rows) |
| 2: select * from t_lockmods where c>=3 for update nowait; |
| c |
| --- |
| 5 |
| 4 |
| 3 |
| (3 rows) |
| 2: select * from t_lockmods for update nowait; |
| ERROR: could not obtain lock on row in relation "t_lockmods" (seg1 slice1 10.140.0.3:7003 pid=15182) |
| 1: abort; |
| ABORT |
| |
| 1q: ... <quitting> |
| 2q: ... <quitting> |
| |
| -- 2.7 Test on DML lock behavior on Partition tables on QDs. |
| -- This suite will test: |
| -- * DML on root |
| -- * DML on one specific leaf |
| -- For detailed behavior and notes, please refer below |
| -- cases's comments. |
| |
| 1: BEGIN; |
| BEGIN |
| 1: DELETE FROM t_lockmods_part_tbl_dml; |
| DELETE 10 |
| -- on QD, there's a lock on the root and the target partition |
| 1: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+--------------------------------- |
| relation | RowExclusiveLock | t | t_lockmods_part_tbl_dml_1_prt_2 |
| relation | RowExclusiveLock | t | t_lockmods_part_tbl_dml_1_prt_1 |
| relation | RowExclusiveLock | t | t_lockmods_part_tbl_dml |
| (3 rows) |
| 1: ROLLBACK; |
| ROLLBACK |
| 1q: ... <quitting> |
| |
| 1: BEGIN; |
| BEGIN |
| 1: UPDATE t_lockmods_part_tbl_dml SET c = 1 WHERE c = 1; |
| UPDATE 1 |
| -- on QD, there's a lock on the root and the target partition |
| 1: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+--------------------------------- |
| relation | RowExclusiveLock | t | t_lockmods_part_tbl_dml_1_prt_2 |
| relation | RowExclusiveLock | t | t_lockmods_part_tbl_dml_1_prt_1 |
| relation | RowExclusiveLock | t | t_lockmods_part_tbl_dml |
| (3 rows) |
| 1: ROLLBACK; |
| ROLLBACK |
| 1q: ... <quitting> |
| |
| 1: BEGIN; |
| BEGIN |
| 1: DELETE FROM t_lockmods_part_tbl_dml_1_prt_1; |
| DELETE 10 |
| -- since the delete operation is on leaf part, there will be a lock on QD |
| 1: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+--------------------------------- |
| relation | AccessShareLock | t | t_lockmods_part_tbl_dml |
| relation | RowExclusiveLock | t | t_lockmods_part_tbl_dml_1_prt_1 |
| (2 rows) |
| 1: ROLLBACK; |
| ROLLBACK |
| 1q: ... <quitting> |
| |
| 1: BEGIN; |
| BEGIN |
| 1: UPDATE t_lockmods_part_tbl_dml_1_prt_1 SET c = 1 WHERE c = 1; |
| UPDATE 1 |
| -- since the update operation is on leaf part, there will be a lock on QD |
| 1: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+--------------------------------- |
| relation | AccessShareLock | t | t_lockmods_part_tbl_dml |
| relation | RowExclusiveLock | t | t_lockmods_part_tbl_dml_1_prt_1 |
| (2 rows) |
| 1: ROLLBACK; |
| ROLLBACK |
| 1q: ... <quitting> |
| |
| 1: BEGIN; |
| BEGIN |
| 1: INSERT INTO t_lockmods_part_tbl_dml SELECT i, 1, i FROM generate_series(1,10)i; |
| INSERT 10 |
| -- With GDD enabled, QD will only hold lock on root for insert |
| 1: select * from show_locks_lockmodes; |
| locktype | mode | granted | relation |
| ----------+------------------+---------+------------------------- |
| relation | AccessShareLock | t | t_lockmods_part_tbl_dml |
| relation | RowExclusiveLock | t | t_lockmods_part_tbl_dml |
| (2 rows) |
| 1: ROLLBACK; |
| ROLLBACK |
| 1q: ... <quitting> |
| |
| -- 2.8 Verify behaviors of select with locking clause (i.e. select for update) |
| -- when running concurrently with index creation, for Heap tables. |
| -- For AO/CO tables, refer to create_index_allows_readonly.source. |
| |
| 1: CREATE TABLE create_index_select_for_update_tbl(a int, b int); |
| CREATE |
| 1: INSERT INTO create_index_select_for_update_tbl SELECT i,i FROM generate_series(1,10)i; |
| INSERT 10 |
| 1: set optimizer = off; |
| SET |
| |
| -- 2.8.1 with GDD enabled, expect no blocking |
| 1: show gp_enable_global_deadlock_detector; |
| gp_enable_global_deadlock_detector |
| ------------------------------------ |
| on |
| (1 row) |
| |
| 1: BEGIN; |
| BEGIN |
| 1: SELECT * FROM create_index_select_for_update_tbl WHERE a = 2 FOR UPDATE; |
| a | b |
| ---+--- |
| 2 | 2 |
| (1 row) |
| |
| 2: set optimizer = off; |
| SET |
| |
| 2: BEGIN; |
| BEGIN |
| -- expect no blocking |
| 2: CREATE INDEX create_index_select_for_update_idx ON create_index_select_for_update_tbl(a); |
| CREATE |
| 2: COMMIT; |
| COMMIT |
| |
| 1: COMMIT; |
| COMMIT |
| |
| 2: DROP INDEX create_index_select_for_update_idx; |
| DROP |
| |
| 2: BEGIN; |
| BEGIN |
| 2: CREATE INDEX create_index_select_for_update_idx ON create_index_select_for_update_tbl(a); |
| CREATE |
| |
| 1: BEGIN; |
| BEGIN |
| -- expect no blocking |
| 1: SELECT * FROM create_index_select_for_update_tbl WHERE a = 2 FOR UPDATE; |
| a | b |
| ---+--- |
| 2 | 2 |
| (1 row) |
| 1: COMMIT; |
| COMMIT |
| -- close session to avoid renew session failure after restart |
| 1q: ... <quitting> |
| |
| 2: COMMIT; |
| COMMIT |
| |
| 2: DROP INDEX create_index_select_for_update_idx; |
| DROP |
| |
| -- 2.8.2 with GDD disabled, expect blocking |
| -- reset gdd |
| 2: ALTER SYSTEM RESET gp_enable_global_deadlock_detector; |
| ALTER |
| -- close session to avoid renew session failure after restart |
| 2q: ... <quitting> |
| 1U:SELECT pg_ctl(dir, 'restart') from lockmodes_datadir; |
| pg_ctl |
| -------- |
| OK |
| (1 row) |
| |
| 1: set optimizer = off; |
| SET |
| 1: show gp_enable_global_deadlock_detector; |
| gp_enable_global_deadlock_detector |
| ------------------------------------ |
| off |
| (1 row) |
| |
| 1: BEGIN; |
| BEGIN |
| 1: SELECT * FROM create_index_select_for_update_tbl WHERE a = 2 FOR UPDATE; |
| a | b |
| ---+--- |
| 2 | 2 |
| (1 row) |
| |
| 2: set optimizer = off; |
| SET |
| |
| 2: BEGIN; |
| BEGIN |
| -- expect blocking |
| 2&: CREATE INDEX create_index_select_for_update_idx ON create_index_select_for_update_tbl(a); <waiting ...> |
| |
| 1: COMMIT; |
| COMMIT |
| |
| 2<: <... completed> |
| CREATE |
| 2: COMMIT; |
| COMMIT |
| |
| 2: DROP INDEX create_index_select_for_update_idx; |
| DROP |
| |
| 2: BEGIN; |
| BEGIN |
| 2: CREATE INDEX create_index_select_for_update_idx ON create_index_select_for_update_tbl(a); |
| CREATE |
| |
| 1: BEGIN; |
| BEGIN |
| -- expect blocking |
| 1&: SELECT * FROM create_index_select_for_update_tbl WHERE a = 2 FOR UPDATE; <waiting ...> |
| |
| 2: COMMIT; |
| COMMIT |
| |
| 1<: <... completed> |
| a | b |
| ---+--- |
| 2 | 2 |
| (1 row) |
| 1: COMMIT; |
| COMMIT |
| |
| 1: drop table lockmodes_datadir; |
| DROP |
| 1q: ... <quitting> |
| 2q: ... <quitting> |
| |
| -- Check that concurrent DROP on leaf partition won't impact analyze on the |
| -- parent since analyze will hold a ShareUpdateExclusiveLock and DROP will |
| -- require an AccessExclusiveLock. |
| -- Case 1. The analyze result is expected when there's concurrent drop on child. |
| 1:create table analyzedrop(a int) partition by range(a); |
| CREATE |
| 1:create table analyzedrop_1 partition of analyzedrop for values from (0) to (10); |
| CREATE |
| 1:create table analyzedrop_2 partition of analyzedrop for values from (10) to (20); |
| CREATE |
| 1:insert into analyzedrop select * from generate_series(0,19); |
| INSERT 20 |
| 1:select gp_inject_fault_infinite('merge_leaf_stats_after_find_children', 'suspend', dbid) from gp_segment_configuration where content = -1 and role = 'p'; |
| gp_inject_fault_infinite |
| -------------------------- |
| Success: |
| (1 row) |
| 1&: analyze analyzedrop; <waiting ...> |
| 2&: drop table analyzedrop_1; <waiting ...> |
| 3:select gp_inject_fault_infinite('merge_leaf_stats_after_find_children', 'reset', dbid) from gp_segment_configuration where content = -1 and role = 'p'; |
| gp_inject_fault_infinite |
| -------------------------- |
| Success: |
| (1 row) |
| 1<: <... completed> |
| ANALYZE |
| 2<: <... completed> |
| DROP |
| 3:select * from pg_stats where tablename like 'analyzedrop%'; |
| schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram |
| ------------+---------------+---------+-----------+-----------+-----------+------------+------------------+-------------------+--------------------------------------------------+-------------+-------------------+------------------------+---------------------- |
| public | analyzedrop | a | t | 0 | 4 | -1 | | | {0,1,2,3,4,5,6,7,8,9,11,12,13,14,15,16,17,18,19} | | | | |
| public | analyzedrop_2 | a | f | 0 | 4 | -1 | | | {10,11,12,13,14,15,16,17,18,19} | 1 | | | |
| (2 rows) |
| -- Case 2. No failure should happen when there's concurrent drop on parent as well. |
| 1:select gp_inject_fault_infinite('merge_leaf_stats_after_find_children', 'suspend', dbid) from gp_segment_configuration where content = -1 and role = 'p'; |
| gp_inject_fault_infinite |
| -------------------------- |
| Success: |
| (1 row) |
| 1&: analyze analyzedrop; <waiting ...> |
| 2&: drop table analyzedrop_2; <waiting ...> |
| 3&: drop table analyzedrop; <waiting ...> |
| 4:select gp_inject_fault_infinite('merge_leaf_stats_after_find_children', 'reset', dbid) from gp_segment_configuration where content = -1 and role = 'p'; |
| gp_inject_fault_infinite |
| -------------------------- |
| Success: |
| (1 row) |
| 1<: <... completed> |
| ANALYZE |
| 2<: <... completed> |
| DROP |
| 3<: <... completed> |
| DROP |
| --empty as table is dropped |
| 4:select * from pg_stats where tablename like 'analyzedrop%'; |
| schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram |
| ------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+---------------------- |
| (0 rows) |
