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

[pimeys]

Ok, so this is kind of one solution for the big issue we have with upserts, updates, deletes and all that. I'll put this in our initiative for the next team to take a look at. We are all of us really hard trying to solve this and many other similar issues for the next release.

For the person implementing a fix, be aware that ON CONFLICT can still have race conditions.

https://stackoverflow.com/questions/26081236/update-where-race-conditions-postgres-read-committed

We have some other ways of doing this too, one of them would be to select for update, which locks the rows and should prevent the issue from happening.

[janpio]

We are treating this issue as the feature/improvement suggestion it is. The actual problems with upsert are tracked via the topic: upsert label: topic: upsert nested upsert ON CONFLICT DO UPDATE/NOTHING is being considered as one of the solutions for these where appropriate.

[garrensmith]

With our latest release we support ON CONFLICT DO UPDATE. You can read more about it in our docs https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#database-upserts