▷¿Cómo usar RETURNING con ON CONFLICT en PostgreSQL? ✔️ Foro Ayuda 【 2024 】

Solución:

La respuesta actualmente aceptada parece estar bien para un solo objetivo de conflicto, pocos conflictos, pequeñas tuplas y ningún desencadenante. Evita problema de concurrencia 1 (ver más abajo) con fuerza bruta. La solución simple tiene su atractivo, los efectos secundarios pueden ser menos importantes.

Para todos los demás casos, sin embargo, no actualice filas idénticas sin necesidad. Incluso si no ve ninguna diferencia en la superficie, hay varios efectos secundarios:

Podría disparar disparadores que no deberían dispararse.
Bloquea las filas “inocentes”, posiblemente incurriendo en costos por transacciones concurrentes.
Puede hacer que la fila parezca nueva, aunque es antigua (marca de tiempo de la transacción).
Más importante, con el modelo MVCC de PostgreSQL, se escribe una nueva versión de fila para cada UPDATE, no importa si los datos de la fila cambiaron. Esto incurre en una penalización de rendimiento para el UPSERT en sí, hinchazón de la mesa, hinchazón de índice, penalización de rendimiento para operaciones posteriores en la mesa, VACUUM costo. Un efecto menor para algunos duplicados, pero masivo para la mayoría de los incautos.

Más, a veces no es práctico o incluso posible usar ON CONFLICT DO UPDATE. El manual:

Para ON CONFLICT DO UPDATE, a conflict_target debe ser provisto.

A soltero “objetivo de conflicto” no es posible si están involucrados múltiples índices / restricciones.

Puede lograr (casi) lo mismo sin actualizaciones vacías y efectos secundarios. Algunas de las siguientes soluciones también funcionan con ON CONFLICT DO NOTHING (sin “objetivo de conflicto”), para atrapar todos posibles conflictos que puedan surgir, que pueden ser deseables o no.

Sin carga de escritura simultánea

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id  --, usr, contact              -- return more columns?
   )
SELECT 'i' AS source                           -- 'i' for 'inserted'
     , id  --, usr, contact                    -- return more columns?
FROM   ins
UNION  ALL
SELECT 's' AS source                           -- 's' for 'selected'
     , c.id  --, usr, contact                  -- return more columns?
FROM   input_rows
JOIN   chats c USING (usr, contact);           -- columns of unique index

los source La columna es una adición opcional para demostrar cómo funciona. De hecho, es posible que lo necesite para diferenciar ambos casos (otra ventaja sobre las escrituras vacías).

El final JOIN chats funciona porque las filas recién insertadas de un CTE de modificación de datos adjunto aún no son visibles en la tabla subyacente. (Todas las partes de la misma instrucción SQL ven las mismas instantáneas de las tablas subyacentes).

Desde el VALUES La expresión es independiente (no está directamente vinculada a una INSERT) Postgres no puede derivar tipos de datos de las columnas de destino y es posible que deba agregar conversiones de tipos explícitos. El manual:

Cuando VALUES se utiliza en INSERT, todos los valores se transfieren automáticamente al tipo de datos de la columna de destino correspondiente. Cuando se usa en otros contextos, puede ser necesario especificar el tipo de datos correcto. Si todas las entradas son constantes literales entre comillas, coaccionar la primera es suficiente para determinar el tipo asumido para todas.

La consulta en sí (sin contar los efectos secundarios) puede ser un poco más cara para pocos engaños, debido a los gastos generales del CTE y los SELECT (que debería ser barato ya que el índice perfecto está ahí por definición; una restricción única se implementa con un índice).

Puede ser (mucho) más rápido para muchos duplicados. El costo efectivo de las escrituras adicionales depende de muchos factores.

Pero hay menos efectos secundarios y costos ocultos En todo caso. Probablemente sea más barato en general.

Las secuencias adjuntas todavía están avanzadas, ya que los valores predeterminados se completan antes de prueba de conflictos.

Acerca de los CTE:

¿Son las consultas de tipo SELECT el único tipo que se puede anidar?
Deduplicar declaraciones SELECT en división relacional

Con carga de escritura concurrente

Asumiendo defecto READ COMMITTED aislamiento de transacciones. Relacionado:

Las transacciones simultáneas dan como resultado una condición de carrera con una restricción única en la inserción

La mejor estrategia para defenderse de las condiciones de carrera depende de los requisitos exactos, el número y tamaño de filas en la tabla y en los UPSERT, el número de transacciones concurrentes, la probabilidad de conflictos, los recursos disponibles y otros factores …

Problema de concurrencia 1

Si una transacción concurrente ha escrito en una fila que su transacción ahora intenta UPSERT, su transacción tiene que esperar a que finalice la otra.

Si la otra transacción termina con ROLLBACK (o cualquier error, es decir, automático ROLLBACK), su transacción puede continuar con normalidad. Posible efecto secundario menor: lagunas en números secuenciales. Pero no faltan filas.

Si la otra transacción termina normalmente (implícita o explícita COMMIT), tu INSERT detectará un conflicto (el UNIQUE índice / restricción es absoluto) y DO NOTHING, por lo tanto, tampoco devuelve la fila. (Tampoco se puede bloquear la fila como se muestra en problema de concurrencia 2 abajo, ya que es no visible.) Los SELECT ve la misma instantánea desde el inicio de la consulta y tampoco puede devolver la fila aún invisible.

¡Cualquiera de estas filas falta en el conjunto de resultados (aunque existen en la tabla subyacente)!

Esta puede estar bien como está. Especialmente si no devuelve filas como en el ejemplo y está satisfecho sabiendo que la fila está ahí. Si eso no es lo suficientemente bueno, hay varias formas de evitarlo.

Puede verificar el recuento de filas de la salida y repetir la declaración si no coincide con el recuento de filas de la entrada. Puede ser lo suficientemente bueno para el raro caso. El punto es iniciar una nueva consulta (puede estar en la misma transacción), que luego verá las filas recién confirmadas.

O comprobar si faltan filas de resultados dentro de la misma consulta y Sobrescribir aquellos con el truco de la fuerza bruta demostrados en la respuesta de Alextoni.

WITH input_rows(usr, contact, name) AS ( ... )  -- see above
, ins AS (
   INSERT INTO chats AS c (usr, contact, name) 
   SELECT * FROM input_rows
   ON     CONFLICT (usr, contact) DO NOTHING
   RETURNING id, usr, contact                   -- we need unique columns for later join
   )
, sel AS (
   SELECT 'i'::"char" AS source                 -- 'i' for 'inserted'
        , id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's'::"char" AS source                 -- 's' for 'selected'
        , c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (                                      -- RARE corner case
   INSERT INTO chats AS c (usr, contact, name)  -- another UPSERT, not just UPDATE
   SELECT i.*
   FROM   input_rows i
   LEFT   JOIN sel   s USING (usr, contact)     -- columns of unique index
   WHERE  s.usr IS NULL                         -- missing!
   ON     CONFLICT (usr, contact) DO UPDATE     -- we've asked nicely the 1st time ...
   SET    name = c.name                         -- ... this time we overwrite with old value
   -- SET name = EXCLUDED.name                  -- alternatively overwrite with *new* value
   RETURNING 'u'::"char" AS source              -- 'u' for updated
           , id  --, usr, contact               -- return more columns?
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;

Es como la consulta anterior, pero agregamos un paso más con el CTE ups, antes de devolver el completo conjunto resultante. Ese último CTE no hará nada la mayor parte del tiempo. Solo si faltan filas en el resultado devuelto, usamos la fuerza bruta.

Más gastos generales, todavía. Cuantos más conflictos con filas preexistentes, más probable es que esto supere al enfoque simple.

Un efecto secundario: el segundo UPSERT escribe filas fuera de orden, por lo que reintroduce la posibilidad de interbloqueos (ver más abajo) si tres o mas las transacciones que escriben en las mismas filas se superponen. Si eso es un problema, necesita una solución diferente, como repetir la declaración completa como se mencionó anteriormente.

Problema de concurrencia 2

Si las transacciones concurrentes pueden escribir en las columnas involucradas de las filas afectadas, y debe asegurarse de que las filas que encontró todavía estén allí en una etapa posterior de la misma transacción, puede bloquear filas existentes barato en el CTE ins (que de lo contrario se desbloquearía) con:

...
ON CONFLICT (usr, contact) DO UPDATE
SET name = name WHERE FALSE  -- never executed, but still locks the row
...

Y agregue una cláusula de bloqueo al SELECT también, como FOR UPDATE.

Esto hace que las operaciones de escritura de la competencia esperen hasta el final de la transacción, cuando se liberan todos los bloqueos. Así que sea breve.

Más detalles y explicación:

Cómo incluir filas excluidas en el REGRESO de INSERT … ON CONFLICT
¿Es SELECT o INSERT en una función propensa a las condiciones de carrera?

Deadlocks?

Defenderse interbloqueos insertando filas en orden consistente. Ver:

Interbloqueo con inserciones de varias filas a pesar de que EN CONFLICTO NO HAGA NADA

Tipos de datos y conversiones

Tabla existente como plantilla para tipos de datos …

Conversiones de tipo explícito para la primera fila de datos en el independiente VALUES la expresión puede ser inconveniente. Hay formas de evitarlo. Puede utilizar cualquier relación existente (tabla, vista, …) como plantilla de fila. La tabla de destino es la elección obvia para el caso de uso. Los datos de entrada son coaccionados a tipos apropiados automáticamente, como en el VALUES cláusula de un INSERT:

WITH input_rows AS (
  (SELECT usr, contact, name FROM chats LIMIT 0)  -- only copies column names and types
   UNION ALL
   VALUES
      ('foo1', 'bar1', 'bob1')  -- no type casts here
    , ('foo2', 'bar2', 'bob2')
   )
   ...

Esto no funciona para algunos tipos de datos. Ver:

Transmitir el tipo NULL al actualizar varias filas

… y nombres

Esto también funciona para todos tipos de datos.

Al insertar en todas las columnas (iniciales) de la tabla, puede omitir los nombres de las columnas. Asumiendo mesa chats en el ejemplo solo consta de las 3 columnas utilizadas en el UPSERT:

WITH input_rows AS (
   SELECT * FROM (
      VALUES
      ((NULL::chats).*)         -- copies whole row definition
      ('foo1', 'bar1', 'bob1')  -- no type casts needed
    , ('foo2', 'bar2', 'bob2')
      ) sub
   OFFSET 1
   )
   ...

Aparte: no uses palabras reservadas como "user" como identificador. Eso es un revólver cargado. Utilice identificadores legales, en minúsculas y sin comillas. Lo reemplacé con usr.

Tuve exactamente el mismo problema y lo resolví usando “actualizar” en lugar de “no hacer nada”, aunque no tenía nada que actualizar. En tu caso sería algo como esto:

INSERT INTO chats ("user", "contact", "name") 
       VALUES ($1, $2, $3), 
              ($2, $1, NULL) 
ON CONFLICT("user", "contact") DO UPDATE SET name=EXCLUDED.name RETURNING id;

Esta consulta devolverá todas las filas, independientemente de que se hayan insertado recientemente o de que existieran antes.

Upsert, siendo una extensión del INSERT La consulta se puede definir con dos comportamientos diferentes en caso de un conflicto de restricciones: DO NOTHING o DO UPDATE.

INSERT INTO upsert_table VALUES (2, 6, 'upserted')
   ON CONFLICT DO NOTHING RETURNING *;

 id | sub_id | status
----+--------+--------
 (0 rows)

Tenga en cuenta también que RETURNING no devuelve nada, porque no se han insertado tuplas. Ahora con DO UPDATE, es posible realizar operaciones en la tupla con la que existe un conflicto. En primer lugar, tenga en cuenta que es importante definir una restricción que se utilizará para definir que existe un conflicto.

INSERT INTO upsert_table VALUES (2, 2, 'inserted')
   ON CONFLICT ON CONSTRAINT upsert_table_sub_id_key
   DO UPDATE SET status = 'upserted' RETURNING *;

 id | sub_id |  status
----+--------+----------
  2 |      2 | upserted
(1 row)

Comentarios y valoraciones del artículo

Eres capaz de añadir valor a nuestro contenido tributando tu veteranía en las explicaciones.

¡Haz clic para puntuar esta entrada!

(Votos: 0 Promedio: 0)

¿Cómo usar RETURNING con ON CONFLICT en PostgreSQL?