| blob | 51bb3668f3b6b945a2c7ba786df3cf1e1bbc67f1 |
1 --
2 -- TRANSACTIONS
3 --
4 BEGIN;
5 SELECT *
6 INTO TABLE xacttest
7 FROM aggtest;
8 INSERT INTO xacttest (a, b) VALUES (777, 777.777);
9 END;
10 -- should retrieve one value--
11 SELECT a FROM xacttest WHERE a > 100;
12 a
13 -----
14 777
15 (1 row)
17 BEGIN;
18 CREATE TABLE disappear (a int4);
19 DELETE FROM aggtest;
20 -- should be empty
21 SELECT * FROM aggtest;
22 a | b
23 ---+---
24 (0 rows)
26 ABORT;
27 -- should not exist
28 SELECT oid FROM pg_class WHERE relname = 'disappear';
29 oid
30 -----
31 (0 rows)
33 -- should have members again
34 SELECT * 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)
43 -- Read-only tests
44 CREATE TABLE writetest (a int);
45 CREATE TEMPORARY TABLE temptest (a int);
46 SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
47 DROP TABLE writetest; -- fail
48 ERROR: transaction is read-only
49 INSERT INTO writetest VALUES (1); -- fail
50 ERROR: transaction is read-only
51 SELECT * FROM writetest; -- ok
52 a
53 ---
54 (0 rows)
56 DELETE FROM temptest; -- ok
57 UPDATE temptest SET a = 0 FROM writetest WHERE temptest.a = 1 AND writetest.a = temptest.a; -- ok
58 PREPARE test AS UPDATE writetest SET a = 0; -- ok
59 EXECUTE test; -- fail
60 ERROR: transaction is read-only
61 SELECT * FROM writetest, temptest; -- ok
62 a | a
63 ---+---
64 (0 rows)
66 CREATE TABLE test AS SELECT * FROM writetest; -- fail
67 ERROR: transaction is read-only
68 START TRANSACTION READ WRITE;
69 DROP TABLE writetest; -- ok
70 COMMIT;
71 -- Subtransactions, basic tests
72 -- create & drop tables
73 SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE;
74 CREATE TABLE foobar (a int);
75 BEGIN;
76 CREATE TABLE foo (a int);
77 SAVEPOINT one;
78 DROP TABLE foo;
79 CREATE TABLE bar (a int);
80 ROLLBACK TO SAVEPOINT one;
81 RELEASE SAVEPOINT one;
82 SAVEPOINT two;
83 CREATE TABLE baz (a int);
84 RELEASE SAVEPOINT two;
85 drop TABLE foobar;
86 CREATE TABLE barbaz (a int);
87 COMMIT;
88 -- should exist: barbaz, baz, foo
89 SELECT * FROM foo; -- should be empty
90 a
91 ---
92 (0 rows)
94 SELECT * FROM bar; -- shouldn't exist
95 ERROR: relation "bar" does not exist
96 LINE 1: SELECT * FROM bar;
97 ^
98 SELECT * FROM barbaz; -- should be empty
99 a
100 ---
101 (0 rows)
103 SELECT * FROM baz; -- should be empty
104 a
105 ---
106 (0 rows)
108 -- inserts
109 BEGIN;
110 INSERT INTO foo VALUES (1);
111 SAVEPOINT one;
112 INSERT into bar VALUES (1);
113 ERROR: relation "bar" does not exist
114 LINE 1: INSERT into bar VALUES (1);
115 ^
116 ROLLBACK TO one;
117 RELEASE SAVEPOINT one;
118 SAVEPOINT two;
119 INSERT into barbaz VALUES (1);
120 RELEASE two;
121 SAVEPOINT three;
122 SAVEPOINT four;
123 INSERT INTO foo VALUES (2);
124 RELEASE SAVEPOINT four;
125 ROLLBACK TO SAVEPOINT three;
126 RELEASE SAVEPOINT three;
127 INSERT INTO foo VALUES (3);
128 COMMIT;
129 SELECT * FROM foo; -- should have 1 and 3
130 a
131 ---
132 1
133 3
134 (2 rows)
136 SELECT * FROM barbaz; -- should have 1
137 a
138 ---
139 1
140 (1 row)
142 -- test whole-tree commit
143 BEGIN;
144 SAVEPOINT one;
145 SELECT foo;
146 ERROR: column "foo" does not exist
147 LINE 1: SELECT foo;
148 ^
149 ROLLBACK TO SAVEPOINT one;
150 RELEASE SAVEPOINT one;
151 SAVEPOINT two;
152 CREATE TABLE savepoints (a int);
153 SAVEPOINT three;
154 INSERT INTO savepoints VALUES (1);
155 SAVEPOINT four;
156 INSERT INTO savepoints VALUES (2);
157 SAVEPOINT five;
158 INSERT INTO savepoints VALUES (3);
159 ROLLBACK TO SAVEPOINT five;
160 COMMIT;
161 COMMIT; -- should not be in a transaction block
162 WARNING: there is no transaction in progress
163 SELECT * FROM savepoints;
164 a
165 ---
166 1
167 2
168 (2 rows)
170 -- test whole-tree rollback
171 BEGIN;
172 SAVEPOINT one;
173 DELETE FROM savepoints WHERE a=1;
174 RELEASE SAVEPOINT one;
175 SAVEPOINT two;
176 DELETE FROM savepoints WHERE a=1;
177 SAVEPOINT three;
178 DELETE FROM savepoints WHERE a=2;
179 ROLLBACK;
180 COMMIT; -- should not be in a transaction block
181 WARNING: there is no transaction in progress
183 SELECT * FROM savepoints;
184 a
185 ---
186 1
187 2
188 (2 rows)
190 -- test whole-tree commit on an aborted subtransaction
191 BEGIN;
192 INSERT INTO savepoints VALUES (4);
193 SAVEPOINT one;
194 INSERT INTO savepoints VALUES (5);
195 SELECT foo;
196 ERROR: column "foo" does not exist
197 LINE 1: SELECT foo;
198 ^
199 COMMIT;
200 SELECT * FROM savepoints;
201 a
202 ---
203 1
204 2
205 (2 rows)
207 BEGIN;
208 INSERT INTO savepoints VALUES (6);
209 SAVEPOINT one;
210 INSERT INTO savepoints VALUES (7);
211 RELEASE SAVEPOINT one;
212 INSERT INTO savepoints VALUES (8);
213 COMMIT;
214 -- rows 6 and 8 should have been created by the same xact
215 SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=8;
216 ?column?
217 ----------
218 t
219 (1 row)
221 -- rows 6 and 7 should have been created by different xacts
222 SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=7;
223 ?column?
224 ----------
225 f
226 (1 row)
228 BEGIN;
229 INSERT INTO savepoints VALUES (9);
230 SAVEPOINT one;
231 INSERT INTO savepoints VALUES (10);
232 ROLLBACK TO SAVEPOINT one;
233 INSERT INTO savepoints VALUES (11);
234 COMMIT;
235 SELECT a FROM savepoints WHERE a in (9, 10, 11);
236 a
237 ----
238 9
239 11
240 (2 rows)
242 -- rows 9 and 11 should have been created by different xacts
243 SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=9 AND b.a=11;
244 ?column?
245 ----------
246 f
247 (1 row)
249 BEGIN;
250 INSERT INTO savepoints VALUES (12);
251 SAVEPOINT one;
252 INSERT INTO savepoints VALUES (13);
253 SAVEPOINT two;
254 INSERT INTO savepoints VALUES (14);
255 ROLLBACK TO SAVEPOINT one;
256 INSERT INTO savepoints VALUES (15);
257 SAVEPOINT two;
258 INSERT INTO savepoints VALUES (16);
259 SAVEPOINT three;
260 INSERT INTO savepoints VALUES (17);
261 COMMIT;
262 SELECT a FROM savepoints WHERE a BETWEEN 12 AND 17;
263 a
264 ----
265 12
266 15
267 16
268 17
269 (4 rows)
271 BEGIN;
272 INSERT INTO savepoints VALUES (18);
273 SAVEPOINT one;
274 INSERT INTO savepoints VALUES (19);
275 SAVEPOINT two;
276 INSERT INTO savepoints VALUES (20);
277 ROLLBACK TO SAVEPOINT one;
278 INSERT INTO savepoints VALUES (21);
279 ROLLBACK TO SAVEPOINT one;
280 INSERT INTO savepoints VALUES (22);
281 COMMIT;
282 SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
283 a
284 ----
285 18
286 22
287 (2 rows)
289 DROP TABLE savepoints;
290 -- only in a transaction block:
291 SAVEPOINT one;
292 ERROR: SAVEPOINT can only be used in transaction blocks
293 ROLLBACK TO SAVEPOINT one;
294 ERROR: ROLLBACK TO SAVEPOINT can only be used in transaction blocks
295 RELEASE SAVEPOINT one;
296 ERROR: RELEASE SAVEPOINT can only be used in transaction blocks
297 -- Only "rollback to" allowed in aborted state
298 BEGIN;
299 SAVEPOINT one;
300 SELECT 0/0;
301 ERROR: division by zero
302 SAVEPOINT two; -- ignored till the end of ...
303 ERROR: current transaction is aborted, commands ignored until end of transaction block
304 RELEASE SAVEPOINT one; -- ignored till the end of ...
305 ERROR: current transaction is aborted, commands ignored until end of transaction block
306 ROLLBACK TO SAVEPOINT one;
307 SELECT 1;
308 ?column?
309 ----------
310 1
311 (1 row)
313 COMMIT;
314 SELECT 1; -- this should work
315 ?column?
316 ----------
317 1
318 (1 row)
320 -- check non-transactional behavior of cursors
321 BEGIN;
322 DECLARE c CURSOR FOR SELECT unique2 FROM tenk1 ORDER BY unique2;
323 SAVEPOINT one;
324 FETCH 10 FROM c;
325 unique2
326 ---------
327 0
328 1
329 2
330 3
331 4
332 5
333 6
334 7
335 8
336 9
337 (10 rows)
339 ROLLBACK TO SAVEPOINT one;
340 FETCH 10 FROM c;
341 unique2
342 ---------
343 10
344 11
345 12
346 13
347 14
348 15
349 16
350 17
351 18
352 19
353 (10 rows)
355 RELEASE SAVEPOINT one;
356 FETCH 10 FROM c;
357 unique2
358 ---------
359 20
360 21
361 22
362 23
363 24
364 25
365 26
366 27
367 28
368 29
369 (10 rows)
371 CLOSE c;
372 DECLARE c CURSOR FOR SELECT unique2/0 FROM tenk1 ORDER BY unique2;
373 SAVEPOINT two;
374 FETCH 10 FROM c;
375 ERROR: division by zero
376 ROLLBACK TO SAVEPOINT two;
377 -- c is now dead to the world ...
378 FETCH 10 FROM c;
379 ERROR: portal "c" cannot be run
380 ROLLBACK TO SAVEPOINT two;
381 RELEASE SAVEPOINT two;
382 FETCH 10 FROM c;
383 ERROR: portal "c" cannot be run
384 COMMIT;
385 --
386 -- Check that "stable" functions are really stable. They should not be
387 -- able to see the partial results of the calling query. (Ideally we would
388 -- also check that they don't see commits of concurrent transactions, but
389 -- that's a mite hard to do within the limitations of pg_regress.)
390 --
391 select * from xacttest;
392 a | b
393 -----+---------
394 56 | 7.8
395 100 | 99.097
396 0 | 0.09561
397 42 | 324.78
398 777 | 777.777
399 (5 rows)
401 create or replace function max_xacttest() returns smallint language sql as
402 'select max(a) from xacttest' stable;
403 begin;
404 update xacttest set a = max_xacttest() + 10 where a > 0;
405 select * from xacttest;
406 a | b
407 -----+---------
408 0 | 0.09561
409 787 | 7.8
410 787 | 99.097
411 787 | 324.78
412 787 | 777.777
413 (5 rows)
415 rollback;
416 -- But a volatile function can see the partial results of the calling query
417 create or replace function max_xacttest() returns smallint language sql as
418 'select max(a) from xacttest' volatile;
419 begin;
420 update xacttest set a = max_xacttest() + 10 where a > 0;
421 select * from xacttest;
422 a | b
423 -----+---------
424 0 | 0.09561
425 787 | 7.8
426 797 | 99.097
427 807 | 324.78
428 817 | 777.777
429 (5 rows)
431 rollback;
432 -- Now the same test with plpgsql (since it depends on SPI which is different)
433 create or replace function max_xacttest() returns smallint language plpgsql as
434 'begin return max(a) from xacttest; end' stable;
435 begin;
436 update xacttest set a = max_xacttest() + 10 where a > 0;
437 select * from xacttest;
438 a | b
439 -----+---------
440 0 | 0.09561
441 787 | 7.8
442 787 | 99.097
443 787 | 324.78
444 787 | 777.777
445 (5 rows)
447 rollback;
448 create or replace function max_xacttest() returns smallint language plpgsql as
449 'begin return max(a) from xacttest; end' volatile;
450 begin;
451 update xacttest set a = max_xacttest() + 10 where a > 0;
452 select * from xacttest;
453 a | b
454 -----+---------
455 0 | 0.09561
456 787 | 7.8
457 797 | 99.097
458 807 | 324.78
459 817 | 777.777
460 (5 rows)
462 rollback;
463 -- test case for problems with dropping an open relation during abort
464 BEGIN;
465 savepoint x;
466 CREATE TABLE koju (a INT UNIQUE);
467 NOTICE: CREATE TABLE / UNIQUE will create implicit index "koju_a_key" for table "koju"
468 INSERT INTO koju VALUES (1);
469 INSERT INTO koju VALUES (1);
470 ERROR: duplicate key value violates unique constraint "koju_a_key"
471 rollback to x;
472 CREATE TABLE koju (a INT UNIQUE);
473 NOTICE: CREATE TABLE / UNIQUE will create implicit index "koju_a_key" for table "koju"
474 INSERT INTO koju VALUES (1);
475 INSERT INTO koju VALUES (1);
476 ERROR: duplicate key value violates unique constraint "koju_a_key"
477 ROLLBACK;
478 DROP TABLE foo;
479 DROP TABLE baz;
480 DROP TABLE barbaz;
481 -- verify that cursors created during an aborted subtransaction are
482 -- closed, but that we do not rollback the effect of any FETCHs
483 -- performed in the aborted subtransaction
484 begin;
485 savepoint x;
486 create table abc (a int);
487 insert into abc values (5);
488 insert into abc values (10);
489 declare foo cursor for select * from abc;
490 fetch from foo;
491 a
492 ---
493 5
494 (1 row)
496 rollback to x;
497 -- should fail
498 fetch from foo;
499 ERROR: cursor "foo" does not exist
500 commit;
501 begin;
502 create table abc (a int);
503 insert into abc values (5);
504 insert into abc values (10);
505 insert into abc values (15);
506 declare foo cursor for select * from abc;
507 fetch from foo;
508 a
509 ---
510 5
511 (1 row)
513 savepoint x;
514 fetch from foo;
515 a
516 ----
517 10
518 (1 row)
520 rollback to x;
521 fetch from foo;
522 a
523 ----
524 15
525 (1 row)
527 abort;
528 -- tests for the "tid" type
529 SELECT '(3, 3)'::tid = '(3, 4)'::tid;
530 ?column?
531 ----------
532 f
533 (1 row)
535 SELECT '(3, 3)'::tid = '(3, 3)'::tid;
536 ?column?
537 ----------
538 t
539 (1 row)
541 SELECT '(3, 3)'::tid <> '(3, 3)'::tid;
542 ?column?
543 ----------
544 f
545 (1 row)
547 SELECT '(3, 3)'::tid <> '(3, 4)'::tid;
548 ?column?
549 ----------
550 t
551 (1 row)