Primary key violation when calling save() (Postgres)

[fossil aspen]

Hi everyone!
I’m working on an API where the client generates the UUIDs for entities, and the backend should either create or update the entity with that ID. However, I’m running into a strange issue:
• When I fetch the entity by ID, sometimes it returns nil (meaning the backend thinks it doesn’t exist).
• So I create a new model instance with that ID and call save().
• But Postgres throws a primary key violation error, which suggests the record does exist.

I tried implementing a “safe retry” function that catches this duplicate key error, then fetches the entity again, and calls update() to apply changes. However, this crashes because Fluent performs a precondition check on the model’s ID property (which is wrapped in a property wrapper), and this check expects the model to already be “tracked” as existing.

My question is:
Is there a recommended Fluent pattern or approach for safely doing a create-or-update by client-generated ID that handles this race condition? Or is there a way to bypass or reset that ID “existence” precondition so I can safely call update() after catching the conflict?

Thanks in advance!

[tropic forum]

If you’re generating your own identifiers, have you tried to use @ID(custom: "id", generatedBy: .user) on the Model as well as .field("id", .uuid, .identifier(auto: false) in your schema?

If that’s still not working you could always just try to get the model first then choose to create or update yourself?

Or check $id.exists perhaps?

[fossil aspen]

I wasn't aware of the "generatedBy" argument. I still don't really understand how this sort of race condition could be happening though, as I always first try to fetch an existing model from the database and the query by id returns nil just for Postgres to later complain when Fluent attempts to save the model.

[solemn veldt]

Im assuming this is reproducible? If you're getting primary key violations then the only way that can happen is if the item already exists in the DB. Now you could be hitting a race condition where another requests is run in parallel and saves before the current request. But the chances of hitting a duplicate UUID should be practically impossible if you're using a proper UUID type

[tropic forum]

Want to share some code? What entity are you calling save on? The one you tried to fetch or the one you just created? And are you definitely only doing so once?

[fossil aspen]

This is from one of the failing requests. The only thing I can think of is the client somehow firing two requests at once and then one of the requests manages do write to the db after the other request's query, but before the save?

'''
let habit = try await Habit.query(on: req.db)
.with(.$user)
.with(.$repetition)
.filter(.$id, .equal, habitID)
.first() ?? Habit()

   let repetition: Repetition

    if habit.id != nil {
        if habit.$user.id != user.id {
            throw AppError(
                .unauthorized,
                reason: "Mismatching user id",
                errorCode: ErrorCodes.mismatchingUserID.rawValue
            )
        }

        repetition = habit.repetition.first ?? Repetition()
    } else {
        repetition = Repetition()
    }

    habit.id = habitID
    habit.name = createOrUpdate.name
    habit.sortOrder = createOrUpdate.sortOrder ?? 0
    habit.isArchived = createOrUpdate.isArchived
    habit.numberOfCompletionsADay = createOrUpdate.numberOfCompletionsADay
    habit.startDate = createOrUpdate.startDate.date ?? Date()
    habit.period = createOrUpdate.period
    habit.isHealthKitHabit = createOrUpdate.healthDataType != nil
    habit.healthUnit = createOrUpdate.healthUnit
    habit.healthDataType = createOrUpdate.healthDataType
    habit.healthGoal = createOrUpdate.healthGoal
    habit.habitTimes = createOrUpdate.habitTimes
    habit.sendReminders = createOrUpdate.sendReminders ?? true
    habit.reminderOffset = createOrUpdate.reminderOffset
    habit.updatedAt = Date()

    try await habit.save(on: req.db)

'''

[solemn veldt]

If you set the log level to debug you should be able to see the DB requests

[fossil aspen]

Thanks @solemn veldt ! I will try that and will post back as soon as I have some logs!

[ornate hamlet]

@solemn veldt now has 4061 !

[tropic forum]

Okay a couple of points - you shouldn’t be reassigning habit.id when the habit has already been found. Even if the two IDs are equal when compared, the reassignment may well be triggering some underlying logic that you’re not aware of. Secondly, why not use a @Timestamp field for updatedAt? Seems unnecessary to be setting this yourself.

I suspect it’s that reassignment which is making your updates falsely act like creates

[tropic forum]

Yes I’ve been able to reproduce - setting habit.id on an already found entity ends up breaking save() as save() uses self.anyID from here.

https://github.com/vapor/fluent-kit/blob/main/Sources/FluentKit/Model/AnyModel.swift

Just refactor your code to only assign the id if it’s a new model. Ideally actually I’d suggest refactoring to handle updates and creates separately altogether

Then as a little extra, you could use something like this to only actually touch properties during an update if they’re different from the existing value.

extension Fields {
    public func updateIfChanged<Field: Equatable>(
        _ modelKeyPath: ReferenceWritableKeyPath<Self, Field>,
        from dtoValue: Field?
    ) throws {
        if let dtoValue, self[keyPath: modelKeyPath] != dtoValue {
            self[keyPath: modelKeyPath] = dtoValue
        }
    }
}

That way your updatedAt timestamps will only be triggered when an actual change is made. Rather than whenever you update irregardless

[tropic forum]

The optionality of dtoValue: Field? is deliberate to allow UpdateDTOs to be modelled with all properties as optional (like a Partial) as per HTTP PATCH semantics

[tropic forum]

If you wanted the quickest dirtiest way to confirm that this is the problem, I’m guessing you could just change habit.id = habitID for if habit.id == nil { habit.id = habitID }

[fossil aspen]

Apologies for the delay here, I got caught up with something. This would make sense, but I didn't really manage to reproduce that here. When testing setting the same id to a model I've just fetched from the database, the save operation works. Nevertheless, I've changed my codebase to remove that assignment to the id field and I am still getting the random primary key violation on my logs on that method.

[solemn veldt]

Do you see the DB calls to show double inserts?

[fossil aspen]

@solemn veldt, I was able to narrow it down to client error. I do see two inserts in very quick succession, but they come from different requests.

[solemn veldt]

So your client is sending 2 requests asking the backend to create 2 models with the same ID