Postgres sqlx upsert causing pq: duplicate key value violates unique constraint

Question

I have a table with a unique index called user_conn_unique and I perform an 'upsert' on that table however intermittently I am getting an error pq: duplicate key value violates unique constraint "user_unique_connection"

I have tried recreating the issue locally by running concurrent request for both insert and update scenarios and also executing the statements directly on the db with many records seeded but cannot recreate it.

From what I see in the logs we receive two requests microseconds apart and one is successful and one fails with the above error

Example Data -

user_id = 592c70b4-48b0-11ec-81d3-0242ac130003

location = EU

group_id = 592c70b4-48b0-11ec-81d3-0242ac131111

In the database there are no duplicates and all the data looks correct.

Below is the table and Go code

PostgreSQL 10.14 - table around 200k rows

create table user_conn (
    user_id uuid not null,
    location text not null,
    group_id uuid not null,
    created_at timestamp with time zone not null default current_timestamp,
    connected_at timestamp with time zone not null default current_timestamp,
    disconnected_at timestamp with time zone,
    primary key (user_id, group_id, location)
);

create unique index user_unique_connection on user_conn (location, user_id, group_id, coalesce(disconnected_at, '1970-01-01'));

alter table user_conn add column unlinked_at timestamp with time zone default null;

Go 1.16

"database/sql"

"github.com/lib/pq" // v1.10.3
"github.com/jmoiron/sqlx" // v1.3.4


func (pg *PG) Upsert(userID *uuid.UUID, location string, groupID *uuid.UUID) error {

    db := pg.DB() // returns *sqlx.DB

    tx, err := db.Beginx()
    if err != nil {
        return err
    }

    defer func() {
        if err != nil {
            tx.Rollback()
        } else {
            err = tx.Commit()
        }
    }()

    stmt := `
        insert into user_conn (
            user_id, location, group_id
        ) values (
            $1, $2, $3
        )
        on conflict (user_id, location, group_id) 
        do update set disconnected_at=null, unlinked_at=null, connected_at=now()
        returning *
    `

    err = tx.Get(cl, stmt, userID, location, groupID)
    if err != nil {            
        return err
    }

    _, err = tx.ExecContext(another statement on another table)
    if err != nil {            
        return err
    }

    return nil
}

What could be causing this issue?

The ON CONFLICT checks the primary key, not the unique constraint. — Frank Heikens, Nov 18 '21 at 20:47
a suggestion to test locally : try adding an artificial delay in that function before returning (e.g : `time.Sleep(10*time.Millisecond)`) to have a bigger window for conflicting transactions. — LeGEC, Nov 18 '21 at 21:25
You have `on conflict (user_id, location, group_id)` and then `do update set disconnected_at=null, unlinked_at=null, connected_at=now()`... How is the do-update supposed to fix or avoid the conflict if all it does is update columns that are *not* part of the set-of-column in the on-conflict clause? It's like saying `ON CONFLICT (id) DO UPDATE SET some_absolutely_unrelated_column=NULL`. — mkopriva, Nov 19 '21 at 05:57
what is the transaction isolation level ? is it the same on your test machine as in production ? — LeGEC, Nov 19 '21 at 11:13
@cID : regarding your schema : since `(user_id, location, group_id)` is already the primary key for this table, you don't need to create a unique constraint on `(location, user_id, group_id, coalesce(disconnected_at, '1970-01-01'))` -- `(location, user_id, group_id)` is already a unique key ... — LeGEC, Nov 19 '21 at 13:43

Postgres sqlx upsert causing pq: duplicate key value violates unique constraint

0 Answers0