1

When a conflict occurs on insert into a table with an auto incrementing id column, the sequence gets bumped causing a gap in the id range, which for me is undesirable.

Here is a simplified version of my situation:

create table tab1 (
    id serial,
    col1 int,
    col2 int,
    col3 int,
    constraint const1 unique (col1, col2)
);

In a stored proc:

insert into tab1 (col1, col2, col3)
values (1, 2, 3)
on conflict on constraint const1
do update set col3 = excluded.col3

If there's a collision, the insert ... on conflict ... update works fine, except the next value from the sequence is burned.

Without doing an exists() check first, is there a way to not burn the next value from the sequence using just a single statement?

Note: There is no chance of a race condition of concurrent updates for the same conflict key.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 2
    https://stackoverflow.com/questions/63720340/postgres-prevent-serial-incrementation-with-on-conflict-do-nothing –  Mar 08 '22 at 06:06
  • @a_horse It's not a dupe. Being `do update` rather than `do nothing`, there is a new alternative for a workaround. See answer. – Bohemian Mar 08 '22 at 07:14
  • Note that whatever solution you find, it is very likely to have very bad performance compared to simple value discarding. Additionally I find it weird that this is "undesirable". Do you have a logic that depends on the fact that there are no gaps in ids? That's bad. – freakish Mar 08 '22 at 07:19

1 Answers1

-2

There's no way to avoid the increment of the sequence, because it happens before the conflict is detected.

Here's the work around I used:

insert into tab1 (col1, col2, col3)
select x.*
from (select 1 a, 2 b, 3 c) x
left join tab1 o on o.col1 = x.a and o.col2 = x.b
where o.col1 is null
returning tab1.id into _id;

if _id is null then
    update tab1 set
    col3 = 3
    where col1 = 1
    and col2 = 2;
end if;
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • This does something different than your original statement –  Mar 08 '22 at 07:17
  • 1
    And probably fails in concurrent scenarios and in the event of a transaction failing/rolling back. – Richard Huxton Mar 08 '22 at 07:18
  • @a_horse_with_no_name I'm a dufus - I failed to mention I am in stored proc land. This is actually how I solved it (edited). There is no chance of a race condition of concurrent updates for the same conflict key. – Bohemian Mar 08 '22 at 07:24
  • @Bohemian: You'd better test this one, because a concurrent insert will fail. And because of the failure and lack of error handling, you can't do the update anymore. – Frank Heikens Mar 08 '22 at 08:15
  • @frank what do you mean exactly? As per question, there will never be a concurrent operation on any given col1/col2 pair. There are several operations in the one transaction on a given key pair, but never in parallel. There will be no errors, so rollback will never happen. – Bohemian Mar 08 '22 at 09:44
  • If that's the case, why do you need the unique constraint? – Frank Heikens Mar 08 '22 at 10:06
  • @frank a unique constraint is at least implied by the nature of the query in the question. An index is needed for reasonable performance on conflict detection. The formal constraint, while not strictly needed kills these two birds with one stone. For context, in the actual situation is an [event outbox pattern](https://microservices.io/patterns/data/transactional-outbox.html) where col1 is entiry_id, col2 is timestamp, col3 is entity state (final state after all updates/inserts in the transaction) and id is the id of the event to be published. It’s working great too! – Bohemian Mar 08 '22 at 10:21