Вставить или выбрать стратегию, чтобы всегда возвращать строку?

Используя 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
Однако иногда это ничего не дает. Я бы ожидал, что он вернет скандал, несмотря ни на что. Как я могу исправить это, чтобы гарантировать, что он всегда возвращает идентификатор?

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

Рассматриваемая таблица определяется следующим образом: 

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. Действительно ли это должно зависеть от моего предположения о значении, которое не было бы в строке? Или есть лучший способ убедиться, что вы получите замок?

--- обновление ---

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

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

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, как и ожидалось.

2 2
sql postgresql concurrency upsert

2 ответа:

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

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

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

prop_type text not null references prop_type(name),

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

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. Просто для однорядные вставки, как вы демонстрируете, но также работают для вставки нескольких строк одновременно. Связанные:

2

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

ON CONFLICT DO 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)
1