--

-- Tests for (sub)transactions

--

CREATE SCHEMA subtransactions;

SET SESSION search_path = 'subtransactions';

CREATE EXTENSION orioledb;

CREATE TABLE o_subtrans (

id integer NOT NULL,

value text NOT NULL,

PRIMARY KEY (id)

) USING orioledb;

---

-- Tests for relfilenode undo

---

-- TRUNCATE undo

BEGIN;

TRUNCATE o_subtrans;

TRUNCATE o_subtrans;

TRUNCATE o_subtrans;

COMMIT;

SELECT * FROM o_subtrans;

id | value

----+-------

(0 rows)

BEGIN;

TRUNCATE o_subtrans;

TRUNCATE o_subtrans;

TRUNCATE o_subtrans;

ROLLBACK;

SELECT * FROM o_subtrans;

id | value

----+-------

(0 rows)

TRUNCATE o_subtrans;

BEGIN;

INSERT INTO o_subtrans

SELECT i, repeat('abc', i % 5) FROM generate_series(0, 555, 5) i;

SAVEPOINT s1;

UPDATE o_subtrans SET value = value || 'def' WHERE id % 10 = 5;

DELETE FROM o_subtrans WHERE id % 10 = 0;

SAVEPOINT s2;

INSERT INTO o_subtrans

SELECT i, repeat('abc', i % 5) FROM generate_series(0, 555, 10) i;

SAVEPOINT s3;

INSERT INTO o_subtrans

SELECT i, repeat('abc', i % 5) FROM generate_series(3, 555, 10) i;

UPDATE o_subtrans SET value = value || 'qwer';

SAVEPOINT s4;

UPDATE o_subtrans SET value = value || 'asdf';

SAVEPOINT s5;

DELETE FROM o_subtrans WHERE id % 4 = 3;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------

{13,33,53,73,93,113,133,153,173,193,213,233,253,273,293,313,333,353,373,393,413,433,453,473,493,513,533,553} | abcabcabcqwerasdf

{5,25,45,65,85,105,125,145,165,185,205,225,245,265,285,305,325,345,365,385,405,425,445,465,485,505,525,545} | defqwerasdf

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} | qwerasdf

(3 rows)

ROLLBACK TO s5;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------

{3,13,23,33,43,53,63,73,83,93,103,113,123,133,143,153,163,173,183,193,203,213,223,233,243,253,263,273,283,293,303,313,323,333,343,353,363,373,383,393,403,413,423,433,443,453,463,473,483,493,503,513,523,533,543,553} | abcabcabcqwerasdf

{5,15,25,35,45,55,65,75,85,95,105,115,125,135,145,155,165,175,185,195,205,215,225,235,245,255,265,275,285,295,305,315,325,335,345,355,365,375,385,395,405,415,425,435,445,455,465,475,485,495,505,515,525,535,545,555} | defqwerasdf

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} | qwerasdf

(3 rows)

ROLLBACK TO s4;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------

{3,13,23,33,43,53,63,73,83,93,103,113,123,133,143,153,163,173,183,193,203,213,223,233,243,253,263,273,283,293,303,313,323,333,343,353,363,373,383,393,403,413,423,433,443,453,463,473,483,493,503,513,523,533,543,553} | abcabcabcqwer

{5,15,25,35,45,55,65,75,85,95,105,115,125,135,145,155,165,175,185,195,205,215,225,235,245,255,265,275,285,295,305,315,325,335,345,355,365,375,385,395,405,415,425,435,445,455,465,475,485,495,505,515,525,535,545,555} | defqwer

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} | qwer

(3 rows)

ROLLBACK TO s3;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} |

{5,15,25,35,45,55,65,75,85,95,105,115,125,135,145,155,165,175,185,195,205,215,225,235,245,255,265,275,285,295,305,315,325,335,345,355,365,375,385,395,405,415,425,435,445,455,465,475,485,495,505,515,525,535,545,555} | def

(2 rows)

ROLLBACK TO s2;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------

{5,15,25,35,45,55,65,75,85,95,105,115,125,135,145,155,165,175,185,195,205,215,225,235,245,255,265,275,285,295,305,315,325,335,345,355,365,375,385,395,405,415,425,435,445,455,465,475,485,495,505,515,525,535,545,555} | def

(1 row)

ROLLBACK TO s1;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------

{0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100,105,110,115,120,125,130,135,140,145,150,155,160,165,170,175,180,185,190,195,200,205,210,215,220,225,230,235,240,245,250,255,260,265,270,275,280,285,290,295,300,305,310,315,320,325,330,335,340,345,350,355,360,365,370,375,380,385,390,395,400,405,410,415,420,425,430,435,440,445,450,455,460,465,470,475,480,485,490,495,500,505,510,515,520,525,530,535,540,545,550,555} |

(1 row)

COMMIT;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------

{0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100,105,110,115,120,125,130,135,140,145,150,155,160,165,170,175,180,185,190,195,200,205,210,215,220,225,230,235,240,245,250,255,260,265,270,275,280,285,290,295,300,305,310,315,320,325,330,335,340,345,350,355,360,365,370,375,380,385,390,395,400,405,410,415,420,425,430,435,440,445,450,455,460,465,470,475,480,485,490,495,500,505,510,515,520,525,530,535,540,545,550,555} |

(1 row)

TRUNCATE o_subtrans;

BEGIN;

INSERT INTO o_subtrans

SELECT i, repeat('abc', i % 5) FROM generate_series(0, 555, 5) i;

SAVEPOINT s1;

UPDATE o_subtrans SET value = value || 'def' WHERE id % 10 = 5;

DELETE FROM o_subtrans WHERE id % 10 = 0;

SAVEPOINT s2;

INSERT INTO o_subtrans

SELECT i, repeat('abc', i % 5) FROM generate_series(0, 555, 10) i;

SAVEPOINT s3;

INSERT INTO o_subtrans

SELECT i, repeat('abc', i % 5) FROM generate_series(3, 555, 10) i;

UPDATE o_subtrans SET value = value || 'qwer';

SAVEPOINT s4;

UPDATE o_subtrans SET value = value || 'asdf';

SAVEPOINT s5;

DELETE FROM o_subtrans WHERE id % 4 = 3;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------

{13,33,53,73,93,113,133,153,173,193,213,233,253,273,293,313,333,353,373,393,413,433,453,473,493,513,533,553} | abcabcabcqwerasdf

{5,25,45,65,85,105,125,145,165,185,205,225,245,265,285,305,325,345,365,385,405,425,445,465,485,505,525,545} | defqwerasdf

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} | qwerasdf

(3 rows)

ROLLBACK TO s5;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------

{3,13,23,33,43,53,63,73,83,93,103,113,123,133,143,153,163,173,183,193,203,213,223,233,243,253,263,273,283,293,303,313,323,333,343,353,363,373,383,393,403,413,423,433,443,453,463,473,483,493,503,513,523,533,543,553} | abcabcabcqwerasdf

{5,15,25,35,45,55,65,75,85,95,105,115,125,135,145,155,165,175,185,195,205,215,225,235,245,255,265,275,285,295,305,315,325,335,345,355,365,375,385,395,405,415,425,435,445,455,465,475,485,495,505,515,525,535,545,555} | defqwerasdf

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} | qwerasdf

(3 rows)

ROLLBACK TO s4;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------

{3,13,23,33,43,53,63,73,83,93,103,113,123,133,143,153,163,173,183,193,203,213,223,233,243,253,263,273,283,293,303,313,323,333,343,353,363,373,383,393,403,413,423,433,443,453,463,473,483,493,503,513,523,533,543,553} | abcabcabcqwer

{5,15,25,35,45,55,65,75,85,95,105,115,125,135,145,155,165,175,185,195,205,215,225,235,245,255,265,275,285,295,305,315,325,335,345,355,365,375,385,395,405,415,425,435,445,455,465,475,485,495,505,515,525,535,545,555} | defqwer

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} | qwer

(3 rows)

ROLLBACK TO s3;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} |

{5,15,25,35,45,55,65,75,85,95,105,115,125,135,145,155,165,175,185,195,205,215,225,235,245,255,265,275,285,295,305,315,325,335,345,355,365,375,385,395,405,415,425,435,445,455,465,475,485,495,505,515,525,535,545,555} | def

(2 rows)

ROLLBACK TO s2;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------

{5,15,25,35,45,55,65,75,85,95,105,115,125,135,145,155,165,175,185,195,205,215,225,235,245,255,265,275,285,295,305,315,325,335,345,355,365,375,385,395,405,415,425,435,445,455,465,475,485,495,505,515,525,535,545,555} | def

(1 row)

ROLLBACK TO s1;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------

{0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100,105,110,115,120,125,130,135,140,145,150,155,160,165,170,175,180,185,190,195,200,205,210,215,220,225,230,235,240,245,250,255,260,265,270,275,280,285,290,295,300,305,310,315,320,325,330,335,340,345,350,355,360,365,370,375,380,385,390,395,400,405,410,415,420,425,430,435,440,445,450,455,460,465,470,475,480,485,490,495,500,505,510,515,520,525,530,535,540,545,550,555} |

(1 row)

ROLLBACK;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

-----------+-------

(0 rows)

TRUNCATE o_subtrans;

BEGIN;

INSERT INTO o_subtrans

SELECT i, repeat('abc', i % 5) FROM generate_series(0, 555, 5) i;

SAVEPOINT s1;

UPDATE o_subtrans SET value = value || 'def' WHERE id % 10 = 5;

DELETE FROM o_subtrans WHERE id % 10 = 0;

SAVEPOINT s2;

INSERT INTO o_subtrans

SELECT i, repeat('abc', i % 5) FROM generate_series(0, 555, 10) i;

SAVEPOINT s3;

INSERT INTO o_subtrans

SELECT i, repeat('abc', i % 5) FROM generate_series(3, 555, 10) i;

UPDATE o_subtrans SET value = value || 'qwer';

SAVEPOINT s4;

UPDATE o_subtrans SET value = value || 'asdf';

SAVEPOINT s5;

DELETE FROM o_subtrans WHERE id % 4 = 3;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------

{13,33,53,73,93,113,133,153,173,193,213,233,253,273,293,313,333,353,373,393,413,433,453,473,493,513,533,553} | abcabcabcqwerasdf

{5,25,45,65,85,105,125,145,165,185,205,225,245,265,285,305,325,345,365,385,405,425,445,465,485,505,525,545} | defqwerasdf

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} | qwerasdf

(3 rows)

RELEASE s5;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------

{13,33,53,73,93,113,133,153,173,193,213,233,253,273,293,313,333,353,373,393,413,433,453,473,493,513,533,553} | abcabcabcqwerasdf

{5,25,45,65,85,105,125,145,165,185,205,225,245,265,285,305,325,345,365,385,405,425,445,465,485,505,525,545} | defqwerasdf

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} | qwerasdf

(3 rows)

RELEASE s4;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------

{13,33,53,73,93,113,133,153,173,193,213,233,253,273,293,313,333,353,373,393,413,433,453,473,493,513,533,553} | abcabcabcqwerasdf

{5,25,45,65,85,105,125,145,165,185,205,225,245,265,285,305,325,345,365,385,405,425,445,465,485,505,525,545} | defqwerasdf

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} | qwerasdf

(3 rows)

RELEASE s3;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------

{13,33,53,73,93,113,133,153,173,193,213,233,253,273,293,313,333,353,373,393,413,433,453,473,493,513,533,553} | abcabcabcqwerasdf

{5,25,45,65,85,105,125,145,165,185,205,225,245,265,285,305,325,345,365,385,405,425,445,465,485,505,525,545} | defqwerasdf

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} | qwerasdf

(3 rows)

RELEASE s2;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------

{13,33,53,73,93,113,133,153,173,193,213,233,253,273,293,313,333,353,373,393,413,433,453,473,493,513,533,553} | abcabcabcqwerasdf

{5,25,45,65,85,105,125,145,165,185,205,225,245,265,285,305,325,345,365,385,405,425,445,465,485,505,525,545} | defqwerasdf

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} | qwerasdf

(3 rows)

RELEASE s1;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------

{13,33,53,73,93,113,133,153,173,193,213,233,253,273,293,313,333,353,373,393,413,433,453,473,493,513,533,553} | abcabcabcqwerasdf

{5,25,45,65,85,105,125,145,165,185,205,225,245,265,285,305,325,345,365,385,405,425,445,465,485,505,525,545} | defqwerasdf

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} | qwerasdf

(3 rows)

COMMIT;

SELECT array_agg(id), value

FROM o_subtrans GROUP BY value ORDER BY value;

array_agg | value

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------

{13,33,53,73,93,113,133,153,173,193,213,233,253,273,293,313,333,353,373,393,413,433,453,473,493,513,533,553} | abcabcabcqwerasdf

{5,25,45,65,85,105,125,145,165,185,205,225,245,265,285,305,325,345,365,385,405,425,445,465,485,505,525,545} | defqwerasdf

{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550} | qwerasdf

(3 rows)

-- INSERT/DELETE undo

TRUNCATE o_subtrans;

BEGIN;

INSERT INTO o_subtrans

SELECT i, repeat('qwer', i % 3) FROM generate_series(1, 10, 1) AS i;

DELETE FROM o_subtrans WHERE id <= 5;

TRUNCATE o_subtrans;

TRUNCATE o_subtrans;

TRUNCATE o_subtrans;

COMMIT;

SELECT * FROM o_subtrans;

id | value

----+-------

(0 rows)

BEGIN;

INSERT INTO o_subtrans

SELECT i, repeat('qwer', i % 3) FROM generate_series(1, 10, 1) AS i;

DELETE FROM o_subtrans WHERE id <= 5;

TRUNCATE o_subtrans;

TRUNCATE o_subtrans;

TRUNCATE o_subtrans;

ROLLBACK;

SELECT * FROM o_subtrans;

id | value

----+-------

(0 rows)

-- UPDATE undo

INSERT INTO o_subtrans

SELECT i, repeat('qwer', i % 3)FROM generate_series(1, 10, 1) AS i;

BEGIN;

UPDATE o_subtrans SET value = value || 'abc';

TRUNCATE o_subtrans;

TRUNCATE o_subtrans;

TRUNCATE o_subtrans;

COMMIT;

SELECT * FROM o_subtrans;

id | value

----+-------

(0 rows)

INSERT INTO o_subtrans

SELECT i, repeat('qwer', i % 3) FROM generate_series(1, 10, 1) AS i;

BEGIN;

UPDATE o_subtrans SET value = value || 'abc';

TRUNCATE o_subtrans;

TRUNCATE o_subtrans;

TRUNCATE o_subtrans;

ROLLBACK;

SELECT * FROM o_subtrans;

id | value

----+----------

1 | qwer

2 | qwerqwer

3 |

4 | qwer

5 | qwerqwer

6 |

7 | qwer

8 | qwerqwer

9 |

10 | qwer

(10 rows)

TRUNCATE o_subtrans;

BEGIN;

INSERT INTO o_subtrans

SELECT i, repeat('qwer', i % 3) FROM generate_series(1, 5, 1) AS i;

SAVEPOINT s1;

INSERT INTO o_subtrans

SELECT i, repeat('asdf', i % 3) FROM generate_series(6, 10, 1) AS i;

SELECT * FROM o_subtrans;

id | value

----+----------

1 | qwer

2 | qwerqwer

3 |

4 | qwer

5 | qwerqwer

6 |

7 | asdf

8 | asdfasdf

9 |

10 | asdf

(10 rows)

ROLLBACK TO s1;

SELECT * FROM o_subtrans;

id | value

----+----------

1 | qwer

2 | qwerqwer

3 |

4 | qwer

5 | qwerqwer

(5 rows)

COMMIT;

SELECT * FROM o_subtrans;

id | value

----+----------

1 | qwer

2 | qwerqwer

3 |

4 | qwer

5 | qwerqwer

(5 rows)

TRUNCATE o_subtrans;

BEGIN;

INSERT INTO o_subtrans

SELECT i, repeat('qwer', i % 3) FROM generate_series(1, 5, 1) AS i;

SAVEPOINT s1;

INSERT INTO o_subtrans

SELECT i, repeat('asdf', i % 3) FROM generate_series(6, 10, 1) AS i;

SELECT * FROM o_subtrans;

id | value

----+----------

1 | qwer

2 | qwerqwer

3 |

4 | qwer

5 | qwerqwer

6 |

7 | asdf

8 | asdfasdf

9 |

10 | asdf

(10 rows)

ROLLBACK TO s1;

SELECT * FROM o_subtrans;

id | value

----+----------

1 | qwer

2 | qwerqwer

3 |

4 | qwer

5 | qwerqwer

(5 rows)

ROLLBACK;

SELECT * FROM o_subtrans;

id | value

----+-------

(0 rows)

TRUNCATE o_subtrans;

BEGIN;

INSERT INTO o_subtrans

SELECT i, repeat('qwer', i % 3) FROM generate_series(1, 5, 1) AS i;

DELETE FROM o_subtrans WHERE id BETWEEN 1 AND 5;

SAVEPOINT s1;

INSERT INTO o_subtrans

SELECT i, repeat('zxcv', i % 3) FROM generate_series(1, 5, 1) AS i;

UPDATE o_subtrans SET value = repeat('rtyu', id % 4) WHERE id BETWEEN 1 AND 5;

INSERT INTO o_subtrans

SELECT i, repeat('asdf', i % 3) FROM generate_series(6, 10, 1) AS i;

UPDATE o_subtrans SET value = repeat('ghjk', id % 5) WHERE id BETWEEN 1 AND 5;

UPDATE o_subtrans SET value = repeat('bnm,', id % 6) WHERE id BETWEEN 1 AND 5;

SAVEPOINT s2;

DELETE FROM o_subtrans WHERE id BETWEEN 6 AND 10;

SELECT * FROM o_subtrans;

id | value

----+----------------------

1 | bnm,

2 | bnm,bnm,

3 | bnm,bnm,bnm,

4 | bnm,bnm,bnm,bnm,

5 | bnm,bnm,bnm,bnm,bnm,

(5 rows)

ROLLBACK TO s2;

COMMIT;

SELECT * FROM o_subtrans;

id | value

----+----------------------

1 | bnm,

2 | bnm,bnm,

3 | bnm,bnm,bnm,

4 | bnm,bnm,bnm,bnm,

5 | bnm,bnm,bnm,bnm,bnm,

6 |

7 | asdf

8 | asdfasdf

9 |

10 | asdf

(10 rows)

TRUNCATE o_subtrans;

BEGIN;

INSERT INTO o_subtrans

SELECT i, repeat('qwer', i % 3) FROM generate_series(1, 5, 1) AS i;

DELETE FROM o_subtrans WHERE id BETWEEN 1 AND 5;

SAVEPOINT s1;

INSERT INTO o_subtrans

SELECT i, repeat('zxcv', i % 3) FROM generate_series(1, 5, 1) AS i;

UPDATE o_subtrans SET value = repeat('rtyu', id % 4) WHERE id BETWEEN 1 AND 5;

INSERT INTO o_subtrans

SELECT i, repeat('asdf', i % 3) FROM generate_series(6, 10, 1) AS i;

UPDATE o_subtrans SET value = repeat('ghjk', id % 5) WHERE id BETWEEN 1 AND 5;

UPDATE o_subtrans SET value = repeat('bnm,', id % 6) WHERE id BETWEEN 1 AND 5;

SAVEPOINT s2;

DELETE FROM o_subtrans WHERE id BETWEEN 6 AND 10;

SELECT * FROM o_subtrans;

id | value

----+----------------------

1 | bnm,

2 | bnm,bnm,

3 | bnm,bnm,bnm,

4 | bnm,bnm,bnm,bnm,

5 | bnm,bnm,bnm,bnm,bnm,

(5 rows)

ROLLBACK TO s1;

COMMIT;

SELECT * FROM o_subtrans;

id | value

----+-------

(0 rows)

TRUNCATE o_subtrans;

BEGIN;

SAVEPOINT s1;

SET TRANSACTION READ ONLY;

SAVEPOINT s2;

SET TRANSACTION READ WRITE;

ERROR: cannot set transaction read-write mode inside a read-only transaction

ROLLBACK;

TRUNCATE o_subtrans;

BEGIN;

INSERT INTO o_subtrans

SELECT id, repeat('abcdef', id % 7) FROM generate_series(0, 500, 5) id;

SAVEPOINT s1;

DELETE FROM o_subtrans WHERE id % 10 = 0;

SAVEPOINT s2;

INSERT INTO o_subtrans

SELECT id, repeat('abcdef', id % 7) FROM generate_series(0, 500, 10) id;

SAVEPOINT s3;

UPDATE o_subtrans SET value = '12345' || value;

ROLLBACK TO s3;

ROLLBACK TO s2;

COMMIT;

TRUNCATE o_subtrans;

BEGIN;

INSERT INTO o_subtrans

SELECT id, repeat('abcdef', id % 7) FROM generate_series(0, 500, 5) id;

SAVEPOINT s1;

DELETE FROM o_subtrans WHERE id % 10 = 0;

SAVEPOINT s2;

INSERT INTO o_subtrans

SELECT id, repeat('abcdef', id % 7) FROM generate_series(0, 500, 10) id;

DELETE FROM o_subtrans WHERE id % 10 = 0;

UPDATE o_subtrans SET value = '12345' || value;

ROLLBACK TO s2;

SELECT * FROM o_subtrans WHERE id = 10;

id | value

----+-------

(0 rows)

ROLLBACK TO s1;

SELECT * FROM o_subtrans WHERE id = 10;

id | value

----+--------------------

10 | abcdefabcdefabcdef

(1 row)

COMMIT;

TRUNCATE o_subtrans;

BEGIN;

INSERT INTO o_subtrans

SELECT id, repeat('abcdef', id % 7) FROM generate_series(0, 500, 5) id;

SAVEPOINT s1;

DELETE FROM o_subtrans WHERE id % 5 = 0;

COMMIT;

BEGIN;

INSERT INTO o_subtrans

SELECT id, repeat('abcdef', id % 7) FROM generate_series(0, 500, 5) id;

DELETE FROM o_subtrans WHERE id % 5 = 0;

ROLLBACK;

TRUNCATE o_subtrans;

INSERT INTO o_subtrans

SELECT id, repeat('abcdef', id % 7) FROM generate_series(0, 500, 5) id;

DELETE FROM o_subtrans WHERE id % 5 = 0;

BEGIN;

INSERT INTO o_subtrans

SELECT id, repeat('abcdef', id % 7) FROM generate_series(0, 500, 10) id;

DELETE FROM o_subtrans WHERE id % 5 = 0;

SELECT id, repeat('abcdef', id % 7) FROM generate_series(0, 500, 5) id;

id | repeat

-----+--------------------------------------

0 |

5 | abcdefabcdefabcdefabcdefabcdef

10 | abcdefabcdefabcdef

15 | abcdef

20 | abcdefabcdefabcdefabcdefabcdefabcdef

25 | abcdefabcdefabcdefabcdef

30 | abcdefabcdef

35 |

40 | abcdefabcdefabcdefabcdefabcdef

45 | abcdefabcdefabcdef

50 | abcdef

55 | abcdefabcdefabcdefabcdefabcdefabcdef

60 | abcdefabcdefabcdefabcdef

65 | abcdefabcdef

70 |

75 | abcdefabcdefabcdefabcdefabcdef

80 | abcdefabcdefabcdef

85 | abcdef

90 | abcdefabcdefabcdefabcdefabcdefabcdef

95 | abcdefabcdefabcdefabcdef

100 | abcdefabcdef

105 |

110 | abcdefabcdefabcdefabcdefabcdef

115 | abcdefabcdefabcdef

120 | abcdef

125 | abcdefabcdefabcdefabcdefabcdefabcdef

130 | abcdefabcdefabcdefabcdef

135 | abcdefabcdef

140 |

145 | abcdefabcdefabcdefabcdefabcdef

150 | abcdefabcdefabcdef

155 | abcdef

160 | abcdefabcdefabcdefabcdefabcdefabcdef

165 | abcdefabcdefabcdefabcdef

170 | abcdefabcdef

175 |

180 | abcdefabcdefabcdefabcdefabcdef

185 | abcdefabcdefabcdef

190 | abcdef

195 | abcdefabcdefabcdefabcdefabcdefabcdef

200 | abcdefabcdefabcdefabcdef

205 | abcdefabcdef

210 |

215 | abcdefabcdefabcdefabcdefabcdef

220 | abcdefabcdefabcdef

225 | abcdef

230 | abcdefabcdefabcdefabcdefabcdefabcdef

235 | abcdefabcdefabcdefabcdef

240 | abcdefabcdef

245 |

250 | abcdefabcdefabcdefabcdefabcdef

255 | abcdefabcdefabcdef

260 | abcdef

265 | abcdefabcdefabcdefabcdefabcdefabcdef

270 | abcdefabcdefabcdefabcdef

275 | abcdefabcdef

280 |

285 | abcdefabcdefabcdefabcdefabcdef

290 | abcdefabcdefabcdef

295 | abcdef

300 | abcdefabcdefabcdefabcdefabcdefabcdef

305 | abcdefabcdefabcdefabcdef

310 | abcdefabcdef

315 |

320 | abcdefabcdefabcdefabcdefabcdef

325 | abcdefabcdefabcdef

330 | abcdef

335 | abcdefabcdefabcdefabcdefabcdefabcdef

340 | abcdefabcdefabcdefabcdef

345 | abcdefabcdef

350 |

355 | abcdefabcdefabcdefabcdefabcdef

360 | abcdefabcdefabcdef

365 | abcdef

370 | abcdefabcdefabcdefabcdefabcdefabcdef

375 | abcdefabcdefabcdefabcdef

380 | abcdefabcdef

385 |

390 | abcdefabcdefabcdefabcdefabcdef

395 | abcdefabcdefabcdef

400 | abcdef

405 | abcdefabcdefabcdefabcdefabcdefabcdef

410 | abcdefabcdefabcdefabcdef

415 | abcdefabcdef

420 |

425 | abcdefabcdefabcdefabcdefabcdef

430 | abcdefabcdefabcdef

435 | abcdef

440 | abcdefabcdefabcdefabcdefabcdefabcdef

445 | abcdefabcdefabcdefabcdef

450 | abcdefabcdef

455 |

460 | abcdefabcdefabcdefabcdefabcdef

465 | abcdefabcdefabcdef

470 | abcdef

475 | abcdefabcdefabcdefabcdefabcdefabcdef

480 | abcdefabcdefabcdefabcdef

485 | abcdefabcdef

490 |

495 | abcdefabcdefabcdefabcdefabcdef

500 | abcdefabcdefabcdef

(101 rows)

COMMIT;

-- Subtransaction check

TRUNCATE o_subtrans;

BEGIN;

INSERT INTO o_subtrans

SELECT i, repeat('a', i) FROM generate_series(1, 10, 1) AS i;

SAVEPOINT s1;

TRUNCATE o_subtrans;

CREATE TABLE o_subtrans_1

(

id int8 not null,

PRIMARY KEY(id)

) USING orioledb;

SAVEPOINT s2;

CREATE TABLE o_subtrans_2

(

id int8 not null,

PRIMARY KEY(id)

) USING orioledb;

TRUNCATE o_subtrans;

SAVEPOINT s3;

CREATE TABLE o_subtrans_3

(

id int8 not null,

PRIMARY KEY(id)

) USING orioledb;

TRUNCATE o_subtrans;

DROP TABLE o_subtrans;

ROLLBACK TO s3;

ROLLBACK TO s2;

CREATE TABLE o_subtrans_4

(

id int8 not null,

PRIMARY KEY(id)

) USING orioledb;

ROLLBACK;

SELECT * FROM o_subtrans;

id | value

----+-------

(0 rows)

DROP TABLE o_subtrans_1;

ERROR: table "o_subtrans_1" does not exist

DROP TABLE o_subtrans_2;

ERROR: table "o_subtrans_2" does not exist

DROP TABLE o_subtrans_3;

ERROR: table "o_subtrans_3" does not exist

DROP TABLE o_subtrans_4;

ERROR: table "o_subtrans_4" does not exist

-- Test some interesting cases to check that orioledb doesn't break them

BEGIN;

SAVEPOINT one;

ROLLBACK TO one;

RELEASE one;

SAVEPOINT two;

RELEASE two;

SAVEPOINT three;

ROLLBACK TO three;

RELEASE three;

COMMIT;

BEGIN;

SAVEPOINT one;

ROLLBACK TO one;

RELEASE one;

SAVEPOINT two;

RELEASE two;

SAVEPOINT three;

SAVEPOINT four;

SAVEPOINT five;

SAVEPOINT six;

ROLLBACK TO four;

ROLLBACK TO four;

RELEASE four;

ROLLBACK TO three;

RELEASE three;

COMMIT;

BEGIN;

-- Check subtransaction vs cursors

CREATE TABLE o_test_1(

val_1 int

) USING orioledb;

INSERT INTO o_test_1 VALUES (10);

DECLARE abc CURSOR for SELECT * FROM o_test_1;

SAVEPOINT s1;

FETCH FROM abc;

val_1

-------

10

(1 row)

ROLLBACK TO s1;

FETCH FROM abc;

val_1

-------

(0 rows)

ROLLBACK;

DROP EXTENSION orioledb CASCADE;

NOTICE: drop cascades to table o_subtrans

DROP SCHEMA subtransactions CASCADE;

RESET search_path;