table temporaire dans une fonction [Résolu]

[jsteffe]

Bonjour,

J'ai créé une table temporaire dans une procédure dans laquelle je stocke les résultats d'un calcul.

A la fin de la procédure, je renvoie les lignes de cette table temporaire comme résultat de la fonction.

Cela marche très bien lors du premier appel de la fonction. mais lors du 2ème appel, il me met le message d'erreur suivant : ERROR: relation with OID 25509 does not exist

En fait, je crois qu'il garde en mémoire l'OID de la table créée lors du 1er appel de la proc. Mais comme la table est détruite sur le commit, elle doit être recréée avec un autre OID : d'où mon problème.


Merci de votre aide ...

[hpalpha]

bonjour,

peux tu mettre ta fonction stp ?

[jsteffe]

voici la fonction.
Je l'ai plus utilisée pour apprendre à maîtriser les tables temporaires dans les procs stockées car on pourrait faire la même chose avec une focntion SQL qui renverrait un select ... limit X ...

A priori, mon problème viendrait du fait suivant : "PL/PgSQL met en cache les fonctions. Un effet de bord malencontrueux est que, si une fonction PL/pgsql accède à une table temporaire et que cette table est ensuite supprimée et recréée, un prochain appel à la fonction échouera car le contenu de la fonction en cache pointera toujours vers l'ancienne table temporaire".
Comment faire pour éviter ce problème ?

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
35
36
37
 
--objectif : renvoyer les x plus grosses commandes d'un client
CREATE OR REPLACE FUNCTION ca_cl2(no_cl char(5), nb integer)
  RETURNS setof v_cde_total AS
$$	
DECLARE
 cdes v_cde_total%rowtype; --crée  un curseur qui a la même structure que la vue v_cde_total
 n bigint;
 i int;
 
BEGIN
i:=0;
n:=3;
  -- crée une table temporaire qui sera détruite au moment du commit
CREATE TEMPORARY TABLE temp_v_cde ON commit DROP  AS 
	SELECT code_client, societe ,  n_commande ,date_commande , total FROM v_cde_total WHERE code_client = no_cl;
 
CREATE TEMPORARY TABLE temp_v_cde_resu 
	(code_client varchar(5), societe varchar(40),  n_commande integer,date_commande date, total numeric) 
	ON commit DROP ;
 
--recopie toutes les commandes du client dans temp_v_cde 
SELECT count(*) INTO n FROM temp_v_cde ;
 
 
while (i<nb AND i<n) loop
	--trouve la plus grosse commande et la transfère dans la table resu (et la supprime de la table temp)
	INSERT INTO temp_v_cde_resu SELECT * FROM temp_v_cde WHERE total = (SELECT max (total) FROM temp_v_cde);
	DELETE FROM temp_v_cde WHERE total = (SELECT max (total) FROM temp_v_cde);
	i:= i+1;
end loop;
	FOR cdes IN SELECT * FROM temp_v_cde_resu LOOP
		RETURN NEXT cdes ;
	END LOOP ;
end;
$$
  LANGUAGE 'plpgsql' volatile;

[hpalpha]

normalement il ne devrait pas mettre en cache, ta fonction est VOLATILE

[jsteffe]

Le problème venait effectivement du fait que pg met en cache les fonctions : si une fonction PL/pgsql accède à une table temporaire et que cette table est ensuite supprimée et recréée, un prochain appel à la fonction échouera car le contenu de la fonction en cache pointera toujours vers l'ancienne table temporaire

même si la fonction est VOLATILE, le problème existe pour les tables temporaires.

La solution consiste à utiliser EXECUTE.
cf code commenté ci-dessous

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
CREATE OR REPLACE FUNCTION ca_cl2(no_cl char(5), nb integer)
  RETURNS setof v_cde_total AS
$$	
DECLARE
 cdes v_cde_total%rowtype; --crée  un curseur qui a la même structure que la vue v_cde_total
 n bigint;
 i int;
request1 varchar(200);
request2 varchar(200);
request3 varchar(200);
 
BEGIN
i:=0;
  -- crée une table temporaire qui sera détruite au moment du commit
--table qui contient toutes les commandes du client
CREATE TEMPORARY TABLE temp_v_cde ON commit DROP  AS 
	SELECT code_client, societe ,  n_commande ,date_commande , total FROM v_cde_total WHERE code_client = no_cl;
 
--table de résultat qui contiendra les x plus grosses commandes du client
CREATE TEMPORARY TABLE temp_v_cde_resu 
	(code_client varchar(5), societe varchar(40),  n_commande integer,date_commande date, total numeric) 
	ON commit DROP ;
 
--compte le nombre de commandes du client
SELECT count(*) INTO n  FROM v_cde_total WHERE code_client = no_cl;
 
--prend les x plus grosses commandes du client et les recopie dans la table temp_v_cde_resu
while (i<nb AND i<n) loop
	/*trouve la plus grosse commande et la transfère dans la table resu (et la supprime de la table temp)
	On est obligé de passer par une commande execute car "PL/PgSQL met en cache les fonctions. 
	Un effet de bord malencontrueux est que, si une fonction PL/pgsql accède à une table temporaire et que cette table est 
	ensuite supprimée et recréée, un prochain appel à la fonction échouera car le contenu de la fonction en cache pointera 
	toujours vers l'ancienne table temporaire".*/
	request1:= 'insert into temp_v_cde_resu select * from temp_v_cde where total = (select max (total) from temp_v_cde)';
	request2:= 'delete from temp_v_cde where total = (select max (total) from temp_v_cde)';
	execute request1;
	execute request2;
 
i:= i+1;
end loop;
	request3:='SELECT * from temp_v_cde_resu';
	FOR cdes IN execute request3 LOOP
		RETURN NEXT cdes ;
	END LOOP ;
end;
$$
  LANGUAGE 'plpgsql' volatile;

[hpalpha]

Oui effectivement, je connaissais pas ce comportement, j'ai vu sur techdev de pg, qu'il fallait utiliser un execute, etrange .... est ce que ce sera un jour corrigé ? je ne sais pas , Bruce Momjian connait ce bug depuis la 7.4

[jeromesteffe]

Je viens de tester sur la version 8.3 : le problème persiste

[Sinclair]

merci pour le tuyo, je viens de rencontrer le même problème également. C'est quand même extraordinaire ...