-- connect to the database:
psql postgres
-- create a database called test and connect to it: 
create database test;
\c test;
-- add some data: 
create table account(name varchar primary key, balance int); 
insert into account values ('Alice', 500);
insert into account values ('Bob', 500);

select * from account;

select sum(balance) from account;

-- open a long-running transaction:

begin transaction;
update account set balance=balance-100 where name='Alice';

select * from account;

-- launch another client:
psql test

-- in the 2nd client, inspect the table:
select * from account;

-- exit the 2nd client:
\q 

-- exit the 1st client:
\q

-- relaunch a client:
psql test

select * from account;
-- no change

-- restart the transaction:
begin transaction;
update account set balance=balance-100 where name='Alice';

-- stop the server
pg_ctl stop -D ~/pg14data

-- how is the client doing? 

-- restart the server
pg_ctl start -D ~/pg14data

-- relaunch the client
psql test

select * from account;
-- no change

-- now let's see how constraint satisfaction is impacted by transactions

create table client(name varchar primary key, city varchar);
delete from account; 
alter table account add constraint name_fk foreign key(name) references client(name) deferrable; 
-- note that the constraint is deferrable;  

-- put some data in client:
insert into client values('Alice', 'Paris');

-- try to put back data in account
insert into account values ('Alice', 500); 
-- ok 

insert into account values ('Bob', 500);
-- doesn't work! we will open a long-running transaction, to move the checks at the end: 

begin transaction;
set constraints all deferred;
insert into account values ('Bob', 500); -- this works!
select * from account;

-- in another client, do
select * from client;
select * from account;

-- come back to the first client:
end transaction;

-- now we do it again but correctly:
begin transaction;
set constraints all deferred;
insert into account values ('Bob', 500);
select * from account;
insert into client values('Bob', 'Lyon');
select * from client;
select * from account; 
end transaction; 

-- the default isolation level in PostgreSQL is read committed, which allows unrepeatable reads and phantom reads
-- Next, we show an unrepeatable read.
-- in one client:
begin transaction;
select * from account;

-- now start another client:
select * from account;
begin transaction;
update account set balance=balance+1;

-- in the first client
select * from account

-- in the 2nd client
end transaction;

-- back to the 1st client:
select * from account; 


-- we can relax the isolation level, or make it more strict. https://www.postgresql.org/docs/12/sql-set-transaction.html 

-- begin transaction isolation level read uncommitted;