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.