Bulk upsert fails on conflict

[aarjan]

Describe the bug I am adding two separate records while creating a chat room for both users. I get this error when running this query in supabase in flutter app. Key (room_id, from_user_id)=(-, -) already exists. duplicate key value violates unique constraint "unique_room_id_from_user_id"

Here is the db schema

postgres=> \d room;
                                         Table "public.room"
     Column      |           Type           | Collation | Nullable |             Default              
-----------------+--------------------------+-----------+----------+----------------------------------
 id              | bigint                   |           | not null | generated by default as identity
 room_id         | text                     |           |          | 
 user_ids        | character varying[]      |           |          | 
 created_at      | timestamp with time zone |           |          | now()
 updated_at      | timestamp with time zone |           |          | now()
 unmatch         | boolean                  |           |          | false
 from_user_id    | uuid                     |           |          | 
 to_user_id      | uuid                     |           |          | 
 initiated       | boolean                  |           | not null | false
 unmatched_by    | uuid                     |           |          | 
 message_request | boolean                  |           | not null | false
Indexes:
    "room_pkey" PRIMARY KEY, btree (id)
    "unique_room_id_from_user_id" UNIQUE CONSTRAINT, btree (room_id, from_user_id)

final room = RoomModel(
      id: roomID,
      initiated: !isMessageRequest,
      toUserId: user.id,
      fromUserId: fromUserID,
      userIDs: [fromUserID, user.id],
    );

    await _client.from(DBConfig.room).upsert(
      [
        room.toMap(),
        // create room for other user
        room
            .copyWith(
              fromUserId: user.id,
              toUserId: fromUserID,
              isMessageRequest: isMessageRequest,
            )
            .toMap(),
      ],
      onConflict: 'room_id,from_user_id',
    );

Expected behavior But, It works if i use upsert for these two values separately.

    await _client.from(DBConfig.room).upsert(
          room.toMap(),
          onConflict: 'room_id,from_user_id',
        );

    // create room for other user
    await _client.from(DBConfig.room).upsert(
          room
              .copyWith(
                fromUserId: user.id,
                toUserId: fromUserID,
                isMessageRequest: isMessageRequest,
              )
              .toMap(),
          onConflict: 'room_id,from_user_id',
        );

Version (please complete the following information): On Linux/macOS

├── supabase_flutter 2.8.4
│   ├── supabase 2.6.3
│   │   ├── functions_client 2.4.1
│   │   ├── gotrue 2.11.1
│   │   ├── postgrest 2.4.1
│   │   ├── realtime_client 2.4.2
│   │   ├── storage_client 2.3.1

[Vinzent03]

I don't quite get the reason of your upserts. Your id column is the primary key, which doesn't change between your both rows you are trying to upsert. Therefore, you can have only one row with that room id. So when you do two separate upserts, I think the first one is just useless, because you overwrite the same row with the new values from your copied room object. So it makes sense that this one doesn't throw any errors. Your first version, where you upsert both room objects in one go, is probably just how postgresql works. I highly doubt there is any issue with supabase.

[dshukertjr]

Closing as it's stale