La forma correcta de actualizar un campo atómicamente en Postgres basándose en su valor actual
Tengo una tabla my_table
en PostgreSQL v 12 que tiene dos campos llamados id
y status
.
Necesito escribir un procedimiento almacenado updateStatus
que tenga tres parámetros (id
, oldStatus
y newStatus
). Debería actualizar el estado de la fila a newStatus
solo si actualmente se encuentra en oldStatus
(de lo contrario, hacer algo más como lanzar una excepción). El procedimiento debería funcionar bien en modo concurrente.
¿Cuál es la mejor manera de lograr esto?
Pensé en el método sugerido aquí: https://stackoverflow.com/questions/11532550/atomic-update-select-in-postgres
¿Está bien? ¿O tal vez hay una forma más fácil de lograr el mismo efecto?
LOOP
UPDATE my_table
SET status = _new_status_
WHERE id = (SELECT id FROM my_table WHERE id = _id_ and status = _old_status_
FOR UPDATE SKIP LOCKED); -- pg 9.5
CONTINUE WHEN FOUND;
UPDATE my_table
SET status = _new_status_
WHERE id = (SELECT id FROM my_table WHERE id = _id_ and status = _old_status_
FOR UPDATE);
EXIT WHEN NOT FOUND; -- lanzar algún error tal vez, etc.
END LOOP;
davy.ai
Como experto en TI especializado en concurrencia de PostgreSQL, sugiero utilizar el enfoque mencionado en la publicación de Stack Overflow, ya que es una manera confiable de actualizar el estado de la fila a
newStatus
solamente si está enoldStatus
en ese momento, y funciona bien en modo concurrente.El enfoque utiliza la cláusula
FOR UPDATE
para bloquear las filas seleccionadas y evitar que otras transacciones las accedan o las modifiquen hasta que la transacción actual haya terminado.El constructo
LOOP
permite que el procedimiento siga intentando actualizar la fila hasta que la encuentre y la actualice correctamente. Esto funciona bien en modo concurrente, ya que otras transacciones también pueden intentar acceder o modificar la misma fila al mismo tiempo.En general, este enfoque es una manera segura y efectiva de lograr el resultado deseado en un entorno concurrente.