--
-- Testing we handle transactions properly
--
CREATE TABLE t(a int, b int) USING columnar;
INSERT INTO t SELECT i, 2 * i FROM generate_series(1, 3) i;
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 1 | 2
 2 | 4
 3 | 6
(3 rows)

-- verify that table rewrites work properly
BEGIN;
ALTER TABLE t ALTER COLUMN b TYPE float4 USING (b + 0.5)::float4;
INSERT INTO t VALUES (4, 8.5);
SELECT * FROM t ORDER BY a;
 a |  b
---------------------------------------------------------------------
 1 | 2.5
 2 | 4.5
 3 | 6.5
 4 | 8.5
(4 rows)

ROLLBACK;
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 1 | 2
 2 | 4
 3 | 6
(3 rows)

-- verify truncate rollback
BEGIN;
TRUNCATE t;
INSERT INTO t VALUES (4, 8);
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 4 | 8
(1 row)

SAVEPOINT s1;
TRUNCATE t;
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
(0 rows)

ROLLBACK TO SAVEPOINT s1;
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 4 | 8
(1 row)

ROLLBACK;
-- verify truncate with unflushed data in upper xacts
BEGIN;
INSERT INTO t VALUES (4, 8);
SAVEPOINT s1;
TRUNCATE t;
ROLLBACK TO SAVEPOINT s1;
COMMIT;
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 1 | 2
 2 | 4
 3 | 6
 4 | 8
(4 rows)

-- verify DROP TABLE rollback
BEGIN;
INSERT INTO t VALUES (5, 10);
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 1 |  2
 2 |  4
 3 |  6
 4 |  8
 5 | 10
(5 rows)

SAVEPOINT s1;
DROP TABLE t;
SELECT * FROM t ORDER BY a;
ERROR:  relation "t" does not exist
ROLLBACK TO SAVEPOINT s1;
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 1 |  2
 2 |  4
 3 |  6
 4 |  8
 5 | 10
(5 rows)

ROLLBACK;
-- verify DROP TABLE with unflushed data in upper xacts
BEGIN;
INSERT INTO t VALUES (5, 10);
SAVEPOINT s1;
DROP TABLE t;
SELECT * FROM t ORDER BY a;
ERROR:  relation "t" does not exist
ROLLBACK TO SAVEPOINT s1;
COMMIT;
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 1 |  2
 2 |  4
 3 |  6
 4 |  8
 5 | 10
(5 rows)

-- verify SELECT when unflushed data in upper transactions errors.
BEGIN;
INSERT INTO t VALUES (6, 12);
SAVEPOINT s1;
SELECT * FROM t;
ERROR:  cannot read from table when there is unflushed data in upper transactions
ROLLBACK;
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 1 |  2
 2 |  4
 3 |  6
 4 |  8
 5 | 10
(5 rows)

--
-- Prepared transactions
--
BEGIN;
INSERT INTO t VALUES (6, 12);
INSERT INTO t VALUES (7, 14);
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 1 |  2
 2 |  4
 3 |  6
 4 |  8
 5 | 10
 6 | 12
 7 | 14
(7 rows)

PREPARE TRANSACTION 'tx01';
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 1 |  2
 2 |  4
 3 |  6
 4 |  8
 5 | 10
(5 rows)

ROLLBACK PREPARED 'tx01';
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 1 |  2
 2 |  4
 3 |  6
 4 |  8
 5 | 10
(5 rows)

BEGIN;
INSERT INTO t VALUES (6, 13);
INSERT INTO t VALUES (7, 15);
PREPARE TRANSACTION 'tx02';
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 1 |  2
 2 |  4
 3 |  6
 4 |  8
 5 | 10
(5 rows)

COMMIT PREPARED 'tx02';
SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 1 |  2
 2 |  4
 3 |  6
 4 |  8
 5 | 10
 6 | 13
 7 | 15
(7 rows)

--
-- Prepared statements
--
PREPARE p1(int) AS INSERT INTO t VALUES (8, $1), (9, $1+2);
EXPLAIN (COSTS OFF) EXECUTE p1(16);
           QUERY PLAN
---------------------------------------------------------------------
 Insert on t
   ->  Values Scan on "*VALUES*"
(2 rows)

EXECUTE p1(16);
EXPLAIN (ANALYZE true, COSTS off, TIMING off, SUMMARY off) EXECUTE p1(20);
                       QUERY PLAN
---------------------------------------------------------------------
 Insert on t (actual rows=0 loops=1)
   ->  Values Scan on "*VALUES*" (actual rows=2 loops=1)
(2 rows)

SELECT * FROM t ORDER BY a;
 a | b
---------------------------------------------------------------------
 1 |  2
 2 |  4
 3 |  6
 4 |  8
 5 | 10
 6 | 13
 7 | 15
 8 | 16
 8 | 20
 9 | 18
 9 | 22
(11 rows)

DROP TABLE t;