代码之家 › 专栏 › 技术社区 › shaunc

插入或选择策略以始终返回一行?

sql-returning upsert concurrency postgresql sql

shaunc · 技术社区 · 7 年前

https://stackoverflow.com/a/40325406/435563 INSERT 或 SELECT 并返回结果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

注意,尽管没有返回一行,但在检查时该行似乎确实存在。我认为问题可能与试图通过两个会话同时添加相同的记录有关。

该表定义为:

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。如果我用完整模式将select连接到其中任何一个上,那么在没有争用的情况下,它们都会按预期返回I=1。

2 回复 | 直到 7 年前

Erwin Brandstetter 7 年前

你的观察似乎不可能的上述命令应返回新插入的行或预先存在的行的id。并发写入无法处理此问题,因为现有的冲突行已锁定。相关答案中的解释:

How to use RETURNING with ON CONFLICT in 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".

D小提琴 here

你的观察符合犯罪:

但你的解释是基于误解:

这不是博士后的工作方式。无论采用哪种方式(在上面链接的答案中进行了解释),Postgres锁定机制甚至从未考虑新行与旧行的比较。

但是,这真的需要取决于我对一个不在行中的值的猜测吗?还是有更好的方法确保你拿到锁?

如果缺少FK值确实是问题所在,则可以使用RCTE在单个语句中添加缺少的(不同的)值。对于像您演示的那样的单行插入很简单,但也适用于同时插入多行。相关:

Vao Tsun 7 年前

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

在冲突中,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)