@ErwinBrandstetter спасибо - На самом деле я отвечал не на тот вопрос :)

ьзуя Postgres 9.6, я следовал стратегии, рекомендованной вhttps://stackoverflow.com/a/40325406/435563 сделатьINSERT или жеSELECT и вернуть полученный идентификатор:

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

Однако иногда это ничего не возвращает. Я бы ожидал, что он вернет строку, несмотря ни на что. Как я могу это исправить, чтобы он всегда возвращал идентификатор?

Примечание: несмотря на то, что строка не возвращена, строка, кажется, существует при проверке. Я полагаю, что проблема может быть связана с попыткой добавить одну и ту же запись через две сессии одновременно.

Данная таблица определяется как:

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

Данные:

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

Если я изменюprop_type = 'jargon' вprop_type = 'foo' оно работает! Казалось бы, блокировка не взята, если выражение ничего не изменит, даже еслиwhere false пункт. Должно ли это зависеть от того, как я угадал значение, которого не было бы в строке? Или есть лучший способ убедиться, что вы получите блокировку?

---ОБНОВИТЬ ---

Общая ситуация заключается в том, что приложение пыталось сохранить ориентированный ациклический граф, используя пул соединений (... с автоматической фиксацией), и использовало этот запрос для получения идентификатора, исключая дубликаты. [Оказывается, гораздо разумнее использовать транзакцию и просто сериализовать одно соединение. Но поведение, когда есть спор здесь, странное.]

Ограничение внешнего ключа, кажется, не влияет на вставку - например:

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

Обратите внимание, один с действительным FK 208, другой с недействительным -208. Если я подключу выбор к любому из них с полным шаблоном, то в ситуациях без конфликтов они оба возвращают i = 1, как и ожидалось.

 Vao Tsun05 окт. 2017 г., 15:28
пожалуйста, опубликуйте также пример данных. Если я не ошибаюсь, он всегда возвращает строку, таким образом, вы можете даже узнать из xmin, xmax, был ли он обновлен или вставлен
 shaunc05 окт. 2017 г., 15:47
Добавлены данные. Я думаю, что это связано с приложением, пытающимся сохранить сложную структуру данных, в которой этот лист появляется дважды, используя пул соединений. Таким образом, запись добавляется в двух разных соединениях.
 Erwin Brandstetter07 окт. 2017 г., 05:08
Мой ссылочный ответ оSELECT или жеINSERTнеINSERT или жеUPDATE, Так написано жирным шрифтом в первой строке. И этот вопрос тоже. Я предлагаю вам обновить первое предложение.
 Vao Tsun06 окт. 2017 г., 10:12
с вашим обновлением я заметил, где false, что делает обновление не произойдет - с этим ответом будет иначе :)

Ответы на вопрос(2)

https://www.postgresql.org/docs/9.5/static/sql-insert.html

ON CONFLICT DO UPDATE гарантирует атомный результат INSERT или UPDATE; при отсутствии независимой ошибки гарантируется один из этих двух результатов даже при высоком параллелизме.

это касается блокировки в вашем обновленном сообщении. Теперь о начальном вопросе с возвращением строки - сначала я прочитал это неуверенно. Теперь, когда я увиделwhere false - с этим пунктом не всегда возвращается строка. например:

t=# create table a(i int, e int);
CREATE TABLE
t=# insert into a select 1,1;
INSERT 0 1
t=# create unique index b on a (i);
CREATE INDEX
---now insert on conflict do nothing:
t=# insert into a select 1,1 on conflict do nothing returning *,xmax,xmin;
 i | e | xmax | xmin
---+---+------+------
(0 rows)

INSERT 0 0
-- where false same effect - no rows
t=# insert into a select 1,1 on conflict(i) do update set e=2 where false returning *,xmax,xmin;
 i | e | xmax | xmin
---+---+------+------
(0 rows)
-- now insert without conflict:
t=# insert into a select 2,2 on conflict(i) do update set e=2 where EXCLUDED.e=1 returning *,xmax;
 i | e | xmax
---+---+------
 2 | 2 |    0
(1 row)
-- now insert with update on conflict:
INSERT 0 1
t=# insert into a select 1,1 on conflict(i) do update set e=2 where EXCLUDED.e=1 returning *,xmax;
 i | e |   xmax
---+---+-----------
 1 | 2 | 126943767
(1 row)
 Vao Tsun07 окт. 2017 г., 10:43
@ErwinBrandstetter спасибо - На самом деле я отвечал не на тот вопрос :)
 Erwin Brandstetter07 окт. 2017 г., 05:14
Введение в вопрос вводит в заблуждение, это на самом деле оINSERT или жеSELECTнеINSERT или жеUPDATE, И вы, возможно, пропустили добавленныйUNION ALL SELECT ... в вопросе.
Решение Вопроса

кажется невозможно. Приведенная выше команда должнавсегда вернуть идентификатор, либо для вновь вставленной строки, либо для ранее существующей строки. Одновременные записи не могут связываться с этим, поскольку существующие конфликтующие строки заблокированы. Объяснение в этом связанном ответе:

Как использовать RETURNING с ON CONFLICT в PostgreSQL?

Если не Возникает исключение, конечно. В этом случае вы получите сообщение об ошибке вместо результата. Вы это проверяли? У вас есть обработка ошибок на месте? (Если ваше приложение каким-то образом сбрасывает сообщения об ошибках: 1) Исправьте это. 2) В журнале БД есть дополнительная запись с настройками регистрации по умолчанию.)

Я вижу ограничение FK в определении вашей таблицы:

prop_type text not null references prop_type(name),

Если вы попытаетесь вставить строку, которая нарушает ограничение, это именно то, что происходит. Если нет строки сname = 'jargon' в таблицеprop_typeвот что вы получаете:

ERROR:  insert or update on table "prop" violates foreign key constraint "prop_prop_type_fkey"
DETAIL:  Key (prop_type)=(jargon) is not present in table "prop_type".

Демо-версия:

dbfiddleВот

будет соответствовать преступлению:

Если я изменю prop_type = 'jargon' на prop_type = 'foo', это сработает!

Но ваше объяснение основано на неправильных представлениях:

Казалось бы, блокировка не взята, если выражение не изменит ничего, даже учитывая предложение where false.

Это не то, как работает Postgres. Блокировка выполняется в любом случае (объяснение в приведенном выше связанном ответе), и механизм блокировки Postgres даже не учитывает сравнение новой строки со старой.

Должно ли это зависеть от того, как я угадал значение, которого не было бы в строке? Или есть лучший способ убедиться, что вы получите блокировку?

И нет

Если пропущенные значения FK действительно являются проблемой, вы можете добавить пропущенные (отдельные) значения в одном выражении с помощью rCTE. Простой для вставки в одну строку, как вы демонстрируете, но работает и для вставки сразу нескольких строк. Связанный:

Как вставить строку, содержащую внешний ключ?Вставить строки в несколько таблиц в одном запросе, выбирая из вовлеченной таблицыМожет ли INSERT [...] ON CONFLICT использоваться для нарушений внешнего ключа?
 Erwin Brandstetter07 окт. 2017 г., 16:31
@shaunc: Вы имеете в виду наоборот?foo вprop_type а такжене jargon? Потому что, еслиfoo былне, это будет начинать получатьдействительно странно ...
 Erwin Brandstetter08 окт. 2017 г., 06:07
«Это то, что я имею в виду» неоднозначно ...
 shaunc07 окт. 2017 г., 08:25
Спасибо за ответ. С вашим подтверждением это кажется маловероятным. По факту,jargon вprop_type покаfoo не является. Тем временем я переключился на сериализацию, используя один клиент для всего графа объектов, а не полагаясь на пул, что означает, что ошибка не воспроизводится, но я попытаюсь посмотреть, смогу ли я вернуться назад, поскольку это казалось очень странным мне.
 shaunc08 окт. 2017 г., 05:50
Это то, что я имею в виду (!). Однако теперь я не могу проверить другую возможность, о которой вы упомянули, - что произошла ошибка, которую я как-то пропустил (хотя они появляются в приложении, если не перехвачены, и мой журнал тестового приложения не есть какая-то информация, я не могу найти окончательно в pg_log сейчас). Поэтому я склоняюсь к этому варианту, так как знаю, что найти настоящие ошибки довольно сложно. Как я уже сказал, я не могу воспроизвести с текущим кодом, но это достаточно странно, что я хочу вернуться и попробовать. Если здесьне ошибка, какие доказательства я должен попытаться найти, чтобы [dis /] подтвердить, что происходит что-то странное?
 shaunc08 окт. 2017 г., 06:09
Извините, я имею в виду, что мое первоначальное утверждение было правильным. Тестовое приспособление имеетcreate table prop_type ( name text primary key ); insert into prop_type values ('jargon'), ('code');

Ваш ответ на вопрос