Обновление Postgres при конфликте завершается ошибкой из-за нарушения UniqueContraint

Я хочу реализовать upsert здесь. Значения в некоторых столбцах будут обновляться при каждой вставке.

Я использую -

insert into deployment.nodes values('sind','11', now(),'temp','not-active')  
on conflict on constraint nodes_pkey 
   DO UPDATE SET latest=now(), agent='na', status='active';

Таблица -

create table if not exists deployment.nodes (
   account varchar(40), 
   hostname varchar(100),
   latest timestamptz, 
   agent varchar(50),
   status varchar(50), 
   primary key(account,hostname,agent)
);

После двух/трех вставок я получаю -

devops=# insert into deployment.nodes values('sind','11', now(),'temp','not-active')  on conflict on constraint nodes_pkey DO UPDATE SET latest=now(), agent='ne', status='active';
INSERT 0 1
devops=# insert into deployment.nodes values('sind','11', now(),'temp','not-active')  on conflict on constraint nodes_pkey DO UPDATE SET latest=now(), agent='ne', status='active';
INSERT 0 1
devops=# insert into deployment.nodes values('sind','11', now(),'temp','not-active')  on conflict on constraint nodes_pkey DO UPDATE SET latest=now(), agent='ne', status='active';
INSERT 0 1
devops=# insert into deployment.nodes values('sind','11', now(),'temp','not-active')  on conflict on constraint nodes_pkey DO UPDATE SET latest=now(), agent='ne', status='active';
ERROR:  duplicate key value violates unique constraint "nodes_pkey"
DETAIL:  Key (account, hostname, agent)=(sind, 11, ne) already exists.

Постгрес версия -

devops=# SHOW server_version;
 server_version
----------------
 10.11

Есть идеи, что происходит?


postgresql constraints primary-key upsert postgresql-10
person Darshan Shah    schedule 27.03.2020    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Это нормальное поведение. Первая вставка вставляет с агентом temp. Вторая попытка вставки обновляет первую запись до агента ne. Третий снова вставляет temp агента в новую запись, а четвертый пытается обновить temp до агента ne, вызывая конфликт с первым.

С уважением,
Бьярни

person Bjarni Ragnarsson    schedule 27.03.2020

arrow_upward
0
arrow_downward

Спасибо @Bjarni за то, что прояснили это.

Предполагается, что обновление при конфликте обновляет строку, вызывающую конфликт. В моем случае у самого разрешения конфликтов, похоже, был конфликт.

Я использовал одни и те же значения первичного ключа в своем «операторе вставки», а также в «операторе конфликта», и это решило проблему.

insert into deployment.nodes 
values('sind','11', now(),'temp','not-active')  
on conflict on constraint nodes_pkey 
   DO UPDATE SET latest=now(), agent='temp', status='active';

Спасибо!

person Darshan Shah    schedule 27.03.2020
comment
Вы можете избежать повторения значения 'temp', используя agent = excluded.agent в части обновления. - person a_horse_with_no_name; 27.03.2020