我有两张桌子:
create table name
(
id bigserial primary key,
name text not null
);
create table name_score
(
id bigserial primary key,
name text not null,
score bigint default 1 not null
);
以及表 name_score 上的唯一索引:
create unique index name_score_name_uindex
on name_score(name);
以及触发函数:
create or replace function add_name_func() returns trigger as
$name_count$
DECLARE
new_name text;
begin
new_name := NEW.name;
insert into name_score(name)
values (new_name)
on conflict (name) do update set score=(select score from name_score where name = new_name for update) + 1;
return null;
end;
$name_count$ language plpgsql;
create trigger name_score_trigger
after insert
on name
for each row
execute procedure add_name_func();
现在,当我第一次使用多线程插入 3 行时,我得到:
ERROR: null value in column "score" of relation "name_score" violates not-null constraint
Detail: Failing row contains (273, "test", null).
Where: SQL statement "insert into name_score(name)
values (new_name)
on conflict (name) do update set score=(select score from name_score where name = new_name for update) + 1"
PL/pgSQL function add_name_func() line 7 at SQL statement
数据库中成功插入了一行,分数为 1
此后,此功能有时会插入错误的分数。
我已经尝试过锁定表并且它可以工作,但我对此感到困惑。
我想知道有没有更好的方法来解决这个问题?
非常感谢您阅读我的问题。