funciones - update case when postgresql
Postgres, actualización y bloqueo de pedidos (2)
Estoy trabajando en Postgres 9.2.
Hay 2 ACTUALIZACIONES, cada una en sus propias transacciones. Uno se ve así:
UPDATE foo SET a=1 WHERE b IN (1,2,3,4);
El otro es similar:
UPDATE foo SET a=2 WHERE b IN (1,2,3,4);
Posiblemente podrían ejecutarse al mismo tiempo y en realidad tener más de 500 en la expresión ''IN''. A veces estoy viendo bloqueos. ¿Es cierto que ese orden de elementos en la expresión ''IN'' puede no influir en el verdadero orden de bloqueo?
Sí. Creo que el problema principal aquí es que IN comprueba la membresía en el conjunto especificado, pero no confiere ningún tipo de orden en la UPDATE , lo que a su vez significa que no se concede ningún pedido concreto en el pedido de bloqueo.
La cláusula WHERE en una instrucción UPDATE esencialmente se comporta de la misma manera que en un SELECT . Por ejemplo, a menudo simularé una UPDATE usando un SELECT para verificar qué se actualizará para ver que es lo que esperaba.
Teniendo esto en cuenta, el siguiente ejemplo que usa SELECT demuestra que IN no confiere orden en sí mismo:
Dado este esquema / datos:
create table foo
(
id serial,
val text
);
insert into foo (val)
values (''one''), (''two''), (''three''), (''four'');
Las siguientes consultas:
select *
from foo
where id in (1,2,3,4);
select *
from foo
where id in (4,3,2,1);
arroje exactamente los mismos resultados: las filas en orden desde id 1-4.
Incluso eso no está garantizado , ya que no ORDER BY un ORDER BY en la selección. Por el contrario, sin él, Postgres utiliza el orden que el servidor decida que es el más rápido (consulte el punto 8 sobre ORDER BY en el documento de Postgres SELECT ). Dada una tabla bastante estática, a menudo es el mismo orden en el que se insertó (como fue el caso aquí). Sin embargo, no hay nada que garantice eso, y si hay mucha agitación en la mesa (muchas tuplas muertas, filas eliminadas, etc.), es menos probable que sea el caso.
Sospecho que eso es lo que está pasando aquí con tu UPDATE . A veces, si no la mayoría de las veces, puede terminar en orden numérico si es la misma forma en que se insertaron las filas, pero no hay nada que lo garantice, y los casos en los que se ven los puntos muertos son escenarios donde los datos ha cambiado de tal manera que una actualización se ordena diferente a la otra.
sqlfiddle con el código anterior.
Posibles soluciones / soluciones alternativas:
En términos de lo que podría hacer al respecto, existen varias opciones, según sus requisitos. Podría explícitamente sacar un bloqueo de tabla en la mesa, aunque eso tendría el efecto de serializar las actualizaciones allí, lo que puede ser un cuello de botella demasiado grande.
Otra opción, que aún permitiría la concurrencia, es iterar explícitamente sobre los elementos usando SQL dinámico en, por ejemplo, Python . De esta manera, tendrías un conjunto de actualizaciones de una fila que ocurrieron siempre en el mismo orden, y dado que podrías asegurar ese orden consistente, el bloqueo normal de Postgres debería poder manejar la concurrencia sin bloqueo.
Eso no funcionará tan bien como la actualización de lotes en SQL puro, pero debería resolver el problema de bloqueo. Una sugerencia para aumentar el rendimiento es COMMIT vez en cuando, y no después de cada fila, lo que ahorra una gran sobrecarga.
Otra opción sería hacer el ciclo en una función de Postgres escrita en PL / pgSQL . Esa función podría llamarse externamente, por ejemplo, en Python , pero el bucle se haría (también explícitamente) en el servidor, lo que podría ahorrar algo de sobrecarga, ya que el bucle y las UPDATEs se realizan completamente en el servidor sin tener que ir sobre el cable de cada iteración de bucle.
No hay ORDER BY en el comando UPDATE .
Pero está para SELECT . Use el bloqueo a nivel de fila con la cláusula FOR UPDATE en una subconsulta:
UPDATE foo f
SET a = 1
FROM (
SELECT b FROM foo
WHERE b IN (1,2,3,4)
ORDER BY b
FOR UPDATE
) upd
WHERE f.b = upd.b;
Por supuesto, b tiene que ser UNIQUE o debe agregar más expresiones a la cláusula ORDER BY para que sea inequívoco.
Y debe aplicar el mismo orden para todas las UPDATE , DELETE y SELECT .. FOR UPDATE en la tabla.
Relacionado, con más detalles: