Обсуждение: BUG #14291: Sequence ID gets modified even for "on conflict" update

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDI5MQpMb2dnZWQgYnk6ICAg
ICAgICAgIE1hZGR5IEpvbmVzCkVtYWlsIGFkZHJlc3M6ICAgICAgbWl0cmFt
YWRkeUBnbWFpbC5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjUuNApPcGVy
YXRpbmcgc3lzdGVtOiAgIFdpbmRvd3MgMTAKRGVzY3JpcHRpb246ICAgICAg
ICAKCkhpLCBIZXJlIGFyZSB0aGUgc3RlcHMgdG8gcmVwbGljYXRlIHRoZSBi
dWc6DQoNClN0ZXAgMTogQ3JlYXRlIGEgc2ltcGxlIHRhYmxlDQpDUkVBVEUg
VEFCTEUgcHVibGljLnRlc3QNCigNCiAgdXNlcm5hbWUgdGV4dCBOT1QgTlVM
TCwNCiAgZnVsbG5hbWUgdGV4dCwNCiAgaWQgYmlnaW50IE5PVCBOVUxMIERF
RkFVTFQgbmV4dHZhbCgndGVzdF9pZF9zZXEnOjpyZWdjbGFzcyksDQogIENP
TlNUUkFJTlQgcHJpbWFyeV90ZXN0IFBSSU1BUlkgS0VZICh1c2VybmFtZSkN
CikNCg0KU3RlcCAyIC0gSGVyZSBpcyB0aGUgc2VxdWVuY2UgSUQ6DQoNCkNS
RUFURSBTRVFVRU5DRSBwdWJsaWMudGVzdF9pZF9zZXENCiAgSU5DUkVNRU5U
IDENCiAgTUlOVkFMVUUgMQ0KICBNQVhWQUxVRSA5MjIzMzcyMDM2ODU0Nzc1
ODA3DQogIFNUQVJUIDENCiAgQ0FDSEUgMTsNCg0KU3RlcCAzIC0gUnVuIHRo
ZSBmb2xsb3dpbmcgc2ltcGxlIFVQU0VSVCBTUUwgY29tbWFuZDoNCg0KSU5T
RVJUIElOVE8gdGVzdCAoVXNlcm5hbWUsRnVsbE5hbWUpIFZBTFVFUyAoJ2pv
aG4nLCdKb2huIEgnKSBPTgpDT05GTElDVChVc2VybmFtZSkgRE8gVXBkYXRl
IHNldCBGdWxsTmFtZT0nSm9obiBQJyB3aGVyZQp0ZXN0LlVzZXJuYW1lPSdq
b2huJzsNCg0KU3RlcCA0IC0gVGhpcyB3aWxsIGluY3JlYXNlIHRoZSAiU3Rh
cnQgYXQiIHZhbHVlIGluIHN0ZXAgMiBhYm92ZSBmb3IKdGVzdF9pZF9zZXEg
KHNpbmNlIHdlIGFyZSBkb2luZyBhIGJyYW5kIG5ldyBpbnNlcnQpLg0KDQpT
dGVwIDUgLSBOb3cgcnVuIHRoZSBFWEFDVCBzYW1lIFVQU0VSVCBjb21tYW5k
IGluIHN0ZXAgMyBmb3IgZml2ZSB0aW1lcyAob3IKbW9yZSkuIEluIHRhYmxl
IHRlc3QsIGl0IHdpbGwgY2hhbmdlIGZ1bGxuYW1lIHRvICJKb2huIFAiLg0K
DQpIb3dldmVyIHRoZXJlIGlzIGEgYnVnIGluIHRlc3RfaWRfc2VxIGJhY2tl
bmQNCg0KRXhwZWN0ZWQgcmVzdWx0OiBTaW5jZSB3ZSBhcmUgb25seSBkb2lu
ZyB1cGRhdGVzIGluIHN0ZXAgNSwgdGhlICJzdGFydCBhdCIKZm9yIHRlc3Rf
aWRfc2VxIHNob3VsZCByZW1haW4gYXQgMi4NCg0KQWN0dWFsIFJlc3VsdDog
RXZlbiB0aG91Z2ggdGhlcmUgYXJlIG5vIGluc2VydHMsIHRoZSAic3RhcnQg
YXQiIGZvcgp0ZXN0X2lkX3NlcSBpbmNyZWFzZXMgdG8gNi4NCg0KU3VtbWFy
eTogSXQgc2VlbXMgdGhhdCBQb3N0Z3Jlc3FsIGZpcnN0IHVwZGF0ZXMgdGVz
dF9pZF9zZXEgZXZlbiB0aG91Z2gKdGhlcmUgYXJlIG5vIGFjdHVhbCBpbnNl
cnRzIGhhcHBlbmluZy4gSXQgc2hvdWxkIG9ubHkgaW5jcmVtZW50IHRlc3Rf
aWRfc2VxCndoZW4gaXQgZG9lcyBhbiBhY3R1YWwgaW5zZXJ0Lg0KDQpJIGhh
dmUgYSBTUUwgVVBTRVJUIGNvbW1hbmQgd2hpY2ggcnVucyBhcm91bmQgMTAw
MCB0aW1lcy4gV2hhdCBpcyBoYXBwZW5pbmcKaXMgaWQgZm9yIGJyYW5kIG5l
dyBpbnNlcnQgaXMgMS4gVGhlbiBydW4gdGhlIHVwc2VydCBjb21tYW5kIDEw
MDAgdGltZXMuIE5vdwpkbyBhIGJyYW5kIG5ldyBpbnNlcnQuIFRoZSBpZCBm
b3IgdGhpcyBzaG91bGQgYmUgMi4gQnV0IGl0IGlzIGFjdHVhbGx5IDEwMDEu
ClRoaXMgaHVnZSBkaWZmZXJlbmNlIGluIGlkIGluIGp1c3QgdHdvIHJvd3Mg
c2VlbXMgb3V0IG9mIHBsYWNlLg0KDQpUZXN0ZWQgaW4gUG9zdGdyZXNxbCA5
LjUuNCBvbiBXaW5kb3dzIDEwLgoK

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