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