Discussion :

[I folima Elda]

Bonjour à tous,

Je viens pour vous demander un petit coup de main (mais petit le coup ).

Je travaille sur un projet d'historisation de tables d'une base de donnée. Pour cela (et ça m'est imposé), je lancerai un script .sh qui appellera un script .sql via une commande : psql "connexion_à_la_BDD" -f mon_script.sql. Sachant que l'idée est que mon script .sh soit le plus générique possible pour être utilisé pour d'autres BDD que nous possédons. Ainsi ça sera les script .sql qui vont être spécifique.
Bon, jusque là rien d'extraordinaire. J'utilise des variables dans mon .sh et les donne à manger à mon .sql via l'option -v variable=valeur de psql et je les utilise dans mon .sql en faisant :variable

Cependant, je bloque pour la purge.
En effet, dans un 1er temps je copie les données à historiser dans une table millésimée.
Ensuite (et c'est là que je bloque), je souhaite vérifier qu'entre ma table_originel (des données à historisé) et la table_millésimé, on est le même nombre d'enregistrement avant de faire un DELETE.
Mais là je bloque dans .sql. Je n'arrive pas à mettre le resultat d'une requête SELECT count(*) FROM table dans une variable et faire un test IF pour faire oui ou non le DELETE.

un petit schema de l'idée si ça peut aider (sachant que je ne veux que la partie en rouge):



J'espère que j'ai été assez claire. Et surtout que quelqu'un pourra m'aider ou m'orienter vers une doc simple et claire pour faire ça

[escartefigue]

Bonjour,

S'il s'agit de recopier totalement une table dans une table historique, puis de vider la table d'origine il faut utiliser un utilitaire pour la recopie et conditionner ensuite le vidage à la bonne fin de l'utilitaire.
Je ne connais pas spécialement postgres, mais un utilitaire, ça ne se trompe pas, s'il dit que la recopie est ok c'est quelle est ok : donc analysez le retour de l'utilitaire.
En plus un count(*) n'a de valeur qu'à un instant 't', si vous n'avez pas verrouillé la table en mode exclusif, rien n'interdit que d'autres transactions fassent des modifs et le count ne vaut en ce cas plus rien
Ensuite, pour la suppression, il ne faut surtout pas utiliser un delete s'il s'agit bien de tout supprimer. Le delete est lent et il journalise toutes les modifs, là vous n'avez pas besoin de journaliser, au contraire.
Chargez à vide la table sans journalisation

EDIT : une variante, puisqu'à priori vous ne voulez supprimer dans la table d'origine qu'une année parmi plusieurs
Partitionnez la table d'origine sur l'année et chargez à vide la ou les partitions souhaitées

[I folima Elda]

Je suis plutôt de votre avis, je lui ferai confiance. Mais on m'a demander de rajouter une sécurité (je n'ai pensé qu'à celle là).

Ensuite, je sais que le count(*) n'est valable qu'à un instant T. Quand il va y avoir cette tâche d'historisation, les traitements qui peut alimenter la table sont bloqués. Comme ça, je suis certain que je n'aurais pas d'ajout en cours de route. De toute façon, mon ordonnanceur saura faire ça comme il faut quand je l'aurai mis en place

Pour la suppression, a part un DELETE ou un TRUNCATE, je ne vois pas mieux pour supprimer des données. Et je ne peux pas charger à vide ma table car elle peut contenir des données de la nouvelle année (oui je me mets dans le pire des cas à chaque fois ).

Partitionner la table? Si je comprends l'idée, ça serai, au moment de mon historisation/purge, je crée un partitionnement (ici pour 2018) et je supprime les données de cette partition (donc en reviens à DELETE ou TRUNCATE) et enfin supprimer la partition car devenu inutile. J'ai faux?
De toute façon, je pense que ça sera difficile à mettre en place sachant que je n'ai que très peu de liberté sur les caractéristiques de la base et des tables.


Sinon, j'ai aussi eu l'idée de tournée dans l'autre sens mon historisation.
Au lieu d'historiser mes données de 2018 de table_ori vers la table_2018, je renomme ma table_ori en table_2018 et je fais un CREATE TABLE table_ori AS SELECT * FROM table_2018 WHERE date != 2018. Ainsi, je diminue drastiquement le nombre de d'enregistrement à copier/supprimer pour chacune des tables.
Mais quelques soit le cas, il me faut vérifier le nombre d'enregistrement entre les 2 tables pour une date donnée. Et c'est là mon problème

[escartefigue]

Partitionner la table? Si je comprends l'idée, ça serai, au moment de mon historisation/purge, je crée un partitionnement (ici pour 2018) et je supprime les données de cette partition (donc en reviens à DELETE ou TRUNCATE) et enfin supprimer la partition car devenu inutile. J'ai faux?

Le partitionnement peut être fait de façon définitive, sachant qu'on peut modifier les critères à tout moment.
L'intérêt est que chaque partition est un fichier physique différent, on peut donc librement vider telle ou telle partition sans toucher les autres.
Et effectivement, vous avez faux , un chargement à vide ne revient pas du tout à la même chose, un chargement, c'est un utilitaire, on peut désactiver la log. Un delete, c'est du DML, on journalise et c'est lent.

Sinon, j'ai aussi eu l'idée de tournée dans l'autre sens mon historisation.
Au lieu d'historiser mes données de 2018 de table_ori vers la table_2018, je renomme ma table_ori en table_2018 et je fais un CREATE TABLE table_ori AS SELECT * FROM table_2018 WHERE date != 2018. Ainsi, je diminue drastiquement le nombre de d'enregistrement à copier/supprimer pour chacune des tables.

Mauvaise idée, le prédicat différent n'est pas sargable, si la table contient un nombre important de lignes, le traitement sera long. En plus, renommer la table impactera tous les objets qui l'utilisent

Un autre intérêt du partitionnement, c'est que vous aurez un index correspondant au critère de partitionnement (qui peut être multi-colonne). Si le besoin de suppression est du à des problèmes de perfs, le partitionnement est une bonne façon de les améliorer. Le partitionnement par période - année ou mois - étant assez classique

[I folima Elda]

Bon, j'étudierai l'idée du partitionnement dans un second temps en en parlant au chef du projet. Comme je l'ai dis, je n'ai pas la totale liberté sur la BDD du projet ^^
(en plus, en relisant la dic sur le partitionnement, je me rends compte que je l'avais utiliser pour un projet d'étude. Ma mémoire de poisson rouge est vive :p)

Pour mon retournage d'historisation, je ne pense pas qu'il y aura de problème de dépendance de tables vu que toutes mes tables sont indépendantes les unes des autres. Mais je ferai de toutes façon une période de test pour en être certain.

Mais je désire tout de même une solution pour mon problème original: récupérer mes count(*) dans une variable et faire un IF pour savoir si DELETE ou pas, le tout dans un .sql

[aieeeuuuuu]

bonjour,

Faites l'opération en une seule fois en utilisant la clause RETURNING : ainsi, plus besoin de vérifier, et pas de risque d'insertion entre les différentes opérations :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
WITH tmp AS (
    DELETE FROM LaTable
    WHERE LaDate BETWEEN '20180101' AND '20181231'
    RETURNING *
)
INSERT INTO LaTable_2018
    SELECT * FROM tmp;

[escartefigue]

que les tables soient indépendantes c'est une chose, mais il faut penser aussi aux autres objets : packages à rebinder, requêtes à adapter, documentation à modifier...