[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-bugs
Subject:    Re: [BUGS] BUG #14291: Sequence ID gets modified even for "on conflict" update
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2016-08-21 0:06:29
Message-ID: 9545.1471737989 () sss ! pgh ! pa ! us
[Download RAW message or body]

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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[prev in list] [next in list] [prev in thread] [next in thread]