[postgres 9.4] Utilisation de variables et test

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):
Pièce jointe 514244


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 :)

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 :)

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 :D).

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

Citation:

---

Envoyé par I folima Elda

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 :P, 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.

Citation:

---

Envoyé par I folima Elda

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 :aie:

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

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

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:

---

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;

---

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...

@aieeeuuuuu
Je vais voir cette fonctionnalité. C'est que ça sera peut être plus simple en effet et intéressant. Par contre, ça risque d'être long si j'ai masse enregistrement. Mais ça c'est plus de mon ressort ;)
Mais c'est vrai que l'idée de base du chef était justement de séparer chacune des tâches pour une reprise distinct en cas de besoin. Genre une purge rate, on refait que la purge mais pas l'historisation. Mais si tout est fait en une fois, je peux faire une simple vérif d'un count pour être sûr qu'il n'y ai pas de relicat (ca fera plaisir au chef ^^)
Edit: dans votre commande, on n'inclus pas la création de la table millésimé. Il faut qu'elle soit créer auparavant.


@escartefigue
Effectivement, je pensais également aux vues par exemple. Enfin bon, faire des améliorations, ça va être compliqué vu le paquebot que j'ai dans les têtes pensantes de chef ^^

Bon, pour ceux qui auront la même question dans le futur (et même si ça ne réponds pas à ma question initiale ^^), voici ce que j'ai fait, en me basant sur la réponse de aieeeuuuuu


table_ori => table originelle contenant les données à historiser
table_histo => table d'historisation contenant les données historisées.

1. création de la table historisé identique à la table originelle SANS les données

Code:

---

1 2 3 4

SELECT * INTO table_histo FROM table_ori WHERE FALSE

---

2. copie/suppression des données depuis la table originelle vers la table historisée

Code:

---

1 2 3 4 5 6 7

WITH tmp AS ( DELETE FROM table_ori WHERE date = date d'historisation RETURNING * ) INSERT INTO table_histo SELECT * FROM tmp;

---

Citation:

---

Envoyé par I folima Elda

Par contre, ça risque d'être long si j'ai masse enregistrement. Mais ça c'est plus de mon ressort ;)
Mais c'est vrai que l'idée de base du chef était justement de séparer chacune des tâches pour une reprise distinct en cas de besoin.

---

avec cette solution, vous pouvez limiter le nombre de ligne dans le DELETE et exécuter la requete tant qu'il reste des lignes à traiter...

Citation:

---

Envoyé par aieeeuuuuu

avec cette solution, vous pouvez limiter le nombre de ligne dans le DELETE et exécuter la requete tant qu'il reste des lignes à traiter...

---

Admettons, on a 1.000.000 d'enregistrements.
Je me demande toujours si c'est plus performant de DELETE 1.000.000 ou bien de faire un DELETE tous les 10.000 (donc 100 passages) ou 100.000 (donc 10 passages) enregistrements? Car effectivement prendre 1 ou 2 minutes pour un DELETE, ça ne me dérangera pas en production. Mais 1H, moyen. Des conseils?

ni l'un ni l'autre, s'il s'agit de supprimer tout, il est préférable de faire un truncate, voire un drop de la table puis un create de la table et des objets dépendants

Pour le DROP/CREATE de la table, ça sera malheureusement pas possible du fait de données qui ne pourrait pas être historisé (il arrive que l'on historise en mars 2020 les données de 2019 mais on a eu aussi les données de 2020 qui sont arrivée par exemple).
Pour le TRUNCATE, j'ai lu qu'il y a peu de différence par rapport au DELETE. C'est surtout pour les auto-incremente à mettre à 0 et pour ne pas écrire dans le journal (donc léger gain de perf) qu'on l'utilise. Ca serai à voir au cas par cas car certaine table n'ont pas d'incrément donc ça sera utile, d'autre en ont un mais ne doit pas être réinitialisé. Et oui, pourquoi faire simple quand on peut faire compliqué chez moi (= au taf) ^^

Pour information, l'historisation automatique des données est une fonctionnalité de la norme SQL 2015 (tables temporelles) parfaitement intégrée à certains SGBDR comme Microsoft SQL Server depuis la version 2017.
À lire : https://blog.developpez.com/sqlpro/p...r-presentation

A +

Bonjour,

Citation:

---

Envoyé par I folima Elda

Admettons, on a 1.000.000 d'enregistrements.
Je me demande toujours si c'est plus performant de DELETE 1.000.000 ou bien de faire un DELETE tous les 10.000 (donc 100 passages) ou 100.000 (donc 10 passages) enregistrements?

---

La réponse tient en une injonction : testez

Nous avions mené ce genre de test, sous SQL server, et, en fonction du serveur (dev vs preprod) et du poids de la ligne les réponses ne furent pas identiques.
De mémoire nous avions opté pour une suppression par transaction de 100 000 lignes en production pour le cas qui nous intéressait.

Je regrette aujourd'hui de ne pas avoir posté le code sur ce site, même si le langage n'est pas le même ça aurait pu servir de trame pour la communauté.
Toujours de mémoire, on avait utilisé la clause LIMIT (variabilisé) et @@rowcount (pour stopper la boucle)
Même remarque pour les résultats. Si la valeur exacte n'est pas utile pour la communauté, la silhouette de la courbe l'est plus certainement.