Thread: 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