
CREATE TABLE job (
   id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   job_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
   job_end TIMESTAMP WITH TIME ZONE,
   job_name VARCHAR(50) NOT NULL
);
CREATE INDEX ON job(job_start);

CREATE TABLE job_detail (
   jobid INT REFERENCES job(id) ON DELETE CASCADE NOT NULL,
   log_date TIMESTAMP WITH TIME ZONE NOT NULL,
   log_message TEXT NOT NULL
);

-- Quelques données dans les tables
INSERT INTO job (job_end, job_name) VALUES 
   (now() + INTERVAL '1 hour', 'Daily routine');
INSERT INTO job_detail VALUES 
   (currval('job_id_seq'), now() + INTERVAL '1 hour 1s', 'Purge is started');
INSERT INTO job_detail VALUES 
   (currval('job_id_seq'), now() + INTERVAL '1 hour 30min', 'Purge is completed');

--CC 

-- Quelques données dans les tables
INSERT INTO job (job_end, job_name) VALUES 
   (now() + INTERVAL '2 hour', 'Daily routine');
INSERT INTO job_detail VALUES 
   (currval('job_id_seq'), now() + INTERVAL '2 hour 1s', 'Purge is started');
INSERT INTO job_detail VALUES 
   (currval('job_id_seq'), now() + INTERVAL '2 hour 30min', 'Purge is completed');


-- \d+ job
--                                                                   Table « public.job »
--   Colonne  |           Type           | Collationnement | NULL-able |            Par défaut            | Stockage | Cible de statistiques | Description 
-- -----------+--------------------------+-----------------+-----------+----------------------------------+----------+-----------------------+-------------
--  id        | integer                  |                 | not null  | generated by default as identity | plain    |                       | 
--  job_start | timestamp with time zone |                 | not null  | now()                            | plain    |                       | 
--  job_end   | timestamp with time zone |                 |           |                                  | plain    |                       | 
--  job_name  | character varying(50)    |                 | not null  |                                  | extended |                       | 
-- Index :
--     "job_pkey" PRIMARY KEY, btree (id)
--     "job_job_start_idx" btree (job_start)
-- Référencé par :
--     TABLE "job_detail" CONSTRAINT "job_detail_jobid_fkey" FOREIGN KEY (jobid) REFERENCES job(id) ON DELETE CASCADE
-- Méthode d'accès : heap
   
   
   
-- ```
-- 
-- Dans un contexte de croissance, le nombre de travaux (_jobs_) augmente considérablement
-- et la puge de la table devient périllieuse au long terme. La transformation de 
-- cette table en table partionnée se revèle nécessaire.
-- 
-- Créer une table `job_part` ayant la même structure que la table `job` en ajoutant
-- le champ `job_start` dans la contrainte de clé primaire pour satisfaire les
-- prérequis de la clé de partionnement.

-- plutôt : 
CREATE TABLE job_part (LIKE job INCLUDING DEFAULTS INCLUDING IDENTITY )
PARTITION BY RANGE (job_start);

-- # \d+ job_part
--                                                          Table partitionnée « public.job_part »
--   Colonne  |           Type           | Collationnement | NULL-able |            Par défaut            | Stockage | Cible de statistiques | Description 
-- -----------+--------------------------+-----------------+-----------+----------------------------------+----------+-----------------------+-------------
--  id        | integer                  |                 | not null  | generated by default as identity | plain    |                       | 
--  job_start | timestamp with time zone |                 | not null  | now()                            | plain    |                       | 
--  job_end   | timestamp with time zone |                 |           |                                  | plain    |                       | 
--  job_name  | character varying(50)    |                 | not null  |                                  | extended |                       | 
-- Clé de partition : RANGE (job_start)
-- Nombre de partitions : 0

-- future clé primaire
-- à créer avant pour gagner du temps pendant la transaction
CREATE UNIQUE INDEX job_orig_start_id ON job(job_start,id);

-- CREATE TABLE job_part (
--    id INT GENERATED BY DEFAULT AS IDENTITY,
--    job_start TIMESTAMP WITH TIME ZONE DEFAULT now(),
--    job_end TIMESTAMP WITH TIME ZONE,
--    job_name VARCHAR(50) NOT NULL,
--    PRIMARY KEY (id, job_start)
-- ) PARTITION BY RANGE (job_start);

-- LA PROCHAINE ÉTAPE CONSISTE À RATTACHER LA TABLE `JOB` EN PARTITION FILLE PAR
-- DÉFAUT DE LA FUTURE TABLE PARTITIONNÉE. POUR CELA, IL PEUT ÊTRE JUDICIEUX DE 
-- JOUER LES INSTRUCTIONS DANS UNE SEULE TRANSACTION ET DE PRÊTER GARDE AU RESPECT
-- DE LA CONTRAINTE ÉTRANGÈRE SUR LA TABLE `JOB_DETAIL`.
-- 





START TRANSACTION;

-- Suppression des contraintes de clé primaire pour prévenir l'erreur suivante :
-- ERROR:  multiple primary keys for table "job" are not allowed
-- surtout qu'on change la PK de la table
ALTER TABLE job_detail DROP CONSTRAINT IF EXISTS job_detail_jobid_fkey;
ALTER TABLE job DROP CONSTRAINT IF EXISTS job_pkey; -- ancienne PK ne le sera plus

-- Suppression des index au niveau de l'ancienne table pour les redéfinir sur
-- la table partitionnée de façon globale
DROP INDEX job_job_start_idx;

-- Renommage des relations pour maintenir la logique métier
ALTER TABLE job RENAME TO job_default;
ALTER TABLE job_part RENAME TO job;
--ALTER INDEX job_part_pkey RENAME TO job_pkey;

-- Ajout de la table job en tant que partition
ALTER TABLE job ATTACH PARTITION job_default DEFAULT;

--CREATE UNIQUE INDEX job_start_id ON job (job_start,id) ; -- vde ou recycle celui de job_default

-- Créer les PK à la main sur les partitions pour pouvoir réutiliser l existant
-- ces index seront renommés
ALTER TABLE job_default ADD CONSTRAINT job_default_pkey PRIMARY KEY USING INDEX job_orig_start_id ;
--NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "job_orig_start_id" to "job_default_pkey"
--ALTER TABLE
\d+ job_default

-- déclaration de la PK au niveau global, qui va reycler la PK sur la table job_default,
ALTER TABLE job ADD CONSTRAINT job_pkey PRIMARY KEY (job_start, id) ;


\d+ job

\d+ job_default




--CREATE INDEX ON job(job_start);

-- Récupération de la dernière valeur de la séquence de la précédente table
-- avant de la dropper
SELECT nextval(pg_get_serial_sequence('job_default', 'id')) ; -- évite bug
SELECT setval(pg_get_serial_sequence('job', 'id'),
      currval(pg_get_serial_sequence('job_default', 'id')), true);

-- Suppression de l'ancienne séquence et de sa relation avec la table job_default
ALTER TABLE job_default ALTER id DROP IDENTITY, ALTER job_start DROP DEFAULT;
ALTER SEQUENCE job_part_id_seq RENAME TO job_id_seq;

-- Réactivation des contraintes de clés étrangères
-- Puisque la colonne job_start fait à présent partie de la PRIMARY KEY, il est
-- nécessaire d'ajouter cette colonne dans la table job_detail et de l'alimenter
ALTER TABLE job_detail ADD job_start TIMESTAMP WITH TIME ZONE;

select * from job ;

select * from job_detail ;

UPDATE job_detail SET job_start = j.job_start
  FROM job_detail d JOIN job j ON j.id = d.jobid; 

select * from job_detail ;
  
ALTER TABLE job_detail ADD FOREIGN KEY (jobid, job_start) 
   REFERENCES job(id, job_start) ON DELETE CASCADE;

COMMIT;

\echo fin de la conversion

---CC  : test

-- Quelques données dans les tables
INSERT INTO job (job_end, job_name) VALUES 
   (now() + INTERVAL '3 hour', 'Daily routine');
INSERT INTO job_detail VALUES 
   (currval('job_id_seq'), now() + INTERVAL '3 hour 1s', 'Purge is started');
INSERT INTO job_detail VALUES 
   (currval('job_id_seq'), now() + INTERVAL '3 hour 30min', 'Purge is completed');


\quit


-- ```
-- 
-- À partir de cette étape, la table `job` est partitionnée mais toutes les lignes
-- passées et à venir sont stockées dans la partition `job_default`. Il est possible
-- de déplacer les lignes d'une partition vers une nouvelle, bien qu'il soit recommandé
-- de maîtriser les requêtes en provenance des utilisateurs car une série de verrous
-- seront posés entre les tables.
-- 
-- ```sql
CREATE OR REPLACE PROCEDURE add_daily_partition(timestamp with time zone)
   LANGUAGE plpgsql AS $$
   DECLARE
      daily_interval INTERVAL := INTERVAL '1 day';
      tablename   VARCHAR(50) := format('job_%s', TO_CHAR($1, 'YYYYMMDD'));
      from_expr   VARCHAR(50) := date_trunc('day', $1);
      to_expr     VARCHAR(50) := date_trunc('day', $1 + daily_interval);

   BEGIN
      EXECUTE format(
         'CREATE TABLE IF NOT EXISTS %s (LIKE job_default INCLUDING CONSTRAINTS);', 
         tablename
      );      
      EXECUTE format(
         'INSERT INTO %s SELECT * FROM job_default WHERE job_start BETWEEN ''%s'' AND ''%s'';',
         tablename, from_expr, to_expr
      );      
      EXECUTE format(
         'DELETE FROM job_default WHERE job_start BETWEEN ''%s'' AND ''%s'';',
         from_expr, to_expr
      );
      EXECUTE format(
         'ALTER TABLE job ATTACH PARTITION %s FOR VALUES FROM (''%s'') TO (''%s'');',
         tablename, from_expr, to_expr
      );
   END;
$$; 

START TRANSACTION;
LOCK TABLE job;
LOCK TABLE job_detail;

-- Retrait temporaire de la clé étrangère pour réduire les risques 
-- de suppression en cascade
ALTER TABLE job_detail DROP CONSTRAINT IF EXISTS job_detail_jobid_job_start_fkey;
CALL add_daily_partition (now());
ALTER TABLE job_detail ADD FOREIGN KEY (jobid, job_start) 
   REFERENCES job(id, job_start) ON DELETE CASCADE;

COMMIT;
