alter user test set work_mem='4GB';

-- après avoir créer le répertoire et mis les bons droits (sous linux, il suffit que l'utilisateur postgres crée le répertoire)
CREATE TABLESPACE tb_test1 OWNER postgres LOCATION 'D:/pg15/tablespaces';

create table in_tb (id int) tablespace tb_test1 ;

SELECT relname FROM pg_class
WHERE reltablespace = ( SELECT oid FROM pg_tablespace
                        WHERE spcname = 'tb_test1');

ALTER DATABASE nom_bdd SET TABLESPACE tb_test1 ;

-- csv -> table
copy tableName from 'path.csv' with (format 'csv',HEADER);
-- HEADER signifie que le csv contient un header identique à la structure de la table

-- table->csv
copy tableName to 'path.csv' with (format 'csv',HEADER);

-- mais aussi
copy (delete from bruno where id=256 returning *) to 'e:\delete_bruno.csv'  with (format 'csv',HEADER);
-- mais aussi: copy (select ....) 

-- ici c'est contextuel à la bdd sauf create role (donc grant et revoke agissent sur l'instance, deny n'existe pas)
create role cours_postgres;
\dg+
grant select on t1,t2 to cours_postgres; 
create role bruno with login password 'a';
GRANT CONNECT ON DATABASE test TO bruno;
create role jpp with login password 'a' noinherit;
grant cours_postgres to bruno,jpp;
grant select,update on t1,t2 to jpp;

grant update on all tables in schema public to jpp;
select * from pg_roles where rolname='jpp';
select * from pg_user;
select grantee,table_name,privilege_type from information_schema.table_privileges  where grantee='jpp';

-- droits sur les schemas
grant usage on SCHEMA hr to bruno ;
grant all on all tables in schema hr to bruno ;
-- ceci est pour les tables existantes à l'instant t (pas les futures tables), donc pour du dynamisme, ajouter:
alter default privileges in schema hr grant all on tables to bruno ;

-- droit par colonne
grant select (val) on t1 to cours_postgres ; 

create role tmp
reassign owned by cours_postgres to tmp;

alter system set logging_collector=on;

-- si absent, un rep est créé dans le rep de l'instance pour le stockage
alter system set log_directory='F:\psqlSaves-LOG\STAGE\log';

-- ce qu'on veut dans les log
-- toute instruction de durée >0 => tout
alter system set log_statement='all';
alter system set log_min_duration_statement=0;
-- login
alter system set log_connections=on;
alter system set log_disconnections=on;

alter system set log_checkpoints=on;
alter system set log_lock_waits=on;
-- si log_temp_files=2 on a l'evt à partir du 2/3ème fichier temp
alter system set log_temp_files=0;

select pg_switch_wal();
-- provoque un archivage de select pg_walfile_name( pg_current_wal_lsn()); le "current wal" (voir les wal avant et après)
-- réfléchir aux paramètres wal_segment_size & archive_timeout ou utiliser pg_receivewal en mode synchrone
select * from pg_stat_archiver;

-- créer une base puis s'y connecter
create database pitr;
\c pitr
-- créer une table
create table test (val varchar(100));
-- mettre des données dans la table -> ligne1
insert into test values ('début scénario');
table test ;

create database test with template=template1;
\c test
CREATE TABLE public.t1 (
	id int4 NOT NULL,
	val varchar(1000) NOT NULL,
	CONSTRAINT t1_pk PRIMARY KEY (id)
);

select oid,datname from pg_database;
select pg_relation_filepath('t1');
-- ou plus malléable
select oid,relname,relfilenode from pg_class where relname='t1';

-- 5M de lignes dans t1
with recursive cte as (
    select 1 as id,'val1' as val
	union all 
	select id+1,concat('val',(id+1))
	from cte
	where id<5000000
)
insert into t1
select * from cte;

-- copie de t1 sans index
create table t2 as (select * from t1);

select * from t1 t 
order by id offset 3000000 rows fetch next 10 rows only 
; -- pour être sûr d'avoir des données dans shared_buffers

-- taux de remplissage du shared_buffers
create extension pg_buffercache;
select count(*)*100.0/(select count(*) from pg_buffercache) as "%use" 
from pg_buffercache 
where relfilenode is not null
;
-- hit ratio (cf https://dataegret.com/2017/03/deep-dive-into-postgres-stats-pg_stat_database/)
select round((blks_hit::float/(blks_read+blks_hit+1)*100)::numeric,2) as "hitsRatio"
from pg_catalog.pg_stat_database 
where datname ='test'
;
select sum(heap_blks_read) as heap_read,sum(heap_blks_hit) as heap_hit,  
  sum(heap_blks_hit)/(sum(heap_blks_hit)+sum(heap_blks_read)) as ratio
from pg_catalog.pg_statio_user_tables 
;

-- dirty pages dans shared_buffers
select count(*) from pg_buffercache where isdirty !='f';

-- hits
create extension pg_stat_statements; -- log des requêtes avec indications réelles des statistiques
alter system set shared_preload_libraries='pg_stat_statements';
select pg_reload_conf();
select * from pg_settings;
  -- PUIS RESTART PG
SELECT pg_stat_statements_reset();
select * from t2 where id=123456;
explain (analyse,buffers) select * from t2 where id=123456;
select query,shared_blks_hit,shared_blks_read,calls from pg_stat_statements where query like '%t2%';
-- shared_blks_hit & shared_blks_read correspondent à peu près à EXPLAIN, puis faire bouger calls
-- cf https://pganalyze.com/blog/5mins-postgres-explain-pg-stat-statements-plan-cache-mode-normalized-query pour un explain depuis pg_stat_statements

show work_mem;
-- Réglages d'affichage de "log"
show log_temp_files;
set log_temp_files to 0;
show client_min_messages;
set client_min_messages to log;

-- \timing
-- timing n'est pas pertinent ici, mais il est bon de savoir à quoi cela sert

-- Sort Method: external merge
explain (analyse,buffers) select * from t2 order by id desc;
-- Sort Method: quicksort
set work_mem ='512MB';
explain (analyse,buffers) select * from t2 order by id desc;

-- grosse table --
-------------------------------
CREATE TABLE public.grosse (
	id int4 NOT NULL,
	val char(1000) NOT NULL,
	CONSTRAINT grosse_pk PRIMARY KEY (id)
);

insert into grosse  
select x,'toto'||x from generate_series(1,2000000) as x
;

show temp_buffers;
create temporary table tmp as (select * from grosse);
drop table tmp;
-- déconnexion (ou pas pour pour tester le message d'erreur)
set temp_buffers to '1GB';
create temporary table tmp as (select * from grosse);

drop table frag;
create table frag (
num int
);
create index ix_frag_num on frag(num);
insert into frag 
select floor(random()*2000000000)::int from generate_series(1,1000000) as x
;
-- état des indexs
create extension pgstattuple ;

SELECT i.indexrelid::regclass,
       s.*
FROM pg_index AS i
   JOIN pg_class AS t ON i.indexrelid = t.oid
   JOIN pg_opclass AS opc ON i.indclass[0] = opc.oid
   JOIN pg_am ON opc.opcmethod = pg_am.oid
   CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s
WHERE t.relkind = 'i'
  and t.reltype=0 and i.indexrelid::regclass::varchar not like 'pg\_%';
  
-- faire des "trous"
delete from frag 
where num<(select avg(num) from frag);
-- refaire plusieurs fois l'Insert et le delete, attendre...

-- liste des connexions (select * bien aussi)
select usename,pid,application_name from  pg_stat_activity where datname='nom_bdd';
-- tuer des connexions
select pg_terminate_backend(pid) from pg_stat_activity where usename='user_name';

-- faire un lock
begin;
Lock table t1 IN Access exclusive mode;
rollback;
-- essayer l'accès sur une autre connexion

-- chercher les lock
  SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid

    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.granted;