summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/combocid.out
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress/expected/combocid.out')
-rw-r--r--src/test/regress/expected/combocid.out169
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..17eb94a
--- /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;