combocid.out

来自「postgresql8.3.4源码,开源数据库」· OUT 代码 · 共 143 行

--
-- 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)