1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
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;
|
