Forums PostgreSQL.fr

bebert73

Membre

curseur avec ORDER BY et UPDATE

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)

Marc Cousin

Membre

Re : curseur avec ORDER BY et UPDATE

À 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.

bebert73

Membre

Re : curseur avec ORDER BY et UPDATE

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();

Marc Cousin

Membre

Re : curseur avec ORDER BY et UPDATE

Oui, j'avais pas vu le mot clé SCROLL. Je devrais arrêter de répondre si tôt le matin

---

Marc.

bebert73

Membre

Re : curseur avec ORDER BY et UPDATE

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...

Marc Cousin

Membre

Re : curseur avec ORDER BY et UPDATE

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.