¿INSERTAR o SELECCIONAR estrategia para devolver siempre una fila?

Question

Oct 05, 2017, 03:24 PM

sql postgresql concurrency upsert sql-returning

¿INSERTAR o SELECCIONAR estrategia para devolver siempre una fila?

Usando Postgres 9.6, he seguido la estrategia recomendada enhttps://stackoverflow.com/a/40325406/435563 hacer unINSERT oSELECT y devolver la identificación resultante:

with ins as (
  insert into prop (prop_type, norm, hash, symbols)
  values (
    $1, $2, $3, $4
  ) on conflict (hash) do
    update set prop_type = 'jargon' where false
  returning id)
select id from ins
union all
select id from prop where hash = $3

Sin embargo, a veces esto no devuelve nada. Hubiera esperado que volviera una fila sin importar qué. ¿Cómo puedo solucionarlo para asegurarme de que siempre devuelve una identificación?

NB, a pesar de no devolver una fila, la fila parece existir en la inspección. Creo que el problema puede estar relacionado con tratar de agregar el mismo registro a través de dos sesiones simultáneamente.

La tabla en cuestión se define como:

create table prop (
  id serial primary key,
  prop_type text not null references prop_type(name),
  norm text not null,
  hash text not null unique,
  symbols jsonb
);

Datos:

EDT DETAIL:  parameters: $1 = 'jargon', $2 = 'j2', $3 = 'lXWkZSmoSE0mZ+n4xpWB', $4 = '[]'

Si cambioprop_type = 'jargon' aprop_type = 'foo' ¡funciona! Parecería que el bloqueo no se toma si la expresión no cambia nada, incluso dada lawhere false cláusula. Sin embargo, ¿esto realmente debe depender de que adivine un valor que no estaría en la fila? ¿O hay una mejor manera de asegurarse de obtener la cerradura?

---ACTUALIZAR ---

La situación general es que la aplicación intentó guardar un gráfico acíclico dirigido usando un grupo de conexiones (... con confirmación automática), y estaba usando esta consulta para obtener la identificación mientras eliminaba las duplicaciones. [Resulta que es mucho más inteligente usar una transacción y simplemente serializar a una conexión. Pero el comportamiento cuando hay contención aquí es extraño.]

La restricción de clave externa no parece afectar la inserción, por ejemplo:

create table foo(i int unique, prop_id int references prop(id));
insert into foo values (1, 208);
insert into foo values (1, 208) 
on conflict (i) do update set prop_id = 208 where false;
--> INSERT 0 0
insert into foo values (1, 208) 
on conflict (i) do update set prop_id = -208 where false;
--> INSERT 0 0

Tenga en cuenta uno con fk 208 válido, el otro con -208 inválido. Si conecto una selección en cualquiera de estos con el patrón completo, en situaciones sin contención, ambos devuelven i = 1 como se esperaba.

Deja tu comentario