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

mitramaddy@gmail.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