drop table if exists aggtest;
drop table if exists xacttest;
drop table if exists disappear;
drop table if exists writetest;
drop table if exists tenk1;
drop table if exists int8_tbl;

create table aggtest (a int2, b float4) with (storage_type=ustore);
COPY aggtest FROM '@abs_srcdir@/data/agg.data';

CREATE TABLE tenk1 (
        unique1         int4,
        unique2         int4,
        two                     int4,
        four            int4,
        ten                     int4,
        twenty          int4,
        hundred         int4,
        thousand        int4,
        twothousand     int4,
        fivethous       int4,
        tenthous        int4,
        odd                     int4,
        even            int4,
        stringu1        name,
        stringu2        name,
        string4         name
) with(storage_type=ustore);

COPY tenk1 FROM '@abs_srcdir@/data/tenk.data';

CREATE TABLE INT8_TBL(q1 int8, q2 int8) with (storage_type=ustore);

INSERT INTO INT8_TBL VALUES('  123   ','  456');
INSERT INTO INT8_TBL VALUES('123   ','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','123');
INSERT INTO INT8_TBL VALUES(+4567890123456789,'4567890123456789');
INSERT INTO INT8_TBL VALUES('+4567890123456789','-4567890123456789');

-- bad inputs
INSERT INTO INT8_TBL(q1) VALUES ('      ');
INSERT INTO INT8_TBL(q1) VALUES ('xxx');
INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485');
INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934');
INSERT INTO INT8_TBL(q1) VALUES ('- 123');
INSERT INTO INT8_TBL(q1) VALUES ('  345     5');
INSERT INTO INT8_TBL(q1) VALUES ('');

-- TRANSACTIONS

START TRANSACTION;

SELECT *
   INTO TABLE xacttest
   FROM aggtest;

INSERT INTO xacttest (a, b) VALUES (777, 777.777);

END;

-- should retrieve one value--
SELECT a FROM xacttest WHERE a > 100;


START TRANSACTION;

CREATE TABLE disappear (a int4) with (storage_type=ustore);

DELETE FROM aggtest;

-- should be empty
SELECT * FROM aggtest;

ABORT;

-- should not exist
SELECT oid FROM pg_class WHERE relname = 'disappear';

-- should have members again 
SELECT * FROM aggtest order by a, b;


-- Read-only tests

CREATE TABLE writetest (a int) with (storage_type=ustore);
CREATE TEMPORARY TABLE temptest (a int);

START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE; -- ok
SELECT * FROM writetest; -- ok
SET TRANSACTION READ WRITE; --fail
COMMIT;

START TRANSACTION;
SET TRANSACTION READ ONLY; -- ok
SET TRANSACTION READ WRITE; -- ok
SET TRANSACTION READ ONLY; -- ok
SELECT * FROM writetest; -- ok
SAVEPOINT x;
SET TRANSACTION READ ONLY; -- ok
SELECT * FROM writetest; -- ok
SET TRANSACTION READ ONLY; -- ok
SET TRANSACTION READ WRITE; --fail
COMMIT;

START TRANSACTION;
SET TRANSACTION READ WRITE; -- ok
SAVEPOINT x;
SET TRANSACTION READ WRITE; -- ok
SET TRANSACTION READ ONLY; -- ok
SELECT * FROM writetest; -- ok
SET TRANSACTION READ ONLY; -- ok
SET TRANSACTION READ WRITE; --fail
COMMIT;

START TRANSACTION;
SET TRANSACTION READ WRITE; -- ok
SAVEPOINT x;
SET TRANSACTION READ ONLY; -- ok
SELECT * FROM writetest; -- ok
ROLLBACK TO SAVEPOINT x;
SHOW transaction_read_only;  -- off
SAVEPOINT y;
SET TRANSACTION READ ONLY; -- ok
SELECT * FROM writetest; -- ok
RELEASE SAVEPOINT y;
SHOW transaction_read_only;  -- off
COMMIT;

SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

DROP TABLE writetest; -- fail
INSERT INTO writetest VALUES (1); -- fail
SELECT * FROM writetest; -- ok
DELETE FROM temptest; -- ok
UPDATE temptest SET a = 0 FROM writetest WHERE temptest.a = 1 AND writetest.a = temptest.a; -- ok
PREPARE test AS UPDATE writetest SET a = 0; -- ok
EXECUTE test; -- fail
SELECT * FROM writetest, temptest; -- ok
CREATE TABLE test AS SELECT * FROM writetest; -- fail

START TRANSACTION READ WRITE;
DROP TABLE writetest; -- ok
COMMIT;

-- Subtransactions, basic tests
-- create & drop tables
SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE;
CREATE TABLE foobar (a int) with (storage_type=ustore);
START TRANSACTION;
	CREATE TABLE foo (a int) with (storage_type=ustore); 
	SAVEPOINT one;
		DROP TABLE foo;
		CREATE TABLE bar (a int) with (storage_type=ustore);
	ROLLBACK TO SAVEPOINT one;
	RELEASE SAVEPOINT one;
	SAVEPOINT two;
		CREATE TABLE baz (a int) with (storage_type=ustore);
	RELEASE SAVEPOINT two;
	drop TABLE foobar;
	CREATE TABLE barbaz (a int) with (storage_type=ustore);
COMMIT;
-- should exist: barbaz, baz, foo
SELECT * FROM foo;		-- should be empty
SELECT * FROM bar;		-- shouldn't exist
SELECT * FROM barbaz;	-- should be empty
SELECT * FROM baz;		-- should be empty

-- inserts
START TRANSACTION;
	INSERT INTO foo VALUES (1);
	SAVEPOINT one;
		INSERT into bar VALUES (1);
	ROLLBACK TO one;
	RELEASE SAVEPOINT one;
	SAVEPOINT two;
		INSERT into barbaz VALUES (1);
	RELEASE two;
	SAVEPOINT three;
		SAVEPOINT four;
			INSERT INTO foo VALUES (2);
		RELEASE SAVEPOINT four;
	ROLLBACK TO SAVEPOINT three;
	RELEASE SAVEPOINT three;
	INSERT INTO foo VALUES (3);
COMMIT;
SELECT * FROM foo ORDER BY a;		-- should have 1 and 3
SELECT * FROM barbaz ORDER BY a;	-- should have 1

-- test whole-tree commit
START TRANSACTION;
	SAVEPOINT one;
		SELECT foo;
	ROLLBACK TO SAVEPOINT one;
	RELEASE SAVEPOINT one;
	SAVEPOINT two;
		CREATE TABLE savepoints (a int) with (storage_type=ustore);
		SAVEPOINT three;
			INSERT INTO savepoints VALUES (1);
			SAVEPOINT four;
				INSERT INTO savepoints VALUES (2);
				SAVEPOINT five;
					INSERT INTO savepoints VALUES (3);
				ROLLBACK TO SAVEPOINT five;
COMMIT;
COMMIT;		-- should not be in a transaction block
SELECT * FROM savepoints ORDER BY 1;

-- test whole-tree rollback
START TRANSACTION;
	SAVEPOINT one;
		DELETE FROM savepoints WHERE a=1;
	RELEASE SAVEPOINT one;
	SAVEPOINT two;
		DELETE FROM savepoints WHERE a=1;
		SAVEPOINT three;
			DELETE FROM savepoints WHERE a=2;
ROLLBACK;
COMMIT;		-- should not be in a transaction block
		
SELECT * FROM savepoints ORDER BY 1;

-- test whole-tree commit on an aborted subtransaction
START TRANSACTION;
	INSERT INTO savepoints VALUES (4);
	SAVEPOINT one;
		INSERT INTO savepoints VALUES (5);
		SELECT foo;
COMMIT;
SELECT * FROM savepoints ORDER BY a;

START TRANSACTION;
	INSERT INTO savepoints VALUES (6);
	SAVEPOINT one;
		INSERT INTO savepoints VALUES (7);
	RELEASE SAVEPOINT one;
	INSERT INTO savepoints VALUES (8);
COMMIT;

START TRANSACTION;
	INSERT INTO savepoints VALUES (9);
	SAVEPOINT one;
		INSERT INTO savepoints VALUES (10);
	ROLLBACK TO SAVEPOINT one;
		INSERT INTO savepoints VALUES (11);
COMMIT;
SELECT a FROM savepoints WHERE a in (9, 10, 11) ORDER BY a;

START TRANSACTION;
	INSERT INTO savepoints VALUES (12);
	SAVEPOINT one;
		INSERT INTO savepoints VALUES (13);
		SAVEPOINT two;
			INSERT INTO savepoints VALUES (14);
	ROLLBACK TO SAVEPOINT one;
		INSERT INTO savepoints VALUES (15);
		SAVEPOINT two;
			INSERT INTO savepoints VALUES (16);
			SAVEPOINT three;
				INSERT INTO savepoints VALUES (17);
COMMIT;
SELECT a FROM savepoints WHERE a BETWEEN 12 AND 17 ORDER BY a;

START TRANSACTION;
	INSERT INTO savepoints VALUES (18);
	SAVEPOINT one;
		INSERT INTO savepoints VALUES (19);
		SAVEPOINT two;
			INSERT INTO savepoints VALUES (20);
	ROLLBACK TO SAVEPOINT one;
		INSERT INTO savepoints VALUES (21);
	ROLLBACK TO SAVEPOINT one;
		INSERT INTO savepoints VALUES (22);
COMMIT;
SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22 ORDER BY a;

DROP TABLE savepoints;

-- only in a transaction block:
SAVEPOINT one;
ROLLBACK TO SAVEPOINT one;
RELEASE SAVEPOINT one;

-- Only "rollback to" allowed in aborted state
START TRANSACTION;
  SAVEPOINT one;
  SELECT 0/0;
  SAVEPOINT two;    -- ignored till the end of ...
  RELEASE SAVEPOINT one;      -- ignored till the end of ...
  ROLLBACK TO SAVEPOINT one;
  SELECT 1;
COMMIT;
SELECT 1;			-- this should work

-- check non-transactional behavior of cursors
START TRANSACTION;
	CURSOR c FOR SELECT unique2 FROM tenk1 ORDER BY unique2;
	SAVEPOINT one;
		FETCH 10 FROM c;
	ROLLBACK TO SAVEPOINT one;
		FETCH 10 FROM c;
	RELEASE SAVEPOINT one;
	FETCH 10 FROM c;
	CLOSE c;
	CURSOR c FOR SELECT unique2/0 FROM tenk1 ORDER BY unique2;
	SAVEPOINT two;
		FETCH 10 FROM c;
	ROLLBACK TO SAVEPOINT two;
	-- c is now dead to the world ...
		FETCH 10 FROM c;
	ROLLBACK TO SAVEPOINT two;
	RELEASE SAVEPOINT two;
	FETCH 10 FROM c;
COMMIT;

--
-- Check that "stable" functions are really stable.  They should not be
-- able to see the partial results of the calling query.  (Ideally we would
-- also check that they don't see commits of concurrent transactions, but
-- that's a mite hard to do within the limitations of pg_regress.)
--
select * from xacttest order by a, b;

create or replace function max_xacttest() returns smallint language sql as
'select max(a) from xacttest' stable;

start transaction;
update xacttest set a = max_xacttest() + 10 where a > 0;
select * from xacttest order by a, b;
rollback;

-- But a volatile function can see the partial results of the calling query
create or replace function max_xacttest() returns smallint language sql as
'select max(a) from xacttest' volatile;

start transaction;
update xacttest set a = max_xacttest() + 10 where a > 0;
select * from xacttest order by a, b;
rollback;

-- Now the same test with plpgsql (since it depends on SPI which is different)
create or replace function max_xacttest() returns smallint language plpgsql as
'begin return max(a) from xacttest; end' stable;

start transaction;
update xacttest set a = max_xacttest() + 10 where a > 0;
select * from xacttest order by a, b;
rollback;

create or replace function max_xacttest() returns smallint language plpgsql as
'begin return max(a) from xacttest; end' volatile;

start transaction;
update xacttest set a = max_xacttest() + 10 where a > 0;
select * from xacttest order by a, b;
rollback;


-- test case for problems with dropping an open relation during abort
START TRANSACTION;
	savepoint x;
		CREATE TABLE koju (a INT UNIQUE) with (storage_type=ustore);
		INSERT INTO koju VALUES (1);
		INSERT INTO koju VALUES (1);
	rollback to x;

	CREATE TABLE koju (a INT UNIQUE) with (storage_type=ustore);
	INSERT INTO koju VALUES (1);
	INSERT INTO koju VALUES (1);
ROLLBACK;

DROP TABLE foo;
DROP TABLE baz;
DROP TABLE barbaz;

-- verify that cursors created during an aborted subtransaction are
-- closed, but that we do not rollback the effect of any FETCHs
-- performed in the aborted subtransaction
start transaction;

savepoint x;
create table abc (a int);
insert into abc values (5);
insert into abc values (10);
cursor foo for select * from abc order by a;
fetch from foo;
rollback to x;

-- should fail
fetch from foo;
commit;

start transaction;

create table abc (a int);
insert into abc values (5);
insert into abc values (10);
insert into abc values (15);
cursor foo for select * from abc order by a;

fetch from foo;

savepoint x;
fetch from foo;
rollback to x;

fetch from foo;

abort;

-- Test for prepared xacts
create table prepared_xact_test(a int);
start transaction;
insert into prepared_xact_test values (1);
prepare transaction 'pxact1';
commit;
select * from prepared_xact_test;
select gid from pg_prepared_xacts;
commit prepared 'pxact1';
select * from prepared_xact_test;
select * from pg_prepared_xacts;

begin;
insert into prepared_xact_test values (2);
prepare transaction 'pxact2';
select gid from pg_prepared_xacts;
rollback prepared 'pxact2';
select * from prepared_xact_test;
select * from pg_prepared_xacts;

drop table prepared_xact_test;

-- Test for proper cleanup after a failure in a cursor portal
-- that was created in an outer subtransaction
CREATE FUNCTION invert(x float8) RETURNS float8 LANGUAGE plpgsql AS
$$ begin return 1/x; end $$;

CREATE FUNCTION create_temp_tab() RETURNS text
LANGUAGE plpgsql AS $$
BEGIN
  CREATE TEMP TABLE new_table (f1 float8);
  -- case of interest is that we fail while holding an open
  -- relcache reference to new_table
  INSERT INTO new_table SELECT invert(0.0);
  RETURN 'foo';
END $$;

BEGIN;
DECLARE ok CURSOR FOR SELECT * FROM int8_tbl order by 1,2;
DECLARE ctt CURSOR FOR SELECT create_temp_tab();
FETCH ok;
SAVEPOINT s1;
FETCH ok;  -- should work
FETCH ctt; -- error occurs here
ROLLBACK TO s1;
FETCH ok;  -- should work
FETCH ctt; -- must be rejected
COMMIT;

DROP FUNCTION create_temp_tab();
DROP FUNCTION invert(x float8);

-- Test for successful cleanup of an aborted transaction at session exit.
-- THIS MUST BE THE LAST TEST IN THIS FILE.

start transaction;
select 1/0;
rollback to X;
rollback;

-- DO NOT ADD ANYTHING HERE.