Vous n'êtes pas identifié(e).
Pages : 1
Bonjour,
Je suis sous PostgreSQL 8.4
Que signifie exactement le message d'erreur "le curseur ... n'est pas un parcours modifiable..." ?
J'ai bien vu d'où provenait cette erreur, par contre je n'ai pas compris pourquoi.
Soit la table suivante :
CREATE TABLE personnes (
rang INTEGER,
nom VARCHAR(16)
);
INSERT INTO personnes (rang, nom) VALUES (1, 'toto'), (2, 'titi'), (3, 'tata');
et la fonction suivante qui fait un UPDATE sur la table personnes, à l'aide d'un curseur
CREATE OR REPLACE FUNCTION misajour() RETURNS VOID AS $$
DECLARE c_personnes SCROLL CURSOR FOR SELECT * FROM personnes ORDER BY rang;
r_personnes personnes%ROWTYPE;
BEGIN
OPEN c_personnes;
FETCH FIRST FROM c_personnes INTO r_personnes;
WHILE FOUND = TRUE LOOP
RAISE NOTICE 'Le nom est %.', r_personnes.nom;
UPDATE personnes
SET nom = UPPER(nom)
WHERE CURRENT OF c_personnes;
FETCH NEXT FROM c_personnes INTO r_personnes;
END LOOP;
CLOSE c_personnes;
END;
$$ LANGUAGE PLPGSQL;
SELECT misajour();
psql:test.sql:98: NOTICE: Le nom est TOTO.
psql:test.sql:98: ERREUR: le curseur « c_personnes » n'est pas un parcours modifiable de la table « personnes »
CONTEXTE : instruction SQL « UPDATE personnes SET nom = UPPER(nom) WHERE CURRENT OF $1 »
PL/pgSQL function "misajour" line 10 at instruction SQL
A noter que l'erreur (le curseur « c_personnes » n'est pas un parcours modifiable de la table « personnes ») est clairement due à la combinaison du ORDER BY dans le curseur, et du UPDATE dans le corps de la fonction.
En effet, j'ai fait la même fonction successivement (sans ORDER BY et avec UPDATE), puis (avec ORDER BY et sans UPDATE), et les deux fonctions marchent bien.
Ci-dessous les corps de ces fonctions qui marchent :
-- sans ORDER BY, avec UPDATE
CREATE OR REPLACE FUNCTION misajour() RETURNS VOID AS $$
DECLARE c_personnes SCROLL CURSOR FOR SELECT * FROM personnes;
r_personnes personnes%ROWTYPE;
BEGIN
OPEN c_personnes;
FETCH FIRST FROM c_personnes INTO r_personnes;
WHILE FOUND = TRUE LOOP
RAISE NOTICE 'Le nom est %.', r_personnes.nom;
UPDATE personnes
SET nom = UPPER(nom)
WHERE CURRENT OF c_personnes;
FETCH NEXT FROM c_personnes INTO r_personnes;
END LOOP;
CLOSE c_personnes;
END;
$$ LANGUAGE PLPGSQL;
SELECT misajour();
-- avec ORDER BY, sans UPDATE
CREATE OR REPLACE FUNCTION misajour() RETURNS VOID AS $$
DECLARE c_personnes SCROLL CURSOR FOR SELECT * FROM personnes ORDER BY rang;
r_personnes personnes%ROWTYPE;
BEGIN
OPEN c_personnes;
FETCH FIRST FROM c_personnes INTO r_personnes;
WHILE FOUND = TRUE LOOP
RAISE NOTICE 'Le nom est %.', r_personnes.nom;
FETCH NEXT FROM c_personnes INTO r_personnes;
END LOOP;
CLOSE c_personnes;
END;
$$ LANGUAGE PLPGSQL;
SELECT misajour();
Pourquoi (avec ORDER BY et avec UPDATE) ça fait cette erreur ?
Dernière modification par bebert73 (13/02/2011 23:26:45)
Hors ligne
À mon avis (mais je n'ai pas pu le tester), c'est qu'il manque un FOR UPDATE dans la déclaration du curseur:
DECLARE c_personnes SCROLL CURSOR FOR SELECT * FROM personnes ORDER BY rang FOR UPDATE;
Est-ce que ça marche avec ?
Marc.
Hors ligne
Bonjour Marc,
en théorie c'est ça, oui...sauf que l'option SCROLL est interdite avec un FOR UPDATE, un curseur déplaçable doit être en READ ONLY !
Il le précisent bien dans la doc, partie 6 chapitre Références, DECLARE, où ils disent clairement que "les parcours inverses sont aussi interdits lorsque la requête inclut les clauses FOR UPDATE et FOR SHARE; donc SCROLL peut ne pas être indiqué dans ce cas"
Mon exemple était mal choisi car je n'ai qu'à enlever le SCROLL, qui ne sert à rien vu que je ne fais pas de parcours arrière.
Mais supposons que je veuille faire un parcourt arrière sur une liste trié par la colonne "rang", comme dans le cas ci-après, pour par exemple mettre en majuscule 1 nom sur 3, mais en partant de la fin :
CREATE TABLE personnes (
rang INTEGER,
nom VARCHAR(16)
);
INSERT INTO personnes (rang, nom) VALUES (1, 'toto'), (2, 'titi'), (3, 'tata'), (4, 'tutu'), (5, 'momo'), (6, 'mama'), (7, 'mumu'), (8, 'mimi');
Je veux obtenir ('toto', 'TITI', 'tata', 'tutu', 'MOMO', 'mama', 'mumu', 'MIMI') avec systématiquement le dernier élément de la liste (triée selon rang) en majuscule.
En fait il est impossible de faire ça en faisant un parcours inverse. La solution de contournement est cependant très simple, il suffit de trier la liste par ordre DESC, du coup on peut faire un parcourt avant et on n'a plus besoin du scroll (voir mon exemple 3, ci-dessous).
En fait je me suis un peu embrouillé les pédales car sur le point précis du SCROLL, PostgreSQL ne respecte pas du tout le standard SQL. D'après SQL99, SCROLL est nécessaire pour deux choses :
- pour déplacer le curseur de plus d'une position
- pour déplacer le curseur en sens inverse
Or dans PostgreSQL, on peut déplacer le curseur de plus d'une position, y compris sans utiliser SCROLL (on le voit dans mon exemple 3)
On peut même dans certains cas faire un parcourt inverse sans utiliser SCROLL (extrait de la doc : ...PostgreSQL™ autorise les récupérations remontantes sans que l'option SCROLL ne soit précisé, sous réserve que le plan d'exécution du curseur soit suffisamment simple pour être géré sans surcharge. Toutefois, il est fortement conseillé aux développeurs d'application ne pas utiliser les récupérations remontantes avec des curseurs qui n'ont pas été créés avec l'option SCROLL.)
EN RESUME, je retiens qu'en PostgreSQL, on peut très bien ne jamais utiliser l'option SCROLL, car
1°/ elle n'est pas nécessaire pour déplacer le curseur de plusieurs positions,
2°/ un parcourt inverse d'une table peut très bien s'effectuer simplement en triant la table dans le sens DESC
Pour information, ci-dessous les bouts de codes qui illustrent ces différents cas (curseur sans SCROLL, curseur avec SCROLL, et solution de contournement)
-- Exemple 1 : sans le SCROLL, ça produit le message suivant :
ERREUR: le curseur peut seulement parcourir en avant
ASTUCE : Déclarez-le avec l'option SCROLL pour activer le parcours inverse
CREATE OR REPLACE FUNCTION misajour() RETURNS VOID AS $$
DECLARE c_personnes CURSOR FOR SELECT * FROM personnes ORDER BY rang FOR UPDATE;
r_personnes personnes%ROWTYPE;
BEGIN
OPEN c_personnes;
FETCH LAST FROM c_personnes INTO r_personnes;
WHILE FOUND = TRUE LOOP
RAISE NOTICE 'Le nom est %.', r_personnes.nom;
UPDATE personnes
SET nom = UPPER(nom)
WHERE CURRENT OF c_personnes;
MOVE -2 FROM c_personnes;
FETCH PRIOR FROM c_personnes INTO r_personnes;
END LOOP;
CLOSE c_personnes;
END;
$$ LANGUAGE PLPGSQL;
SELECT misajour();
-- Exemple 2 : avec le SCROLL, ça produit le message suivant
ERREUR: DECLARE SCROLL CURSOR ... FOR UPDATE/SHARE n'est pas supporté
DÉTAIL : Les curseurs déplaçables doivent être en lecture seule (READ ONLY).
CREATE OR REPLACE FUNCTION misajour() RETURNS VOID AS $$
DECLARE c_personnes SCROLL CURSOR FOR SELECT * FROM personnes ORDER BY rang FOR UPDATE;
r_personnes personnes%ROWTYPE;
BEGIN
OPEN c_personnes;
FETCH LAST FROM c_personnes INTO r_personnes;
WHILE FOUND = TRUE LOOP
RAISE NOTICE 'Le nom est %.', r_personnes.nom;
UPDATE personnes
SET nom = UPPER(nom)
WHERE CURRENT OF c_personnes;
MOVE -2 FROM c_personnes;
FETCH PRIOR FROM c_personnes INTO r_personnes;
END LOOP;
CLOSE c_personnes;
END;
$$ LANGUAGE PLPGSQL;
SELECT misajour();
-- Exemple 3, solution de contournement (ORDER BY rang DESC)
CREATE OR REPLACE FUNCTION misajour() RETURNS VOID AS $$
DECLARE c_personnes CURSOR FOR SELECT * FROM personnes ORDER BY rang DESC FOR UPDATE;
r_personnes personnes%ROWTYPE;
BEGIN
OPEN c_personnes;
FETCH FIRST FROM c_personnes INTO r_personnes;
WHILE FOUND = TRUE LOOP
RAISE NOTICE 'Le nom est %.', r_personnes.nom;
UPDATE personnes
SET nom = UPPER(nom)
WHERE CURRENT OF c_personnes;
MOVE 2 FROM c_personnes;
FETCH NEXT FROM c_personnes INTO r_personnes;
END LOOP;
CLOSE c_personnes;
END;
$$ LANGUAGE PLPGSQL;
SELECT misajour();
Hors ligne
Oui, j'avais pas vu le mot clé SCROLL. Je devrais arrêter de répondre si tôt le matin 
Marc.
Hors ligne
ha ha, oui, ou alors changer de marque de céréales 
bon, en résumé je retiens que PostgreSQL possède une petite limitation par rapport au standard SQL si on veut parcourir une table dans tous les sens avec un curseur FOR UPDATE (un parcourt en sens purement inverse pouvant se simuler via un ORDER BY DESC)
je viens de tester en ORACLE, là ça marche
ceci étant, ce n'est pas tous les jours qu'on a besoin de faire ce genre de manip...
Hors ligne
Oui. Ou alors, comme autre contournement, on peut ne pas utiliser le current of, mais passer par la clé primaire. C'est un peu plus lent et moins simple d'usage, mais on s'en sort
Marc.
Hors ligne
Pages : 1
