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-- Test assorted behaviors around the implicit transaction block created 423-- when multiple SQL commands are sent in a single Query message. These 424-- tests rely on the fact that psql will not break SQL commands apart at a 425-- backslash-quoted semicolon, but will send them as one Query. 426 427create temp table i_table (f1 int); 428 429-- psql will show only the last result in a multi-statement Query 430SELECT 1\; SELECT 2\; SELECT 3; 431 432-- this implicitly commits: 433insert into i_table values(1)\; select * from i_table; 434-- 1/0 error will cause rolling back the whole implicit transaction 435insert into i_table values(2)\; select * from i_table\; select 1/0; 436select * from i_table; 437 438rollback; -- we are not in a transaction at this point 439 440-- can use regular begin/commit/rollback within a single Query 441begin\; insert into i_table values(3)\; commit; 442rollback; -- we are not in a transaction at this point 443begin\; insert into i_table values(4)\; rollback; 444rollback; -- we are not in a transaction at this point 445 446-- begin converts implicit transaction into a regular one that 447-- can extend past the end of the Query 448select 1\; begin\; insert into i_table values(5); 449commit; 450select 1\; begin\; insert into i_table values(6); 451rollback; 452 453-- commit in implicit-transaction state commits but issues a warning. 454insert into i_table values(7)\; commit\; insert into i_table values(8)\; select 1/0; 455-- similarly, rollback aborts but issues a warning. 456insert into i_table values(9)\; rollback\; select 2; 457 458select * from i_table; 459 460rollback; -- we are not in a transaction at this point 461 462-- implicit transaction block is still a transaction block, for e.g. VACUUM 463SELECT 1\; VACUUM; 464SELECT 1\; COMMIT\; VACUUM; 465 466-- we disallow savepoint-related commands in implicit-transaction state 467SELECT 1\; SAVEPOINT sp; 468SELECT 1\; COMMIT\; SAVEPOINT sp; 469ROLLBACK TO SAVEPOINT sp\; SELECT 2; 470SELECT 2\; RELEASE SAVEPOINT sp\; SELECT 3; 471 472-- but this is OK, because the BEGIN converts it to a regular xact 473SELECT 1\; BEGIN\; SAVEPOINT sp\; ROLLBACK TO SAVEPOINT sp\; COMMIT; 474 475 476-- Test for successful cleanup of an aborted transaction at session exit. 477-- THIS MUST BE THE LAST TEST IN THIS FILE. 478 479begin; 480select 1/0; 481rollback to X; 482 483-- DO NOT ADD ANYTHING HERE. 484