Re: BUG #13846: INSERT ON CONFLICT consumes sequencers on conflicts

On Tue, Jan 5, 2016 at 8:13 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>> When using an INSERT statement with an ON CONFLICT clause, if there is a
>> conflict, and the table being inserted into has a column defaulted to
>> nextval('seq'), the sequencer 'seq' is always incremented. This can quickly
>> and artificially consume all values of the sequencer; it behaves as if
>> conflict detection happens after inserts are attempted, rather than before.
>
> Yes. That's by design. You can't reliably do conflict detection before
> evaluating column default values.

Right. If you didn't consume a sequence value, but just did a
"peek-ahead", then several concurrently inserting sessions would all
"peek-ahead" and see the same value. There'd then be a race condition
that broke the useful guarantees that ON CONFLICT DO UPDATE makes.

--
Peter Geoghegan