diff options
Diffstat (limited to '')
| -rw-r--r-- | src/test/regress/expected/combocid.out | 169 |
1 files changed, 169 insertions, 0 deletions
diff --git a/src/test/regress/expected/combocid.out b/src/test/regress/expected/combocid.out new file mode 100644 index 0000000..2bf080b --- /dev/null +++ b/src/test/regress/expected/combocid.out @@ -0,0 +1,169 @@ +-- +-- Tests for some likely failure cases with combo cmin/cmax mechanism +-- +CREATE TEMP TABLE combocidtest (foobar int); +BEGIN; +-- a few dummy ops to push up the CommandId counter +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest VALUES (1); +INSERT INTO combocidtest VALUES (2); +SELECT ctid,cmin,* FROM combocidtest; + ctid | cmin | foobar +-------+------+-------- + (0,1) | 10 | 1 + (0,2) | 11 | 2 +(2 rows) + +SAVEPOINT s1; +UPDATE combocidtest SET foobar = foobar + 10; +-- here we should see only updated tuples +SELECT ctid,cmin,* FROM combocidtest; + ctid | cmin | foobar +-------+------+-------- + (0,3) | 12 | 11 + (0,4) | 12 | 12 +(2 rows) + +ROLLBACK TO s1; +-- now we should see old tuples, but with combo CIDs starting at 0 +SELECT ctid,cmin,* FROM combocidtest; + ctid | cmin | foobar +-------+------+-------- + (0,1) | 0 | 1 + (0,2) | 1 | 2 +(2 rows) + +COMMIT; +-- combo data is not there anymore, but should still see tuples +SELECT ctid,cmin,* FROM combocidtest; + ctid | cmin | foobar +-------+------+-------- + (0,1) | 0 | 1 + (0,2) | 1 | 2 +(2 rows) + +-- Test combo CIDs with portals +BEGIN; +INSERT INTO combocidtest VALUES (333); +DECLARE c CURSOR FOR SELECT ctid,cmin,* FROM combocidtest; +DELETE FROM combocidtest; +FETCH ALL FROM c; + ctid | cmin | foobar +-------+------+-------- + (0,1) | 1 | 1 + (0,2) | 1 | 2 + (0,5) | 0 | 333 +(3 rows) + +ROLLBACK; +SELECT ctid,cmin,* FROM combocidtest; + ctid | cmin | foobar +-------+------+-------- + (0,1) | 1 | 1 + (0,2) | 1 | 2 +(2 rows) + +-- check behavior with locked tuples +BEGIN; +-- a few dummy ops to push up the CommandId counter +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest SELECT 1 LIMIT 0; +INSERT INTO combocidtest VALUES (444); +SELECT ctid,cmin,* FROM combocidtest; + ctid | cmin | foobar +-------+------+-------- + (0,1) | 1 | 1 + (0,2) | 1 | 2 + (0,6) | 10 | 444 +(3 rows) + +SAVEPOINT s1; +-- this doesn't affect cmin +SELECT ctid,cmin,* FROM combocidtest FOR UPDATE; + ctid | cmin | foobar +-------+------+-------- + (0,1) | 1 | 1 + (0,2) | 1 | 2 + (0,6) | 10 | 444 +(3 rows) + +SELECT ctid,cmin,* FROM combocidtest; + ctid | cmin | foobar +-------+------+-------- + (0,1) | 1 | 1 + (0,2) | 1 | 2 + (0,6) | 10 | 444 +(3 rows) + +-- but this does +UPDATE combocidtest SET foobar = foobar + 10; +SELECT ctid,cmin,* FROM combocidtest; + ctid | cmin | foobar +-------+------+-------- + (0,7) | 12 | 11 + (0,8) | 12 | 12 + (0,9) | 12 | 454 +(3 rows) + +ROLLBACK TO s1; +SELECT ctid,cmin,* FROM combocidtest; + ctid | cmin | foobar +-------+------+-------- + (0,1) | 12 | 1 + (0,2) | 12 | 2 + (0,6) | 0 | 444 +(3 rows) + +COMMIT; +SELECT ctid,cmin,* FROM combocidtest; + ctid | cmin | foobar +-------+------+-------- + (0,1) | 12 | 1 + (0,2) | 12 | 2 + (0,6) | 0 | 444 +(3 rows) + +-- test for bug reported in +-- CABRT9RC81YUf1=jsmWopcKJEro=VoeG2ou6sPwyOUTx_qteRsg@mail.gmail.com +CREATE TABLE IF NOT EXISTS testcase( + id int PRIMARY KEY, + balance numeric +); +INSERT INTO testcase VALUES (1, 0); +BEGIN; +SELECT * FROM testcase WHERE testcase.id = 1 FOR UPDATE; + id | balance +----+--------- + 1 | 0 +(1 row) + +UPDATE testcase SET balance = balance + 400 WHERE id=1; +SAVEPOINT subxact; +UPDATE testcase SET balance = balance - 100 WHERE id=1; +ROLLBACK TO SAVEPOINT subxact; +-- should return one tuple +SELECT * FROM testcase WHERE id = 1 FOR UPDATE; + id | balance +----+--------- + 1 | 400 +(1 row) + +ROLLBACK; +DROP TABLE testcase; |