1Parsed test spec with 3 sessions 2 3starting permutation: controller_locks controller_show s1_upsert s2_upsert controller_show controller_unlock_1_1 controller_unlock_2_1 controller_unlock_1_3 controller_unlock_2_3 controller_show controller_unlock_2_2 controller_show controller_unlock_1_2 controller_show 4step controller_locks: SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock); 5pg_advisory_lock|sess|lock 6----------------+----+---- 7 | 1| 1 8 | 1| 2 9 | 1| 3 10 | 2| 1 11 | 2| 2 12 | 2| 3 13(6 rows) 14 15step controller_show: SELECT * FROM upserttest; 16key|data 17---+---- 18(0 rows) 19 20s1: NOTICE: blurt_and_lock_123() called for k1 in session 1 21s1: NOTICE: acquiring advisory lock on 3 22step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...> 23s2: NOTICE: blurt_and_lock_123() called for k1 in session 2 24s2: NOTICE: acquiring advisory lock on 3 25step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...> 26step controller_show: SELECT * FROM upserttest; 27key|data 28---+---- 29(0 rows) 30 31step controller_unlock_1_1: SELECT pg_advisory_unlock(1, 1); 32pg_advisory_unlock 33------------------ 34t 35(1 row) 36 37step controller_unlock_2_1: SELECT pg_advisory_unlock(2, 1); 38pg_advisory_unlock 39------------------ 40t 41(1 row) 42 43step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3); 44pg_advisory_unlock 45------------------ 46t 47(1 row) 48 49s1: NOTICE: blurt_and_lock_123() called for k1 in session 1 50s1: NOTICE: acquiring advisory lock on 2 51step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3); 52pg_advisory_unlock 53------------------ 54t 55(1 row) 56 57s2: NOTICE: blurt_and_lock_123() called for k1 in session 2 58s2: NOTICE: acquiring advisory lock on 2 59step controller_show: SELECT * FROM upserttest; 60key|data 61---+---- 62(0 rows) 63 64step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2); 65pg_advisory_unlock 66------------------ 67t 68(1 row) 69 70step s2_upsert: <... completed> 71step controller_show: SELECT * FROM upserttest; 72key|data 73---+----------- 74k1 |inserted s2 75(1 row) 76 77step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2); 78pg_advisory_unlock 79------------------ 80t 81(1 row) 82 83s1: NOTICE: blurt_and_lock_123() called for k1 in session 1 84s1: NOTICE: acquiring advisory lock on 2 85s1: NOTICE: blurt_and_lock_123() called for k1 in session 1 86s1: NOTICE: acquiring advisory lock on 2 87step s1_upsert: <... completed> 88step controller_show: SELECT * FROM upserttest; 89key|data 90---+----------------------------------- 91k1 |inserted s2 with conflict update s1 92(1 row) 93 94 95starting permutation: controller_locks controller_show s1_upsert s2_upsert controller_show controller_unlock_1_1 controller_unlock_2_1 controller_unlock_1_3 controller_unlock_2_3 controller_show controller_unlock_1_2 controller_show controller_unlock_2_2 controller_show 96step controller_locks: SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock); 97pg_advisory_lock|sess|lock 98----------------+----+---- 99 | 1| 1 100 | 1| 2 101 | 1| 3 102 | 2| 1 103 | 2| 2 104 | 2| 3 105(6 rows) 106 107step controller_show: SELECT * FROM upserttest; 108key|data 109---+---- 110(0 rows) 111 112s1: NOTICE: blurt_and_lock_123() called for k1 in session 1 113s1: NOTICE: acquiring advisory lock on 3 114step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...> 115s2: NOTICE: blurt_and_lock_123() called for k1 in session 2 116s2: NOTICE: acquiring advisory lock on 3 117step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...> 118step controller_show: SELECT * FROM upserttest; 119key|data 120---+---- 121(0 rows) 122 123step controller_unlock_1_1: SELECT pg_advisory_unlock(1, 1); 124pg_advisory_unlock 125------------------ 126t 127(1 row) 128 129step controller_unlock_2_1: SELECT pg_advisory_unlock(2, 1); 130pg_advisory_unlock 131------------------ 132t 133(1 row) 134 135step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3); 136pg_advisory_unlock 137------------------ 138t 139(1 row) 140 141s1: NOTICE: blurt_and_lock_123() called for k1 in session 1 142s1: NOTICE: acquiring advisory lock on 2 143step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3); 144pg_advisory_unlock 145------------------ 146t 147(1 row) 148 149s2: NOTICE: blurt_and_lock_123() called for k1 in session 2 150s2: NOTICE: acquiring advisory lock on 2 151step controller_show: SELECT * FROM upserttest; 152key|data 153---+---- 154(0 rows) 155 156step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2); 157pg_advisory_unlock 158------------------ 159t 160(1 row) 161 162step s1_upsert: <... completed> 163step controller_show: SELECT * FROM upserttest; 164key|data 165---+----------- 166k1 |inserted s1 167(1 row) 168 169step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2); 170pg_advisory_unlock 171------------------ 172t 173(1 row) 174 175s2: NOTICE: blurt_and_lock_123() called for k1 in session 2 176s2: NOTICE: acquiring advisory lock on 2 177s2: NOTICE: blurt_and_lock_123() called for k1 in session 2 178s2: NOTICE: acquiring advisory lock on 2 179step s2_upsert: <... completed> 180step controller_show: SELECT * FROM upserttest; 181key|data 182---+----------------------------------- 183k1 |inserted s1 with conflict update s2 184(1 row) 185 186 187starting permutation: controller_locks controller_show s1_insert_toast s2_insert_toast controller_show controller_unlock_1_1 controller_unlock_2_1 controller_unlock_1_3 controller_unlock_2_3 controller_show controller_unlock_1_2 controller_show_count controller_unlock_2_2 controller_show_count 188step controller_locks: SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock); 189pg_advisory_lock|sess|lock 190----------------+----+---- 191 | 1| 1 192 | 1| 2 193 | 1| 3 194 | 2| 1 195 | 2| 2 196 | 2| 3 197(6 rows) 198 199step controller_show: SELECT * FROM upserttest; 200key|data 201---+---- 202(0 rows) 203 204s1: NOTICE: blurt_and_lock_123() called for k2 in session 1 205s1: NOTICE: acquiring advisory lock on 3 206step s1_insert_toast: INSERT INTO upserttest VALUES('k2', ctoast_large_val()) ON CONFLICT DO NOTHING; <waiting ...> 207s2: NOTICE: blurt_and_lock_123() called for k2 in session 2 208s2: NOTICE: acquiring advisory lock on 3 209step s2_insert_toast: INSERT INTO upserttest VALUES('k2', ctoast_large_val()) ON CONFLICT DO NOTHING; <waiting ...> 210step controller_show: SELECT * FROM upserttest; 211key|data 212---+---- 213(0 rows) 214 215step controller_unlock_1_1: SELECT pg_advisory_unlock(1, 1); 216pg_advisory_unlock 217------------------ 218t 219(1 row) 220 221step controller_unlock_2_1: SELECT pg_advisory_unlock(2, 1); 222pg_advisory_unlock 223------------------ 224t 225(1 row) 226 227step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3); 228pg_advisory_unlock 229------------------ 230t 231(1 row) 232 233s1: NOTICE: blurt_and_lock_123() called for k2 in session 1 234s1: NOTICE: acquiring advisory lock on 2 235step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3); 236pg_advisory_unlock 237------------------ 238t 239(1 row) 240 241s2: NOTICE: blurt_and_lock_123() called for k2 in session 2 242s2: NOTICE: acquiring advisory lock on 2 243step controller_show: SELECT * FROM upserttest; 244key|data 245---+---- 246(0 rows) 247 248step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2); 249pg_advisory_unlock 250------------------ 251t 252(1 row) 253 254step s1_insert_toast: <... completed> 255step controller_show_count: SELECT COUNT(*) FROM upserttest; 256count 257----- 258 1 259(1 row) 260 261step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2); 262pg_advisory_unlock 263------------------ 264t 265(1 row) 266 267s2: NOTICE: blurt_and_lock_123() called for k2 in session 2 268s2: NOTICE: acquiring advisory lock on 2 269s2: NOTICE: blurt_and_lock_123() called for k2 in session 2 270s2: NOTICE: acquiring advisory lock on 2 271step s2_insert_toast: <... completed> 272step controller_show_count: SELECT COUNT(*) FROM upserttest; 273count 274----- 275 1 276(1 row) 277 278 279starting permutation: controller_locks controller_show s1_begin s2_begin s1_upsert s2_upsert controller_show controller_unlock_1_1 controller_unlock_2_1 controller_unlock_1_3 controller_unlock_2_3 controller_show controller_unlock_1_2 controller_show controller_unlock_2_2 controller_show s1_commit controller_show s2_commit controller_show 280step controller_locks: SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock); 281pg_advisory_lock|sess|lock 282----------------+----+---- 283 | 1| 1 284 | 1| 2 285 | 1| 3 286 | 2| 1 287 | 2| 2 288 | 2| 3 289(6 rows) 290 291step controller_show: SELECT * FROM upserttest; 292key|data 293---+---- 294(0 rows) 295 296step s1_begin: BEGIN; 297step s2_begin: BEGIN; 298s1: NOTICE: blurt_and_lock_123() called for k1 in session 1 299s1: NOTICE: acquiring advisory lock on 3 300step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...> 301s2: NOTICE: blurt_and_lock_123() called for k1 in session 2 302s2: NOTICE: acquiring advisory lock on 3 303step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...> 304step controller_show: SELECT * FROM upserttest; 305key|data 306---+---- 307(0 rows) 308 309step controller_unlock_1_1: SELECT pg_advisory_unlock(1, 1); 310pg_advisory_unlock 311------------------ 312t 313(1 row) 314 315step controller_unlock_2_1: SELECT pg_advisory_unlock(2, 1); 316pg_advisory_unlock 317------------------ 318t 319(1 row) 320 321step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3); 322pg_advisory_unlock 323------------------ 324t 325(1 row) 326 327s1: NOTICE: blurt_and_lock_123() called for k1 in session 1 328s1: NOTICE: acquiring advisory lock on 2 329step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3); 330pg_advisory_unlock 331------------------ 332t 333(1 row) 334 335s2: NOTICE: blurt_and_lock_123() called for k1 in session 2 336s2: NOTICE: acquiring advisory lock on 2 337step controller_show: SELECT * FROM upserttest; 338key|data 339---+---- 340(0 rows) 341 342step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2); 343pg_advisory_unlock 344------------------ 345t 346(1 row) 347 348step s1_upsert: <... completed> 349step controller_show: SELECT * FROM upserttest; 350key|data 351---+---- 352(0 rows) 353 354step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2); 355pg_advisory_unlock 356------------------ 357t 358(1 row) 359 360s2: NOTICE: blurt_and_lock_123() called for k1 in session 2 361s2: NOTICE: acquiring advisory lock on 2 362s2: NOTICE: blurt_and_lock_123() called for k1 in session 2 363s2: NOTICE: acquiring advisory lock on 2 364step controller_show: SELECT * FROM upserttest; 365key|data 366---+---- 367(0 rows) 368 369step s1_commit: COMMIT; 370s2: NOTICE: blurt_and_lock_123() called for k1 in session 2 371s2: NOTICE: acquiring advisory lock on 2 372step s2_upsert: <... completed> 373step controller_show: SELECT * FROM upserttest; 374key|data 375---+----------- 376k1 |inserted s1 377(1 row) 378 379step s2_commit: COMMIT; 380step controller_show: SELECT * FROM upserttest; 381key|data 382---+----------------------------------- 383k1 |inserted s1 with conflict update s2 384(1 row) 385 386 387starting permutation: s1_create_non_unique_index s1_confirm_index_order controller_locks controller_show s2_begin s1_upsert s2_upsert controller_show controller_unlock_1_1 controller_unlock_2_1 controller_unlock_1_3 controller_unlock_2_3 controller_show controller_lock_2_4 controller_unlock_2_2 controller_show controller_unlock_1_2 controller_print_speculative_locks controller_unlock_2_4 s2_noop controller_print_speculative_locks s2_commit s1_noop controller_show controller_print_speculative_locks 388step s1_create_non_unique_index: CREATE INDEX upserttest_key_idx ON upserttest((blurt_and_lock_4(key))); 389step s1_confirm_index_order: SELECT 'upserttest_key_uniq_idx'::regclass::int8 < 'upserttest_key_idx'::regclass::int8; 390?column? 391-------- 392t 393(1 row) 394 395step controller_locks: SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock); 396pg_advisory_lock|sess|lock 397----------------+----+---- 398 | 1| 1 399 | 1| 2 400 | 1| 3 401 | 2| 1 402 | 2| 2 403 | 2| 3 404(6 rows) 405 406step controller_show: SELECT * FROM upserttest; 407key|data 408---+---- 409(0 rows) 410 411step s2_begin: BEGIN; 412s1: NOTICE: blurt_and_lock_123() called for k1 in session 1 413s1: NOTICE: acquiring advisory lock on 3 414step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...> 415s2: NOTICE: blurt_and_lock_123() called for k1 in session 2 416s2: NOTICE: acquiring advisory lock on 3 417step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...> 418step controller_show: SELECT * FROM upserttest; 419key|data 420---+---- 421(0 rows) 422 423step controller_unlock_1_1: SELECT pg_advisory_unlock(1, 1); 424pg_advisory_unlock 425------------------ 426t 427(1 row) 428 429step controller_unlock_2_1: SELECT pg_advisory_unlock(2, 1); 430pg_advisory_unlock 431------------------ 432t 433(1 row) 434 435step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3); 436pg_advisory_unlock 437------------------ 438t 439(1 row) 440 441s1: NOTICE: blurt_and_lock_123() called for k1 in session 1 442s1: NOTICE: acquiring advisory lock on 2 443step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3); 444pg_advisory_unlock 445------------------ 446t 447(1 row) 448 449s2: NOTICE: blurt_and_lock_123() called for k1 in session 2 450s2: NOTICE: acquiring advisory lock on 2 451step controller_show: SELECT * FROM upserttest; 452key|data 453---+---- 454(0 rows) 455 456step controller_lock_2_4: SELECT pg_advisory_lock(2, 4); 457pg_advisory_lock 458---------------- 459 460(1 row) 461 462step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2); 463pg_advisory_unlock 464------------------ 465t 466(1 row) 467 468s2: NOTICE: blurt_and_lock_4() called for k1 in session 2 469s2: NOTICE: acquiring advisory lock on 4 470step controller_show: SELECT * FROM upserttest; 471key|data 472---+---- 473(0 rows) 474 475step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2); 476pg_advisory_unlock 477------------------ 478t 479(1 row) 480 481s1: NOTICE: blurt_and_lock_4() called for k1 in session 1 482s1: NOTICE: acquiring advisory lock on 4 483s1: NOTICE: blurt_and_lock_123() called for k1 in session 1 484s1: NOTICE: acquiring advisory lock on 2 485s1: NOTICE: blurt_and_lock_123() called for k1 in session 1 486s1: NOTICE: acquiring advisory lock on 2 487step controller_print_speculative_locks: 488 SELECT pa.application_name, locktype, mode, granted 489 FROM pg_locks pl JOIN pg_stat_activity pa USING (pid) 490 WHERE 491 locktype IN ('spectoken', 'transactionid') 492 AND pa.datname = current_database() 493 AND pa.application_name LIKE 'isolation/insert-conflict-specconflict-s%' 494 ORDER BY 1, 2, 3, 4; 495 496application_name |locktype |mode |granted 497-----------------------------------------+-------------+-------------+------- 498isolation/insert-conflict-specconflict-s1|spectoken |ShareLock |f 499isolation/insert-conflict-specconflict-s1|transactionid|ExclusiveLock|t 500isolation/insert-conflict-specconflict-s2|spectoken |ExclusiveLock|t 501isolation/insert-conflict-specconflict-s2|transactionid|ExclusiveLock|t 502(4 rows) 503 504step controller_unlock_2_4: SELECT pg_advisory_unlock(2, 4); 505pg_advisory_unlock 506------------------ 507t 508(1 row) 509 510s1: NOTICE: blurt_and_lock_123() called for k1 in session 1 511s1: NOTICE: acquiring advisory lock on 2 512step s2_upsert: <... completed> 513step s2_noop: 514step controller_print_speculative_locks: 515 SELECT pa.application_name, locktype, mode, granted 516 FROM pg_locks pl JOIN pg_stat_activity pa USING (pid) 517 WHERE 518 locktype IN ('spectoken', 'transactionid') 519 AND pa.datname = current_database() 520 AND pa.application_name LIKE 'isolation/insert-conflict-specconflict-s%' 521 ORDER BY 1, 2, 3, 4; 522 523application_name |locktype |mode |granted 524-----------------------------------------+-------------+-------------+------- 525isolation/insert-conflict-specconflict-s1|transactionid|ExclusiveLock|t 526isolation/insert-conflict-specconflict-s1|transactionid|ShareLock |f 527isolation/insert-conflict-specconflict-s2|transactionid|ExclusiveLock|t 528(3 rows) 529 530step s2_commit: COMMIT; 531s1: NOTICE: blurt_and_lock_123() called for k1 in session 1 532s1: NOTICE: acquiring advisory lock on 2 533step s1_upsert: <... completed> 534step s1_noop: 535step controller_show: SELECT * FROM upserttest; 536key|data 537---+----------------------------------- 538k1 |inserted s2 with conflict update s1 539(1 row) 540 541step controller_print_speculative_locks: 542 SELECT pa.application_name, locktype, mode, granted 543 FROM pg_locks pl JOIN pg_stat_activity pa USING (pid) 544 WHERE 545 locktype IN ('spectoken', 'transactionid') 546 AND pa.datname = current_database() 547 AND pa.application_name LIKE 'isolation/insert-conflict-specconflict-s%' 548 ORDER BY 1, 2, 3, 4; 549 550application_name|locktype|mode|granted 551----------------+--------+----+------- 552(0 rows) 553 554