1-- 2-- TRANSACTIONS 3-- 4BEGIN; 5SELECT * 6 INTO TABLE xacttest 7 FROM aggtest; 8INSERT INTO xacttest (a, b) VALUES (777, 777.777); 9END; 10-- should retrieve one value-- 11SELECT a FROM xacttest WHERE a > 100; 12 a 13----- 14 777 15(1 row) 16 17BEGIN; 18CREATE TABLE disappear (a int4); 19DELETE FROM aggtest; 20-- should be empty 21SELECT * FROM aggtest; 22 a | b 23---+--- 24(0 rows) 25 26ABORT; 27-- should not exist 28SELECT oid FROM pg_class WHERE relname = 'disappear'; 29 oid 30----- 31(0 rows) 32 33-- should have members again 34SELECT * FROM aggtest; 35 a | b 36-----+--------- 37 56 | 7.8 38 100 | 99.097 39 0 | 0.09561 40 42 | 324.78 41(4 rows) 42 43-- Read-only tests 44CREATE TABLE writetest (a int); 45CREATE TEMPORARY TABLE temptest (a int); 46BEGIN; 47SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE; -- ok 48SELECT * FROM writetest; -- ok 49 a 50--- 51(0 rows) 52 53SET TRANSACTION READ WRITE; --fail 54ERROR: transaction read-write mode must be set before any query 55COMMIT; 56BEGIN; 57SET TRANSACTION READ ONLY; -- ok 58SET TRANSACTION READ WRITE; -- ok 59SET TRANSACTION READ ONLY; -- ok 60SELECT * FROM writetest; -- ok 61 a 62--- 63(0 rows) 64 65SAVEPOINT x; 66SET TRANSACTION READ ONLY; -- ok 67SELECT * FROM writetest; -- ok 68 a 69--- 70(0 rows) 71 72SET TRANSACTION READ ONLY; -- ok 73SET TRANSACTION READ WRITE; --fail 74ERROR: cannot set transaction read-write mode inside a read-only transaction 75COMMIT; 76BEGIN; 77SET TRANSACTION READ WRITE; -- ok 78SAVEPOINT x; 79SET TRANSACTION READ WRITE; -- ok 80SET TRANSACTION READ ONLY; -- ok 81SELECT * FROM writetest; -- ok 82 a 83--- 84(0 rows) 85 86SET TRANSACTION READ ONLY; -- ok 87SET TRANSACTION READ WRITE; --fail 88ERROR: cannot set transaction read-write mode inside a read-only transaction 89COMMIT; 90BEGIN; 91SET TRANSACTION READ WRITE; -- ok 92SAVEPOINT x; 93SET TRANSACTION READ ONLY; -- ok 94SELECT * FROM writetest; -- ok 95 a 96--- 97(0 rows) 98 99ROLLBACK TO SAVEPOINT x; 100SHOW transaction_read_only; -- off 101 transaction_read_only 102----------------------- 103 off 104(1 row) 105 106SAVEPOINT y; 107SET TRANSACTION READ ONLY; -- ok 108SELECT * FROM writetest; -- ok 109 a 110--- 111(0 rows) 112 113RELEASE SAVEPOINT y; 114SHOW transaction_read_only; -- off 115 transaction_read_only 116----------------------- 117 off 118(1 row) 119 120COMMIT; 121SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY; 122DROP TABLE writetest; -- fail 123ERROR: cannot execute DROP TABLE in a read-only transaction 124INSERT INTO writetest VALUES (1); -- fail 125ERROR: cannot execute INSERT in a read-only transaction 126SELECT * FROM writetest; -- ok 127 a 128--- 129(0 rows) 130 131DELETE FROM temptest; -- ok 132UPDATE temptest SET a = 0 FROM writetest WHERE temptest.a = 1 AND writetest.a = temptest.a; -- ok 133PREPARE test AS UPDATE writetest SET a = 0; -- ok 134EXECUTE test; -- fail 135ERROR: cannot execute UPDATE in a read-only transaction 136SELECT * FROM writetest, temptest; -- ok 137 a | a 138---+--- 139(0 rows) 140 141CREATE TABLE test AS SELECT * FROM writetest; -- fail 142ERROR: cannot execute CREATE TABLE AS in a read-only transaction 143START TRANSACTION READ WRITE; 144DROP TABLE writetest; -- ok 145COMMIT; 146-- Subtransactions, basic tests 147-- create & drop tables 148SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE; 149CREATE TABLE foobar (a int); 150BEGIN; 151 CREATE TABLE foo (a int); 152 SAVEPOINT one; 153 DROP TABLE foo; 154 CREATE TABLE bar (a int); 155 ROLLBACK TO SAVEPOINT one; 156 RELEASE SAVEPOINT one; 157 SAVEPOINT two; 158 CREATE TABLE baz (a int); 159 RELEASE SAVEPOINT two; 160 drop TABLE foobar; 161 CREATE TABLE barbaz (a int); 162COMMIT; 163-- should exist: barbaz, baz, foo 164SELECT * FROM foo; -- should be empty 165 a 166--- 167(0 rows) 168 169SELECT * FROM bar; -- shouldn't exist 170ERROR: relation "bar" does not exist 171LINE 1: SELECT * FROM bar; 172 ^ 173SELECT * FROM barbaz; -- should be empty 174 a 175--- 176(0 rows) 177 178SELECT * FROM baz; -- should be empty 179 a 180--- 181(0 rows) 182 183-- inserts 184BEGIN; 185 INSERT INTO foo VALUES (1); 186 SAVEPOINT one; 187 INSERT into bar VALUES (1); 188ERROR: relation "bar" does not exist 189LINE 1: INSERT into bar VALUES (1); 190 ^ 191 ROLLBACK TO one; 192 RELEASE SAVEPOINT one; 193 SAVEPOINT two; 194 INSERT into barbaz VALUES (1); 195 RELEASE two; 196 SAVEPOINT three; 197 SAVEPOINT four; 198 INSERT INTO foo VALUES (2); 199 RELEASE SAVEPOINT four; 200 ROLLBACK TO SAVEPOINT three; 201 RELEASE SAVEPOINT three; 202 INSERT INTO foo VALUES (3); 203COMMIT; 204SELECT * FROM foo; -- should have 1 and 3 205 a 206--- 207 1 208 3 209(2 rows) 210 211SELECT * FROM barbaz; -- should have 1 212 a 213--- 214 1 215(1 row) 216 217-- test whole-tree commit 218BEGIN; 219 SAVEPOINT one; 220 SELECT foo; 221ERROR: column "foo" does not exist 222LINE 1: SELECT foo; 223 ^ 224 ROLLBACK TO SAVEPOINT one; 225 RELEASE SAVEPOINT one; 226 SAVEPOINT two; 227 CREATE TABLE savepoints (a int); 228 SAVEPOINT three; 229 INSERT INTO savepoints VALUES (1); 230 SAVEPOINT four; 231 INSERT INTO savepoints VALUES (2); 232 SAVEPOINT five; 233 INSERT INTO savepoints VALUES (3); 234 ROLLBACK TO SAVEPOINT five; 235COMMIT; 236COMMIT; -- should not be in a transaction block 237WARNING: there is no transaction in progress 238SELECT * FROM savepoints; 239 a 240--- 241 1 242 2 243(2 rows) 244 245-- test whole-tree rollback 246BEGIN; 247 SAVEPOINT one; 248 DELETE FROM savepoints WHERE a=1; 249 RELEASE SAVEPOINT one; 250 SAVEPOINT two; 251 DELETE FROM savepoints WHERE a=1; 252 SAVEPOINT three; 253 DELETE FROM savepoints WHERE a=2; 254ROLLBACK; 255COMMIT; -- should not be in a transaction block 256WARNING: there is no transaction in progress 257SELECT * FROM savepoints; 258 a 259--- 260 1 261 2 262(2 rows) 263 264-- test whole-tree commit on an aborted subtransaction 265BEGIN; 266 INSERT INTO savepoints VALUES (4); 267 SAVEPOINT one; 268 INSERT INTO savepoints VALUES (5); 269 SELECT foo; 270ERROR: column "foo" does not exist 271LINE 1: SELECT foo; 272 ^ 273COMMIT; 274SELECT * FROM savepoints; 275 a 276--- 277 1 278 2 279(2 rows) 280 281BEGIN; 282 INSERT INTO savepoints VALUES (6); 283 SAVEPOINT one; 284 INSERT INTO savepoints VALUES (7); 285 RELEASE SAVEPOINT one; 286 INSERT INTO savepoints VALUES (8); 287COMMIT; 288-- rows 6 and 8 should have been created by the same xact 289SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=8; 290 ?column? 291---------- 292 t 293(1 row) 294 295-- rows 6 and 7 should have been created by different xacts 296SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=7; 297 ?column? 298---------- 299 f 300(1 row) 301 302BEGIN; 303 INSERT INTO savepoints VALUES (9); 304 SAVEPOINT one; 305 INSERT INTO savepoints VALUES (10); 306 ROLLBACK TO SAVEPOINT one; 307 INSERT INTO savepoints VALUES (11); 308COMMIT; 309SELECT a FROM savepoints WHERE a in (9, 10, 11); 310 a 311---- 312 9 313 11 314(2 rows) 315 316-- rows 9 and 11 should have been created by different xacts 317SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=9 AND b.a=11; 318 ?column? 319---------- 320 f 321(1 row) 322 323BEGIN; 324 INSERT INTO savepoints VALUES (12); 325 SAVEPOINT one; 326 INSERT INTO savepoints VALUES (13); 327 SAVEPOINT two; 328 INSERT INTO savepoints VALUES (14); 329 ROLLBACK TO SAVEPOINT one; 330 INSERT INTO savepoints VALUES (15); 331 SAVEPOINT two; 332 INSERT INTO savepoints VALUES (16); 333 SAVEPOINT three; 334 INSERT INTO savepoints VALUES (17); 335COMMIT; 336SELECT a FROM savepoints WHERE a BETWEEN 12 AND 17; 337 a 338---- 339 12 340 15 341 16 342 17 343(4 rows) 344 345BEGIN; 346 INSERT INTO savepoints VALUES (18); 347 SAVEPOINT one; 348 INSERT INTO savepoints VALUES (19); 349 SAVEPOINT two; 350 INSERT INTO savepoints VALUES (20); 351 ROLLBACK TO SAVEPOINT one; 352 INSERT INTO savepoints VALUES (21); 353 ROLLBACK TO SAVEPOINT one; 354 INSERT INTO savepoints VALUES (22); 355COMMIT; 356SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22; 357 a 358---- 359 18 360 22 361(2 rows) 362 363DROP TABLE savepoints; 364-- only in a transaction block: 365SAVEPOINT one; 366ERROR: SAVEPOINT can only be used in transaction blocks 367ROLLBACK TO SAVEPOINT one; 368ERROR: ROLLBACK TO SAVEPOINT can only be used in transaction blocks 369RELEASE SAVEPOINT one; 370ERROR: RELEASE SAVEPOINT can only be used in transaction blocks 371-- Only "rollback to" allowed in aborted state 372BEGIN; 373 SAVEPOINT one; 374 SELECT 0/0; 375ERROR: division by zero 376 SAVEPOINT two; -- ignored till the end of ... 377ERROR: current transaction is aborted, commands ignored until end of transaction block 378 RELEASE SAVEPOINT one; -- ignored till the end of ... 379ERROR: current transaction is aborted, commands ignored until end of transaction block 380 ROLLBACK TO SAVEPOINT one; 381 SELECT 1; 382 ?column? 383---------- 384 1 385(1 row) 386 387COMMIT; 388SELECT 1; -- this should work 389 ?column? 390---------- 391 1 392(1 row) 393 394-- check non-transactional behavior of cursors 395BEGIN; 396 DECLARE c CURSOR FOR SELECT unique2 FROM tenk1 ORDER BY unique2; 397 SAVEPOINT one; 398 FETCH 10 FROM c; 399 unique2 400--------- 401 0 402 1 403 2 404 3 405 4 406 5 407 6 408 7 409 8 410 9 411(10 rows) 412 413 ROLLBACK TO SAVEPOINT one; 414 FETCH 10 FROM c; 415 unique2 416--------- 417 10 418 11 419 12 420 13 421 14 422 15 423 16 424 17 425 18 426 19 427(10 rows) 428 429 RELEASE SAVEPOINT one; 430 FETCH 10 FROM c; 431 unique2 432--------- 433 20 434 21 435 22 436 23 437 24 438 25 439 26 440 27 441 28 442 29 443(10 rows) 444 445 CLOSE c; 446 DECLARE c CURSOR FOR SELECT unique2/0 FROM tenk1 ORDER BY unique2; 447 SAVEPOINT two; 448 FETCH 10 FROM c; 449ERROR: division by zero 450 ROLLBACK TO SAVEPOINT two; 451 -- c is now dead to the world ... 452 FETCH 10 FROM c; 453ERROR: portal "c" cannot be run 454 ROLLBACK TO SAVEPOINT two; 455 RELEASE SAVEPOINT two; 456 FETCH 10 FROM c; 457ERROR: portal "c" cannot be run 458COMMIT; 459-- 460-- Check that "stable" functions are really stable. They should not be 461-- able to see the partial results of the calling query. (Ideally we would 462-- also check that they don't see commits of concurrent transactions, but 463-- that's a mite hard to do within the limitations of pg_regress.) 464-- 465select * from xacttest; 466 a | b 467-----+--------- 468 56 | 7.8 469 100 | 99.097 470 0 | 0.09561 471 42 | 324.78 472 777 | 777.777 473(5 rows) 474 475create or replace function max_xacttest() returns smallint language sql as 476'select max(a) from xacttest' stable; 477begin; 478update xacttest set a = max_xacttest() + 10 where a > 0; 479select * from xacttest; 480 a | b 481-----+--------- 482 0 | 0.09561 483 787 | 7.8 484 787 | 99.097 485 787 | 324.78 486 787 | 777.777 487(5 rows) 488 489rollback; 490-- But a volatile function can see the partial results of the calling query 491create or replace function max_xacttest() returns smallint language sql as 492'select max(a) from xacttest' volatile; 493begin; 494update xacttest set a = max_xacttest() + 10 where a > 0; 495select * from xacttest; 496 a | b 497-----+--------- 498 0 | 0.09561 499 787 | 7.8 500 797 | 99.097 501 807 | 324.78 502 817 | 777.777 503(5 rows) 504 505rollback; 506-- Now the same test with plpgsql (since it depends on SPI which is different) 507create or replace function max_xacttest() returns smallint language plpgsql as 508'begin return max(a) from xacttest; end' stable; 509begin; 510update xacttest set a = max_xacttest() + 10 where a > 0; 511select * from xacttest; 512 a | b 513-----+--------- 514 0 | 0.09561 515 787 | 7.8 516 787 | 99.097 517 787 | 324.78 518 787 | 777.777 519(5 rows) 520 521rollback; 522create or replace function max_xacttest() returns smallint language plpgsql as 523'begin return max(a) from xacttest; end' volatile; 524begin; 525update xacttest set a = max_xacttest() + 10 where a > 0; 526select * from xacttest; 527 a | b 528-----+--------- 529 0 | 0.09561 530 787 | 7.8 531 797 | 99.097 532 807 | 324.78 533 817 | 777.777 534(5 rows) 535 536rollback; 537-- test case for problems with dropping an open relation during abort 538BEGIN; 539 savepoint x; 540 CREATE TABLE koju (a INT UNIQUE); 541 INSERT INTO koju VALUES (1); 542 INSERT INTO koju VALUES (1); 543ERROR: duplicate key value violates unique constraint "koju_a_key" 544DETAIL: Key (a)=(1) already exists. 545 rollback to x; 546 CREATE TABLE koju (a INT UNIQUE); 547 INSERT INTO koju VALUES (1); 548 INSERT INTO koju VALUES (1); 549ERROR: duplicate key value violates unique constraint "koju_a_key" 550DETAIL: Key (a)=(1) already exists. 551ROLLBACK; 552DROP TABLE foo; 553DROP TABLE baz; 554DROP TABLE barbaz; 555-- test case for problems with revalidating an open relation during abort 556create function inverse(int) returns float8 as 557$$ 558begin 559 analyze revalidate_bug; 560 return 1::float8/$1; 561exception 562 when division_by_zero then return 0; 563end$$ language plpgsql volatile; 564create table revalidate_bug (c float8 unique); 565insert into revalidate_bug values (1); 566insert into revalidate_bug values (inverse(0)); 567drop table revalidate_bug; 568drop function inverse(int); 569-- verify that cursors created during an aborted subtransaction are 570-- closed, but that we do not rollback the effect of any FETCHs 571-- performed in the aborted subtransaction 572begin; 573savepoint x; 574create table abc (a int); 575insert into abc values (5); 576insert into abc values (10); 577declare foo cursor for select * from abc; 578fetch from foo; 579 a 580--- 581 5 582(1 row) 583 584rollback to x; 585-- should fail 586fetch from foo; 587ERROR: cursor "foo" does not exist 588commit; 589begin; 590create table abc (a int); 591insert into abc values (5); 592insert into abc values (10); 593insert into abc values (15); 594declare foo cursor for select * from abc; 595fetch from foo; 596 a 597--- 598 5 599(1 row) 600 601savepoint x; 602fetch from foo; 603 a 604---- 605 10 606(1 row) 607 608rollback to x; 609fetch from foo; 610 a 611---- 612 15 613(1 row) 614 615abort; 616-- Test for proper cleanup after a failure in a cursor portal 617-- that was created in an outer subtransaction 618CREATE FUNCTION invert(x float8) RETURNS float8 LANGUAGE plpgsql AS 619$$ begin return 1/x; end $$; 620CREATE FUNCTION create_temp_tab() RETURNS text 621LANGUAGE plpgsql AS $$ 622BEGIN 623 CREATE TEMP TABLE new_table (f1 float8); 624 -- case of interest is that we fail while holding an open 625 -- relcache reference to new_table 626 INSERT INTO new_table SELECT invert(0.0); 627 RETURN 'foo'; 628END $$; 629BEGIN; 630DECLARE ok CURSOR FOR SELECT * FROM int8_tbl; 631DECLARE ctt CURSOR FOR SELECT create_temp_tab(); 632FETCH ok; 633 q1 | q2 634-----+----- 635 123 | 456 636(1 row) 637 638SAVEPOINT s1; 639FETCH ok; -- should work 640 q1 | q2 641-----+------------------ 642 123 | 4567890123456789 643(1 row) 644 645FETCH ctt; -- error occurs here 646ERROR: division by zero 647CONTEXT: PL/pgSQL function invert(double precision) line 1 at RETURN 648SQL statement "INSERT INTO new_table SELECT invert(0.0)" 649PL/pgSQL function create_temp_tab() line 6 at SQL statement 650ROLLBACK TO s1; 651FETCH ok; -- should work 652 q1 | q2 653------------------+----- 654 4567890123456789 | 123 655(1 row) 656 657FETCH ctt; -- must be rejected 658ERROR: portal "ctt" cannot be run 659COMMIT; 660DROP FUNCTION create_temp_tab(); 661DROP FUNCTION invert(x float8); 662-- Test for successful cleanup of an aborted transaction at session exit. 663-- THIS MUST BE THE LAST TEST IN THIS FILE. 664begin; 665select 1/0; 666ERROR: division by zero 667rollback to X; 668ERROR: no such savepoint 669-- DO NOT ADD ANYTHING HERE. 670