onConflictDoNothing still incrementing primaryKey
I have the following ballots schema and then inserting to it via an Next.js app directory API route. I added not to do anything if there is a conflict. I thought I was doing it correctly as nothing was being inserted into the DB. However, when I inserted a new record and refreshed Supabase, I all of a sudden went from a primary key of 1 for the first record to a primary key of 8 for the 2nd record. Is there a way to not increment this primary key if there is a conflict? Am I possibly doing some checks wrong?
8 Replies
Hi @jsingleton37 ! I'm having a similar issue as well. Did you ever get to a solution here?
AFAIK this is the default behavior
Yes sequence is consumed the moment it tries to insert. It is also never guaranteed that numbers will increase by one (depends on db internal stuffs)
Ah! Very helpful, thank you both. Are there any common workarounds to prevent the serial id from incrementing in this case?
Is the best workaround to write two separate queries (one to queue up existing records) and then check against the result before insert/update? Feels a bit DB-expensive to do it that way, but I would guess that's how other ORMs (Django mostly in my experience) implements that under the hood>
Do you have any use case that requires that? The database can't guarantee that the next serial will last + 1
@Raphaël M (@rphlmr) ⚡ Main use case here is that we're bulk upserting lots of records via an ETL, and some of those records may be old and being flushed through again unnecessarily, so we would want all upserting to be idempotent without burning serial ids in those cases where we're getting the same data repetitively.
sounds like
insert...OnConflictDoUpdate then isn't the way to go since the serial id gets incremented right as the isnert is called even if it doesn't success and ends up performing an update! I'm coming from Django world where they've got update_or_create built-ins that I imagine do the separate queries in series (check for existing ones and then insert or udpate) under the hood, which I know is know the aim of Drizzle to provide!On conflict is a native sql feature (so the database do the check before inserting and will first increment serial).
If you check before insert you may slow down your bulk inserts 😬
@Raphaël M (@rphlmr) ⚡ totally! but then how else would you recommend doing this without burning serial ids when there are conflicts?