Partitionnements automatiques via script
Bonjour à tous,
Alors avant toute chose, sachez que je suis novice en Postgres. J'ai même pas 1an de formation dans les doigts ^^. Mais je tente de me documenter et de tester ce que je trouve. On progresse petit à petit :)
Pour remettre dans le contexte. J'ai un projet de migration d'une base oracle sous postgres. Bon et comme c'est pas aussi simple, on la modifie entre temps (le nombre de table, relation etc...) donc les outils de migration = inutiles.
La création des tables, aucun soucis. Maintenant on me demande de partitionner l'une des tables pour la rendre plus performante. Ce partitionnement se fera sur une date avec une plage de 2ans. Donc j'aurai 24 tables filles mensuellement géré.
L'idée serai de supprimer le mois le plus ancien lorsque l'on arrive au mois en cours. Par exemple, supprimer la table fille de 2015-07 quand on arrive à 2017-07 et créer cette dernière.
A la main, pas de soucis: on CREATE TABLE 2017-07 et on DROP TABLE 2015-17 (et index associé)
Cependant, bon informaticien que je suis, je suis un fainéant et je désire automatiser ça: DROP/CREATE automatique avec l'horodatage dans le nom de la table fille.
En me perdant dans les méandre du net, j'ai pu voir cette proposition. Mais je ne la comprends pas totalement et ne suis pas certain qu'elle fasse exactement ce que je désire.
source: https://blog.hbis.fr/2014/01/13/zabb...ql_partitions/
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| CREATE OR REPLACE FUNCTION trg_monthly_partition()
RETURNS trigger AS $BODY$
DECLARE tablename text;
partname text;
startdate text;
enddate text;
query text;
BEGIN
tablename := TG_ARGV[0];
partname := tablename || '_' || TO_CHAR(to_TIMESTAMP(NEW.clock), 'YYYY-MM');
EXECUTE 'INSERT INTO ' || 'partition.' || quote_ident(partname) || ' SELECT ($1).*' USING NEW;
RETURN NULL;
EXCEPTION
WHEN undefined_table THEN
startdate := EXTRACT(EPOCH FROM date_trunc('month', TO_TIMESTAMP(NEW.clock)));
enddate := EXTRACT(EPOCH FROM date_trunc('month', TO_TIMESTAMP(NEW.clock) + ('1 month')::interval));
EXECUTE 'CREATE TABLE IF NOT EXISTS ' || 'stat.' || quote_ident(partname) || ' (CHECK ((clock >= ' || quote_literal(startdate) || ' AND clock < ' || quote_literal(enddate) || '))) INHERITS ( ' || tablename || ' )';
EXECUTE 'CREATE INDEX ' || quote_ident(partname) || '_1 on ' || 'stat.' || quote_ident(partname) || '(itemid, clock)';
EXECUTE 'INSERT INTO ' || 'stat.' || quote_ident(partname) || ' SELECT($1).*' USING NEW;
RETURN NULL;
END;
$BODY$LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION trg_monthly_partition()
OWNER TO postgres; |
Pourriez-vous m'indiquer un script (oui je fais tout en script.sql en ce moment ^^) pouvant résoudre mon soucis?
Merci beaucoup
