INSERT或SELECT策略总是返回一行？ [英] INSERT or SELECT strategy to always return a row?
问题描述
使用Postgres 9.6，我遵循了 https://stackoverflow.com/a/40325406/435563执行 INSERT 或 SELECT 并返回结果ID：
Using Postgres 9.6, I have followed the strategy recommended in https://stackoverflow.com/a/40325406/435563 to do an INSERT or SELECT and return the resulting id:
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
但是，有时这什么都不返回。我本来希望它无论如何都能返回一行。
However, sometimes this returns nothing. I would have expected it to return a row no matter what. How can I fix it to insure it always returns an id?
NB，尽管不返回任何行，但在检查时该行似乎确实存在。我认为问题可能与尝试通过两个会话同时添加相同的记录有关。
NB, despite not returning a row, the row does seem to exist on inspection. I believe the problem may be related to trying to add the same record via two sessions simultaneously.
该表的定义为：
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 子句中也不会改变任何内容，则似乎没有采取锁定措施。不过，这是否真的需要取决于我的猜测，该值不会出现在行中？还是有更好的方法来确保您获得锁定？
If I change prop_type = 'jargon' to prop_type = 'foo' it works! It would seem the lock isn't taken if the expression wouldn't change anything even given the where false clause. Does this really need to depend on my guessing a value that wouldn't be in the row, though? Or is there a better way to ensure you get the lock?
--- 更新 ---
总体情况是，应用程序尝试使用连接池（...带有自动提交）保存有向无环图，并使用此查询获取ID，同时清除重复项。 [结果发现，使用事务并序列化到一个连接要聪明得多。但是这里存在争用时的行为很奇怪。]
The overall situation is that the application tried to save a directed acyclic graph using a connection pool (...with autocommit), and was using this query to get id while winnowing out duplications. [Turns out that much smarter is to use a transaction and just serialize to one connection. But the behavior when there is contention here is odd.]
外键约束似乎并不影响插入-例如：
The foreign key constraint doesn't seem to affect the insert -- e.g.:
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。
Note one with valid fk 208, the other with invalid -208. If I connect a select onto either of these with the full pattern, then in situations without contention, they both return i = 1 as expected.
推荐答案
您的观察似乎不可能。上面的命令应该 始终 返回一个ID，该ID是新插入的行还是已有的行。并发写入不会对此造成混乱，因为现有的冲突行已锁定。相关答案的解释：
Your observation seems impossible. The above command should always return an id, either for the newly inserted row or for the pre-existing row. Concurrent writes cannot mess with this since existing conflicting rows are locked. Explanation in this related answer:
- How to use RETURNING with ON CONFLICT in PostgreSQL?
除非引发异常。在这种情况下，您会收到一条错误消息而不是结果。你检查了吗？您是否有错误处理？ （以防您的应用以某种方式丢弃错误消息：1）修复此问题。 2）数据库日志中还有一个带有默认日志记录设置的条目。）
Unless an exception is raised, of course. You get an error message instead of a result in that case. Did you check that? Do you have error-handling in place? (In case your app somehow discards error messages: 1) Fix that. 2) There is an additional entry in the DB log with default logging settings.)
我确实在表定义中看到了FK约束：
I do see a FK constraint in your table definition:
prop_type text not null references prop_type(name),
如果您尝试插入违反约束的行，则将发生这种情况。如果表 prop_type 中不存在 name ='jargon'的行，那么您将得到：
If you try to insert a row that violates the constraint, that's exactly what happens. If there is no row with name = 'jargon' in table prop_type, that's what you get:
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 这里
您的观察将适合这种犯罪：
Your observation would fit the crime:
如果我将prop_type ='jargon'更改为prop_type ='foo'可以正常工作！
If I change prop_type = 'jargon' to prop_type = 'foo' it works!
但是您的解释是基于误解：
But your explanation is based on misconceptions:
即使给定where虚假子句，表达式也不会改变任何内容，似乎没有采取锁定措施。
It would seem the lock isn't taken if the expression wouldn't change anything even given the where false clause.
这不是Postgres的工作原理。可以采用任何一种方式进行锁定（上面链接的答案中有解释），Postgres锁定机制甚至从未考虑过新行与旧行的比较。
That's not how Postgres works. The lock is taken either way (explanation in above linked answer), and the Postgres locking mechanism never even considers how the new row compares to the old.
这是否真的取决于我的猜测，但是该值不在行中？还是有更好的方法来确保您获得锁定？
Does this really need to depend on my guessing a value that wouldn't be in the row, though? Or is there a better way to ensure you get the lock?
否。
如果确实存在缺失的FK值，则可以在带有rCTE的单个语句中添加缺失的（不同的）值。像您演示的那样，对单行插入很简单，但是也可以一次插入很多行。相关内容：
If missing FK values are indeed the problem, you might add missing (distinct) values in a single statement with rCTEs. Simple for single-row inserts like you demonstrate, but works for inserting many rows at once, too. Related:
- How do I insert a row which contains a foreign key?
- INSERT rows into multiple tables in a single query, selecting from an involved table
- Can INSERT [...] ON CONFLICT be used for foreign key violations?
这篇关于INSERT或SELECT策略总是返回一行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
