

CREATE TABLE numbers
(a INT);

--základní blok příkazů, se kterým budeme pracovat
DELETE FROM numbers;
INSERT INTO numbers (a) SELECT generate_series FROM (SELECT * FROM generate_series(1,1000000,1)) AS q;
DELETE FROM numbers WHERE a % 2 = 0;
DELETE FROM numbers WHERE a % 3 = 0;
DELETE FROM numbers WHERE a % 5 = 0;
DELETE FROM numbers WHERE a % 7 = 0;
DELETE FROM numbers;
SELECT count(*) FROM numbers;


--všechny DELETE v jedné transakci

INSERT INTO numbers (a) SELECT generate_series FROM (SELECT * FROM generate_series(1,1000000,1)) AS q;
BEGIN; -- BEGIN začne transakci
DELETE FROM numbers WHERE a % 2 = 0;
DELETE FROM numbers WHERE a % 3 = 0;
DELETE FROM numbers WHERE a % 5 = 0;
DELETE FROM numbers WHERE a % 7 = 0;
DELETE FROM numbers;
COMMIT; -- COMMIT provede její změny natrvalo
SELECT count(*) FROM numbers;


-- rušení transakce
DELETE FROM numbers;
INSERT INTO numbers (a) SELECT generate_series FROM (SELECT * FROM generate_series(1,1000000,1)) AS q;
BEGIN;
DELETE FROM numbers WHERE a % 2 = 0;
DELETE FROM numbers WHERE a % 3 = 0;
DELETE FROM numbers WHERE a % 5 = 0;
DELETE FROM numbers WHERE a % 7 = 0;
DELETE FROM numbers;
ROLLBACK; --ROLLBACK zruší změny, které transakce provedla (a transakci ukončí)
SELECT count(*) FROM numbers;


--savepointy transakce
DELETE FROM numbers;
INSERT INTO numbers (a) SELECT generate_series FROM (SELECT * FROM generate_series(1,1000000,1)) AS q;
BEGIN;
DELETE FROM numbers WHERE a % 2 = 0;
DELETE FROM numbers WHERE a % 3 = 0;
SAVEPOINT my_savepoint; --SAVEPOINT vytvoří bod, do kterého se lze vrátit
DELETE FROM numbers WHERE a % 5 = 0;
DELETE FROM numbers WHERE a % 7 = 0;
DELETE FROM numbers;
ROLLBACK TO my_savepoint; -- ROLLBACK TO [SAVEPOINT] vráti transakci do daného bodu (a transkace pokračuje)
DELETE FROM numbers WHERE a % 7 = 0;
COMMIT;
SELECT count(*) FROM numbers;



INSERT INTO numbers (a) SELECT generate_series FROM (SELECT * FROM generate_series(1,1000000,1)) AS q;
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- BEGIN může nastavit i úroveň izolace dané transakce (koukněte do dokumentace, mnoho volitelných částí)
DELETE FROM numbers WHERE a % 2 = 0;
DELETE FROM numbers WHERE a % 3 = 0;
DELETE FROM numbers WHERE a % 5 = 0;
DELETE FROM numbers WHERE a % 7 = 0;
DELETE FROM numbers;
COMMIT; 
SELECT count(*) FROM numbers;



INSERT INTO numbers (a) SELECT generate_series FROM (SELECT * FROM generate_series(1,1000000,1)) AS q;
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- to samé ale pomocí SET TRANSACTION
DELETE FROM numbers WHERE a % 2 = 0;
DELETE FROM numbers WHERE a % 3 = 0;
DELETE FROM numbers WHERE a % 5 = 0;
DELETE FROM numbers WHERE a % 7 = 0;
DELETE FROM numbers;
COMMIT;
SELECT count(*) FROM numbers;

-- bokem spuštěno mnoho změn v numbers

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- může dojít k phantom read
SELECT count(*) FROM numbers WHERE a % 2 = 0;
SELECT count(*) FROM numbers WHERE a % 3 = 0;
SELECT count(*) FROM numbers WHERE a % 5 = 0;
SELECT count(*) FROM numbers WHERE a % 7 = 0;
SELECT pg_sleep(1);
SELECT count(*) FROM numbers WHERE a % 2 = 0;
SELECT count(*) FROM numbers WHERE a % 3 = 0;
SELECT count(*) FROM numbers WHERE a % 5 = 0;
SELECT count(*) FROM numbers WHERE a % 7 = 0;
SELECT pg_sleep(1);
SELECT count(*) FROM numbers WHERE a % 2 = 0;
SELECT count(*) FROM numbers WHERE a % 3 = 0;
SELECT count(*) FROM numbers WHERE a % 5 = 0;
SELECT count(*) FROM numbers WHERE a % 7 = 0;
SELECT pg_sleep(1);
SELECT count(*) FROM numbers WHERE a % 2 = 0;
SELECT count(*) FROM numbers WHERE a % 3 = 0;
SELECT count(*) FROM numbers WHERE a % 5 = 0;
SELECT count(*) FROM numbers WHERE a % 7 = 0;
SELECT pg_sleep(1);
SELECT count(*) FROM numbers WHERE a % 2 = 0;
SELECT count(*) FROM numbers WHERE a % 3 = 0;
SELECT count(*) FROM numbers WHERE a % 5 = 0;
SELECT count(*) FROM numbers WHERE a % 7 = 0;
SELECT pg_sleep(1);
SELECT count(*) FROM numbers WHERE a % 2 = 0;
SELECT count(*) FROM numbers WHERE a % 3 = 0;
SELECT count(*) FROM numbers WHERE a % 5 = 0;
SELECT count(*) FROM numbers WHERE a % 7 = 0;
COMMIT;



BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- tady už ne
SELECT count(*) FROM numbers WHERE a % 2 = 0;
SELECT count(*) FROM numbers WHERE a % 3 = 0;
SELECT count(*) FROM numbers WHERE a % 5 = 0;
SELECT count(*) FROM numbers WHERE a % 7 = 0;
SELECT pg_sleep(1);
SELECT count(*) FROM numbers WHERE a % 2 = 0;
SELECT count(*) FROM numbers WHERE a % 3 = 0;
SELECT count(*) FROM numbers WHERE a % 5 = 0;
SELECT count(*) FROM numbers WHERE a % 7 = 0;
SELECT pg_sleep(1);
SELECT count(*) FROM numbers WHERE a % 2 = 0;
SELECT count(*) FROM numbers WHERE a % 3 = 0;
SELECT count(*) FROM numbers WHERE a % 5 = 0;
SELECT count(*) FROM numbers WHERE a % 7 = 0;
SELECT pg_sleep(1);
SELECT count(*) FROM numbers WHERE a % 2 = 0;
SELECT count(*) FROM numbers WHERE a % 3 = 0;
SELECT count(*) FROM numbers WHERE a % 5 = 0;
SELECT count(*) FROM numbers WHERE a % 7 = 0;
SELECT pg_sleep(1);
SELECT count(*) FROM numbers WHERE a % 2 = 0;
SELECT count(*) FROM numbers WHERE a % 3 = 0;
SELECT count(*) FROM numbers WHERE a % 5 = 0;
SELECT count(*) FROM numbers WHERE a % 7 = 0;
SELECT pg_sleep(1);
SELECT count(*) FROM numbers WHERE a % 2 = 0;
SELECT count(*) FROM numbers WHERE a % 3 = 0;
SELECT count(*) FROM numbers WHERE a % 5 = 0;
SELECT count(*) FROM numbers WHERE a % 7 = 0;
COMMIT;





