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

select postgresql example

Question

Tengo el siguiente UPSERT en PostgreSQL 9.5:

INSERT INTO chats ("user", "contact", "name") 
           VALUES ($1, $2, $3), 
                  ($2, $1, NULL) 
ON CONFLICT("user", "contact") DO NOTHING
RETURNING id;

Si no hay conflictos, devuelve algo como esto:

----------
    | id |
----------
  1 | 50 |
----------
  2 | 51 |
----------

Pero si hay conflictos, no devuelve ninguna fila:

----------
    | id |
----------

Quiero devolver las nuevas columnas de id si no hay conflictos o devolver las columnas de id existentes de las columnas en conflicto.
Se puede hacer esto? Si es así, ¿cómo?

Answer 1

La respuesta actualmente aceptada parece estar bien para un único objetivo de conflicto, pocos conflictos, pequeñas tuplas y ningún desencadenante. Y evita el 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.

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

• Puede 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 transacción).

• Lo más importante , con el modelo MVCC de PostgreSQL, se escribe una nueva versión de fila de cualquier manera, sin importar si los datos de la fila son los mismos. Esto incurre en una penalización de rendimiento para el UPSERT, hinchazón de tabla, hinchazón de índice, penalización de rendimiento para todas las operaciones posteriores en la tabla, costo de VACUUM . Un efecto menor para algunos duplicados, pero masivo para la mayoría de los engañados.

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

Para ON CONFLICT DO UPDATE , se debe proporcionar un conflict_target .

Puede lograr (casi) lo mismo sin actualizaciones vacías y efectos secundarios. Y algunas de las siguientes soluciones también funcionan ON CONFLICT DO NOTHING (sin "objetivo de conflicto"), para detectar todos los posibles conflictos que puedan surgir. (Puede o no ser deseable).

Sin carga de escritura concurrente

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

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

Los JOIN chats finales de JOIN chats funcionan porque las filas recién insertadas de un CTE modificador 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).

Dado que la expresión VALUES es independiente (no se adjunta directamente a un 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 usa en INSERT , todos los valores se convierten automáticamente en el 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 citadas, la coerción de la primera es suficiente para determinar el tipo asumido para todos.

La consulta en sí misma puede ser un poco más costosa para algunos duplicados, debido a la sobrecarga del CTE y al SELECT adicional (que debería ser barato ya que el índice perfecto está allí por definición; se implementa una restricción única con un índice).

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

Pero hay menos efectos secundarios y costos ocultos en cualquier caso. Es probablemente más barato en general.

(Las secuencias adjuntas aún están avanzadas, ya que los valores predeterminados se completan antes de probar conflictos).

Sobre los CTE:

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

Con carga de escritura concurrente

Asumiendo el aislamiento predeterminado de la transacción READ COMMITTED .

Respuesta relacionada en dba.SE con explicación detallada:

• Las transacciones concurrentes dan como resultado una condición de carrera con una restricción única en la inserción

La mejor estrategia para defenderse contra las condiciones de carrera depende de los requisitos exactos, el número y el tamaño de las 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 finaliza con ROLLBACK (o cualquier error, es decir, ROLLBACK automático), su transacción puede continuar normalmente. Efecto secundario menor: lagunas en los números secuenciales. Pero no faltan filas.

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

¡Faltan tales filas del conjunto de resultados (aunque existan en la tabla subyacente)!

Esto puede estar bien como está . Especialmente si no está devolviendo filas como en el ejemplo y está satisfecho sabiendo que la fila está allí. 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 caso raro. El punto es comenzar una nueva consulta (puede estar en la misma transacción), que luego verá las filas recién confirmadas.

O compruebe si faltan filas de resultados dentro de la misma consulta y sobrescriba aquellas con el truco de fuerza bruta demostrado 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 las actualizaciones de CTE, antes de devolver el conjunto de resultados completo . Ese último CTE no hará nada la mayor parte del tiempo. Solo si faltan filas del resultado devuelto, usamos la fuerza bruta.

Más sobrecarga, todavía. Cuantos más conflictos con las filas preexistentes, es más probable que esto supere el enfoque simple.

Un efecto secundario: el segundo UPSERT escribe filas fuera de servicio, por lo que reintroduce la posibilidad de puntos muertos (ver más abajo) si se superponen tres o más transacciones que escriben en las mismas filas. Si eso es un problema, necesita una solución diferente.

Problema de concurrencia 2

Si las transacciones simultáneas 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 las filas de manera económica con:

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

Y agregue también una cláusula de bloqueo a SELECT , 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 sé breve.

Más detalles y explicación:

• Cómo incluir filas excluidas en REGRESAR de INSERTAR ... EN CONFLICTO
• ¿Es SELECCIONAR o INSERTAR en una función propensa a las condiciones de carrera?

Puntos muertos?

Defiéndete de los puntos muertos insertando filas en orden consistente . Ver:

• Punto muerto con INSERTOS de varias filas a pesar de EN CONFLICTO NO HAGA NADA

Tipos de datos y conversiones

Tabla existente como plantilla para tipos de datos ...

Las conversiones de tipos explícitas para la primera fila de datos en la expresión VALUES independiente pueden ser inconvenientes. Hay formas de evitarlo. Puede usar cualquier relación existente (tabla, vista, ...) como plantilla de fila. La tabla de destino es la opción obvia para el caso de uso. Los datos de entrada se coaccionan a los tipos apropiados automáticamente, como en una cláusula VALUES 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 needed
    , (''foo2'', ''bar2'', ''bob2'')
   )
   ...

Esto no funciona para algunos tipos de datos (explicación en la respuesta vinculada en la parte inferior). El siguiente truco funciona para todos los tipos de datos:

... y nombres

Si inserta filas enteras (todas las columnas de la tabla, o al menos un conjunto de columnas iniciales), también puede omitir los nombres de columna. Suponiendo que los chats tabla en el ejemplo solo tienen las 3 columnas utilizadas:

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

Explicación detallada y más alternativas:

• Tipo de conversión NULL al actualizar varias filas

Aparte: no use palabras reservadas como "user" como identificador. Esa es una pistola cargada. Utilice identificadores legales, en minúsculas y sin comillas. Lo reemplacé con usr .

Answer 2

Por su parte, ser una extensión de la consulta INSERT puede definirse con dos comportamientos diferentes en caso de conflicto de restricción: 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 hay un conflicto. Primero 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)

Answer 3

Tuve exactamente el mismo problema y lo resolví usando ''hacer actualización'' en lugar de ''no hacer nada'', aunque no tenía nada que actualizar. En su 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 o que ya existían anteriormente.