Re: BUG #14291: Sequence ID gets modified even for "on conflict" update

mitramaddy(at)gmail(dot)com writes:
> Expected result: Since we are only doing updates in step 5, the "start at"
> for test_id_seq should remain at 2.
> Actual Result: Even though there are no inserts, the "start at" for
> test_id_seq increases to 6.

This is not a bug. See previous discussions at, eg,

https://www.postgresql.org/message-id/flat/20160105150227.1117.51692%40wrigleys.postgresql.org
https://www.postgresql.org/message-id/flat/20160506065528.2693.64808%40wrigleys.postgresql.org

The core reason why it's not a bug is that the INSERT is attempted in full
and only after detecting a conflict in the attempted unique-index
insertion does the code fall back to the ON CONFLICT path.

More generally, though, it's not a terribly good idea to assume that the
sequence of numbers obtained from a sequence object has no holes in it.
The description of nextval() at
https://www.postgresql.org/docs/9.5/static/functions-sequence.html
specifically disclaims this:

Important: To avoid blocking concurrent transactions that obtain
numbers from the same sequence, a nextval operation is never
rolled back; that is, once a value has been fetched it is
considered used and will not be returned again. This is true even
if the surrounding transaction later aborts, or if the calling
query ends up not using the value. For example an INSERT with an
ON CONFLICT clause will compute the to-be-inserted tuple,
including doing any required nextval calls, before detecting any
conflict that would cause it to follow the ON CONFLICT rule
instead. Such cases will leave unused "holes" in the sequence of
assigned values. Thus, PostgreSQL sequence objects cannot be used
to obtain "gapless" sequences.

regards, tom lane