`upsert()` should do ON CONFLICT DO UPDATE/NOTHING in postgresql

[revmischa]

The current upsert offered by Prisma has a race condition (#3242). It would be optimal if at least the Postgres/Mysql/Mongodb drivers used the database's native upsert to avoid that.

Originally posted by @windowsdeveloperwannabe in #8134 (comment)

SELECT then INSERT causes race conditions. Postgres has native support for upsert. It's surprising that prisma has a function explicitly named upsert() that doesn't use this feature.

[lewisakura]

I'm not sure on the performance for INSERT ... ON CONFLICT DO UPDATE but I have a feeling it'd be more efficient than the two SELECT/INSERT queries.

[richardwu]

I've been noticing many weird upsert conflict errors in prod when I have batched queries (e.g., from Apollo) hitting the same endpoint with the same uniqueness criterion: I thought I was doing something wrong until I realized that upsert in Prisma is not actually a true atomic upsert...

I think there should be a big red bold warning in the docs at the very least that upsert is not a true upsert, since I'm sure this can potentially cause a severe race conditions for others.

The workaround here would be to wrap this in an interactive transaction, but unfortunately there's a bug currently with interactive transactions that causes a deadlock... #9846.

[tlindener]

I'm hitting the same issue in a websocket handler. When two messages including the same key are received nothing about concurrency can be done from my side.

[rgmz]

SELECT then INSERT causes race conditions. Postgres has native support for upsert. It's surprising that prisma has a function explicitly named upsert() that doesn't use this feature.

In my experience Postgres' upsert functionality isn't safe from race conditions either -- at least between concurrent transactions. We ended up replacing instances of INSERT INTO ... ON CONFLICT ... with a crude a double-checked lock implementation in high-volume areas.

(Disclaimer: this is likely a sub-optimal implementation; I've also typed it from memory so it's possible there are bugs. 😄 )

CREATE OR REPLACE FUNCTION select_or_insert_foo(p_foo_name text)
    RETURNS int
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_foo_id foo.id%type;
BEGIN
  SELECT id
  INTO v_foo_id
  FROM foo
  WHERE name = p_foo_name;
  
  IF v_foo_id IS NULL THEN
    PERFORM pg_advisory_xact_lock(hashtext('select_or_insert_foo'), hashtext(p_foo_name));

    SELECT id
    INTO v_foo_id
    FROM foo
    WHERE name = p_foo_name;
  
    IF v_foo_id IS NULL THEN
      INSERT INTO foo (name)
      VALUES (p_foo_name)
      RETURNING id INTO v_foo_id;
    END IF;
  END IF;

  RETURN v_foo_id;
END;
$$;

[adarnon]

Any update on this? It's pretty weird that Prisma has an upsert() action that isn't atomic and no one cares...

[SSouper]

Any updates on this? Or any workaround? :(

[alonbilu]

An update & ETA will be appreciated guys

[ledniy]

shouldn't it have high priority?