1-- 2-- Tests for some likely failure cases with combo cmin/cmax mechanism 3-- 4CREATE TEMP TABLE combocidtest (foobar int); 5 6BEGIN; 7 8-- a few dummy ops to push up the CommandId counter 9INSERT INTO combocidtest SELECT 1 LIMIT 0; 10INSERT INTO combocidtest SELECT 1 LIMIT 0; 11INSERT INTO combocidtest SELECT 1 LIMIT 0; 12INSERT INTO combocidtest SELECT 1 LIMIT 0; 13INSERT INTO combocidtest SELECT 1 LIMIT 0; 14INSERT INTO combocidtest SELECT 1 LIMIT 0; 15INSERT INTO combocidtest SELECT 1 LIMIT 0; 16INSERT INTO combocidtest SELECT 1 LIMIT 0; 17INSERT INTO combocidtest SELECT 1 LIMIT 0; 18INSERT INTO combocidtest SELECT 1 LIMIT 0; 19 20INSERT INTO combocidtest VALUES (1); 21INSERT INTO combocidtest VALUES (2); 22 23SELECT ctid,cmin,* FROM combocidtest; 24 25SAVEPOINT s1; 26 27UPDATE combocidtest SET foobar = foobar + 10; 28 29-- here we should see only updated tuples 30SELECT ctid,cmin,* FROM combocidtest; 31 32ROLLBACK TO s1; 33 34-- now we should see old tuples, but with combo CIDs starting at 0 35SELECT ctid,cmin,* FROM combocidtest; 36 37COMMIT; 38 39-- combo data is not there anymore, but should still see tuples 40SELECT ctid,cmin,* FROM combocidtest; 41 42-- Test combo cids with portals 43BEGIN; 44 45INSERT INTO combocidtest VALUES (333); 46 47DECLARE c CURSOR FOR SELECT ctid,cmin,* FROM combocidtest; 48 49DELETE FROM combocidtest; 50 51FETCH ALL FROM c; 52 53ROLLBACK; 54 55SELECT ctid,cmin,* FROM combocidtest; 56 57-- check behavior with locked tuples 58BEGIN; 59 60-- a few dummy ops to push up the CommandId counter 61INSERT INTO combocidtest SELECT 1 LIMIT 0; 62INSERT INTO combocidtest SELECT 1 LIMIT 0; 63INSERT INTO combocidtest SELECT 1 LIMIT 0; 64INSERT INTO combocidtest SELECT 1 LIMIT 0; 65INSERT INTO combocidtest SELECT 1 LIMIT 0; 66INSERT INTO combocidtest SELECT 1 LIMIT 0; 67INSERT INTO combocidtest SELECT 1 LIMIT 0; 68INSERT INTO combocidtest SELECT 1 LIMIT 0; 69INSERT INTO combocidtest SELECT 1 LIMIT 0; 70INSERT INTO combocidtest SELECT 1 LIMIT 0; 71 72INSERT INTO combocidtest VALUES (444); 73 74SELECT ctid,cmin,* FROM combocidtest; 75 76SAVEPOINT s1; 77 78-- this doesn't affect cmin 79SELECT ctid,cmin,* FROM combocidtest FOR UPDATE; 80SELECT ctid,cmin,* FROM combocidtest; 81 82-- but this does 83UPDATE combocidtest SET foobar = foobar + 10; 84 85SELECT ctid,cmin,* FROM combocidtest; 86 87ROLLBACK TO s1; 88 89SELECT ctid,cmin,* FROM combocidtest; 90 91COMMIT; 92 93SELECT ctid,cmin,* FROM combocidtest; 94 95-- test for bug reported in 96-- CABRT9RC81YUf1=jsmWopcKJEro=VoeG2ou6sPwyOUTx_qteRsg@mail.gmail.com 97CREATE TABLE IF NOT EXISTS testcase( 98 id int PRIMARY KEY, 99 balance numeric 100); 101INSERT INTO testcase VALUES (1, 0); 102BEGIN; 103SELECT * FROM testcase WHERE testcase.id = 1 FOR UPDATE; 104UPDATE testcase SET balance = balance + 400 WHERE id=1; 105SAVEPOINT subxact; 106UPDATE testcase SET balance = balance - 100 WHERE id=1; 107ROLLBACK TO SAVEPOINT subxact; 108-- should return one tuple 109SELECT * FROM testcase WHERE id = 1 FOR UPDATE; 110ROLLBACK; 111DROP TABLE testcase; 112