0

I have two tables:

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

and a unique index on table name_score:

create unique index name_score_name_uindex
    on name_score(name);

and a trigger function:

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

and now, when I first time insert 3 rows using muti thread, I got:

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

and there a row successfully inserted in db with score 1

after that this function sometimes upsert wrong score.


I have tried lock table and it's working, I'm confusing that.

And I want to know is there any better way to solve that?

Thank you so much reading my quesion.

1
  • Don't try to increment existing value. Re-count the whole table.
    – Akina
    Commented Jul 1 at 10:22

1 Answer 1

0

The problem is the subquery you are using in the UPDATE branch. The problem will go away if you write

... ON CONFLICT (name) DO UPDATE SET score = name_score.score + 1

as long as you are using the default READ COMMITTED transaction isolation level. The row locks taken by the UPDATE will serialize the updates, and the second operation will see the result of the first one, so that there are no race conditions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.