Comment récupérer les anciennes valeurs de colonne après une MISE À JOUR dans PostgreSQL en utilisant uniquement SQL ?

Comment obtenir les valeurs de colonne de pré-mise à jour à l'aide de SQL uniquement

Problème :

PostgreSQL ne permet pas de récupérer la pré-mise à jour mettre à jour les valeurs des colonnes dans la clause RETURNING d'une instruction UPDATE. Cela pose un défi lorsque l'on cherche à obtenir les anciennes valeurs des colonnes mises à jour sans utiliser de mécanismes supplémentaires tels que des déclencheurs ou des procédures.

Solution utilisant une auto-jointure :

Une solution consiste à tirer parti d'une auto-jointure dans la clause FROM. Cette technique crée efficacement une copie de la table et permet de référencer à la fois les valeurs mises à jour et celles de pré-mise à jour :

UPDATE tbl x
SET    tbl_id = 23
     , name = 'New Guy'
FROM   tbl y                -- Using the FROM clause
WHERE  x.tbl_id = y.tbl_id  -- Must be UNIQUE NOT NULL
AND    x.tbl_id = 3
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;

Cette méthode nécessite une colonne UNIQUE NOT NULL pour l'auto-jointure, garantissant que chaque ligne correspond à un seul homologue dans l'instance jointe.

Solutions pour la charge d'écriture simultanée :

Dans Dans des scénarios impliquant une forte concurrence d'écriture, deux options existent pour éviter les conditions de concurrence :

Option 1 : niveau d'isolement SERIALIZABLE :

L'utilisation du niveau d'isolement SERIALIZABLE garantit un accès exclusif aux lignes pendant l'exécution de transactions, empêchant ainsi les écritures simultanées :

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE ... ;
COMMIT;

Option 2 : Explicite Verrouillage :

Alternativement, un verrouillage explicite peut être utilisé pour empêcher les mises à jour simultanées des lignes :

UPDATE tbl x
SET    tbl_id = 24
     , name = 'New Gal'
FROM  (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 FOR UPDATE) y 
WHERE  x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!