Discussion :

[apidou]

Bonjour,

J'ai tenté de réaliser ma première procèdure avec Postgree : celle-ci échoue :

Code :

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

1
2
3
4
 
ERREUR: mémoire partagée épuisée
État SQL :53200
Astuce : Vous pourriez avoir besoin d'augmenter max_locks_per_transaction.

Je vais faire un peu long dans les explications car j'aimerai des conseils sur le fond (ma requête est elle opportune ?) et sur la forme (je découvre le plpgsql et malgré la lecture de doc j'ai des grosses lacunes...).

BUT :
Je cherche à automatiser la création d'une couche géographique (à partir d'une autre couche) sur différentes périodes et sur l'ensemble des communes de ma région : les données sont très volumineuses et les opérations de traitements gourmandes en ressource...

CODE :

Code :

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

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
CREATE OR REPLACE FUNCTION generation_table_geo() RETURNS BOOLEAN AS $$
DECLARE
mviews RECORD;
insee varchar;
 
BEGIN
	FOR mviews IN SELECT code_insee FROM public.commune ORDER BY code_insee LOOP
	insee:=mviews.code_insee;
 
	--Création d'une table temporaire par période  --
	DROP TABLE IF EXISTS tb_1960 CASCADE CONSTRAINT;
	CREATE TEMP TABLE tb_1960 
	AS
	(SELECT buffer(st_union(buffer(public.tb_parcelle.the_geom,60)),-20)as the_geom
	FROM public.tb_parcelle
	WHERE public.tb_parcelle.id=insee AND  public.tb_parcelle.annee<=1960 );
 
	DROP TABLE IF EXISTS tb_1970 CASCADE CONSTRAINT;
	CREATE TEMP TABLE tb_1970 
	AS
	(SELECT buffer(st_union(buffer(public.tb_parcelle.the_geom,60)),-20)as the_geom
	FROM public.tb_parcelle
	WHERE public.tb_parcelle.id=insee AND  public.tb_parcelle.annee<=1970 );
 
	DROP TABLE IF EXISTS tb_1980 CASCADE CONSTRAINT;
	CREATE TEMP TABLE tb_1980 
	AS
	(SELECT buffer(st_union(buffer(public.tb_parcelle.the_geom,60)),-20)as the_geom
	FROM public.tb_parcelle
	WHERE public.tb_parcelle.id=insee AND  public.tb_parcelle.annee<=1980 );
 
	DROP TABLE IF EXISTS tb_1990 CASCADE CONSTRAINT;
	CREATE TEMP TABLE tb_1990 
	AS
	(SELECT buffer(st_union(buffer(public.tb_parcelle.the_geom,60)),-20)as the_geom
	FROM public.tb_parcelle
	WHERE public.tb_parcelle.id=insee AND  public.tb_parcelle.annee<=1990 );
 
	DROP TABLE IF EXISTS tb_2000 CASCADE CONSTRAINT;
	CREATE TEMP TABLE tb_2000 
	AS
	(SELECT buffer(st_union(buffer(public.tb_parcelle.the_geom,60)),-20)as the_geom
	FROM public.tb_parcelle
	WHERE public.tb_parcelle.id=insee AND  public.tb_parcelle.annee<=2000 );
 
	--Création des indexs--
	EXECUTE 'CREATE INDEX geom60 ON tb_1960 USING gist (the_geom)';
	EXECUTE 'CREATE INDEX geom70 ON tb_1970 USING gist (the_geom)';
	EXECUTE 'CREATE INDEX geom80 ON tb_1980 USING gist (the_geom)';
	EXECUTE 'CREATE INDEX geom90 ON tb_1990 USING gist (the_geom)';
	EXECUTE 'CREATE INDEX geom00 ON tb_2000 USING gist (the_geom)';
 
	--insertions table finale --
	EXECUTE 'INSERT INTO test_tb_result (annee,the_geom) VALUES (''1960'',(SELECT the_geom FROM tb_1960))';
	EXECUTE 'INSERT INTO test_tb_result (annee,the_geom) VALUES (''1970'',(SELECT st_difference(tb_1970.the_geom,tb_1960.the_geom)AS the_geom FROM tb_1970,tb_1960))';
	EXECUTE 'INSERT INTO test_tb_result (annee,the_geom) VALUES (''1980'',(SELECT st_difference(tb_1980.the_geom,tb_1970.the_geom)AS the_geom FROM tb_1980,tb_1970))';
	EXECUTE 'INSERT INTO test_tb_result (annee,the_geom) VALUES (''1990'',(SELECT st_difference(tb_1990.the_geom,tb_1980.the_geom)AS the_geom FROM tb_1990,tb_1980))';
	EXECUTE 'INSERT INTO test_tb_result (annee,the_geom) VALUES (''2000'',(SELECT st_difference(tb_2000.the_geom,tb_1990.the_geom)AS the_geom FROM tb_2000,tb_1990))';
 
	END LOOP;
 
EXECUTE 'UPDATE test_tb_result SET surface = st_area2d(the_geom)';	
 
RETURN true;
 
END;
$$LANGUAGE plpgsql volatile;

Précisions :
- j'ai tenté au départ de faire les traitements sur l'ensemble de la couche régionale mais cela plante : visiblement le traitement de st_difference ne passe pas d'où mon recours à une boucle
- quand j'exécute le SQL de ma procédure sur une commune cela fonctionne très bien (en -de 30s)

Questions :
- que garde la base en mémoire ? je supprime bien les tables temporaires ...
- pourquoi rien ne se passe dans la table finale ? Je pensai que la mise à jour se ferait progressivement à chaque itération... Est ce la cause du problème mémoire ?
- les index sont bien supprimés par DROP TABLE ? Est ce utile d'ajouter CASCADE CONSTRAINT ?
- faut il vraiment que j'augmente max_locks_per_transaction ? si oui comment ...?

Merci de votre aide.

[SQLpro]

Visiblement vous utilisez un SGBDR qui travaille en ensembliste à contre emploi. les SGBDR manipulent des ensembles de données et vous vous faites des boucles y compris en dédoublant les données par des tables temporaires. Cela produit une explosion cardinalistique et conduit au désastre...

Si mes souvenirs sont exact, les communes sont au nombre de 36 000. Vous créez 5 tables avec 2 index chacune cela fait 15 objets à créer pour votre traitement. 15 x 36 000 = 540 000 soit près d'un demi million de tables. Puis vous les supprimez. Soit près d'un million d'opérations DDL (CREATE, ALTER ou DROP...) Or ce sont des opérations qui ne peuvent en aucun cas être optimisées. Bien qu'il y ait des DROP, les tables ne sont pas toujours physiquement libérées immédiatement. Sinon vous attendriez des heures pour la journalisation !!!

Dans la 4e édition de mon livre sur SQL, dans un nouveau chapitre consacré à l'administration des SGBDR, j'attire l'attention sur les méfaits de l'utilisation abusive des tables temporaires...

Bref, commencez par vous former aux bases de données relationnelles et faites des requêtes...
Au final, je suis persuadé que tout votre traitement, avec une base correctement modélisée, se résumera à une seule et unique requête,
Notamment avec un GROUP BY directement sur la table des communes...

Postez votre demande en terme fonctionnelle ainsi que le DDL (CREATE TABLE) de votre table

A +

[apidou]

- Je ne travaille pas sur 36000 mais sur environ 700 communes.
-il me semble créer qu'un index par table et non pas deux ?
-Mon problème n'a rien a voir avec la structuration de ma base de donnée: je pars d'une table postgis (1 multipolygone hyper complexe) sur laquelle j'applique de multiples traitements géométriques. Comme je l'ai déjà noté, je sais très bien réaliser la procédure en une seule requête ; le soucis c'est qu’après une nuit de calcul cela tourne toujours...
Sur la couche sig régionale ça plante, sur une couche communale la requête fonctionne en environ 20s d'où mon idée de recourir à une boucle...
-Je pensai que mes tables temporaires et les index étaient bien effacés à chaque itération et non pas à la fin. Faut il que je passe par des tables en dur (au lieu des 5 tables temporaires) ?

[SQLpro]

Une table temporaire, comme une table en dur suppose de faire redonder les données, et donc conduira aux mêmes explosions de données du fait de la redondance.
D'autre part vous dites :

"je sais très bien réaliser la procédure en une seule requête ; le soucis c'est qu’après une nuit de calcul cela tourne toujours..."

Mais quelles sont les ressources du serveur ?
Quelle RAM ?
Quelle taille de base ?
Quel paramétrage ?
Quel OS ?
Vos données sont-elles indexées ?
Quel est le traitement ? (je ne comprends pas votre calcul SIG basé sur l'expression buffer(st_union(buffer(public.tb_parcelle.the_geom,60)),-20).

A +

[estofilo]

Chaque table ou index créé dans une transaction occasionne un verrou dans cette transaction, qui reste jusqu'à la fin de la transaction, que l'objet soit détruit ou pas.

A supposer que ces tables temporaires soient nécessaires (je n'ai pas d'avis là-dessus), il faudrait les créér en-dehors de la boucle, index compris ce qui est possible puisque leur structure est toujours la même.
A l'intérieur de la boucle pour vider/remplir les tables faire TRUNCATE TABLE nomtable suivi d'un INSERT INTO nomtable SELECT ...

Avec ça le problème de max_locks_per_transaction va disparaitre.

Cependant comme tout ça s'exécute dans une seule transaction, le résultat est tout ou rien.
Tout ce qui est fait dans la table finale n'est pas visible par une autre session pendant la transaction, et à la moindre erreur qui fait échouer la transaction, toutes les écritures qu'a faite la procédure sont perdues.
Je pense que ça répond à la question "pourquoi rien ne se passe dans la table finale?".

[apidou]

Un grand merci pour cette réponse! Je comprends maintenant mieux le fonctionnement de la base et donc ce qui bloque.
Je teste dès que possible...

[apidou]

La technique d'utiliser une mise à jour fonctionne très bien.
merci