Thread: BUG #14360: snapshot too old bug? cann't reclaim dead tuple after has only xmin's transaction.
BUG #14360: snapshot too old bug? cann't reclaim dead tuple after has only xmin's transaction.
From
digoal@126.com
Date:
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM2MApMb2dnZWQgYnk6ICAg ICAgICAgIFpob3UgRGlnb2FsCkVtYWlsIGFkZHJlc3M6ICAgICAgZGlnb2Fs QDEyNi5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjYuMApPcGVyYXRpbmcg c3lzdGVtOiAgIENlbnRPUyA2LnggeDY0CkRlc2NyaXB0aW9uOiAgICAgICAg CgpgYGANCnNlc3Npb24gYTogIA0KcG9zdGdyZXM9IyBzZWxlY3QgcGdfc2xl ZXAoMTAwMCk7DQoNCnRoZW4gOiANCnNlc3Npb24gYjogIA0KcG9zdGdyZXM9 IyBpbnNlcnQgaW50byB0YmwxIHZhbHVlcyAoMSk7DQpJTlNFUlQgMCAxDQpw b3N0Z3Jlcz0jIGRlbGV0ZSBmcm9tIHRibDE7DQpERUxFVEUgMQ0KcG9zdGdy ZXM9IyBpbnNlcnQgaW50byB0YmwxIHZhbHVlcyAoMSk7DQpJTlNFUlQgMCAx DQpwb3N0Z3Jlcz0jIGRlbGV0ZSBmcm9tIHRibDE7DQpERUxFVEUgMQ0KDQph ZnRlciBvbGRfc25hcHNob3RfdGhyZXNob2xkOiANCmNhbm4ndCByZWNsYWlt IHRibDEncyBkZWFkIHR1cGxlDQoNCg0KYnV0IHdoZW4gaXQgc2NhbiB0Ymwx ICwgYW5kIHRoZW4gYWZ0ZXIgb2xkX3NuYXBzaG90X3RocmVzaG9sZCBhZ2Fp biwgaXQncyBvawpub3cuIA0KDQpwb3N0Z3Jlcz0jIHZhY3V1bSB2ZXJib3Nl IHRibDE7DQpJTkZPOiAgdmFjdXVtaW5nICJwdWJsaWMudGJsMSINCklORk86 ICAidGJsMSI6IGZvdW5kIDAgcmVtb3ZhYmxlLCAyIG5vbnJlbW92YWJsZSBy b3cgdmVyc2lvbnMgaW4gMSBvdXQgb2YgMQpwYWdlcw0KREVUQUlMOiAgMiBk ZWFkIHJvdyB2ZXJzaW9ucyBjYW5ub3QgYmUgcmVtb3ZlZCB5ZXQuDQpUaGVy ZSB3ZXJlIDIgdW51c2VkIGl0ZW0gcG9pbnRlcnMuDQpTa2lwcGVkIDAgcGFn ZXMgZHVlIHRvIGJ1ZmZlciBwaW5zLg0KMCBwYWdlcyBhcmUgZW50aXJlbHkg ZW1wdHkuDQpDUFUgMC4wMHMvMC4wMHUgc2VjIGVsYXBzZWQgMC4wMCBzZWMu DQpWQUNVVU0NCg0KDQpwb3N0Z3Jlcz0jIHZhY3V1bSB2ZXJib3NlIHRibDE7 DQpJTkZPOiAgdmFjdXVtaW5nICJwdWJsaWMudGJsMSINCklORk86ICAidGJs MSI6IGZvdW5kIDAgcmVtb3ZhYmxlLCAwIG5vbnJlbW92YWJsZSByb3cgdmVy c2lvbnMgaW4gMSBvdXQgb2YgMQpwYWdlcw0KREVUQUlMOiAgMCBkZWFkIHJv dyB2ZXJzaW9ucyBjYW5ub3QgYmUgcmVtb3ZlZCB5ZXQuDQpUaGVyZSB3ZXJl IDQgdW51c2VkIGl0ZW0gcG9pbnRlcnMuDQpTa2lwcGVkIDAgcGFnZXMgZHVl IHRvIGJ1ZmZlciBwaW5zLg0KMCBwYWdlcyBhcmUgZW50aXJlbHkgZW1wdHku DQpDUFUgMC4wMHMvMC4wMHUgc2VjIGVsYXBzZWQgMC4wMCBzZWMuDQpgYGAK Cg==
Re: BUG #14360: snapshot too old bug? cann't reclaim dead tuple after has only xmin's transaction.
From
Kevin Grittner
Date:
On Fri, Oct 7, 2016 at 9:54 AM, <digoal@126.com> wrote: > [tuple visible to "old snapshot" is vacuumed early, but not at > the first vacuum where the age could allow it] Not a bug. Note that the documentation here: https://www.postgresql.org/docs/9.6/static/runtime-config-resource.html#GUC-OLD-SNAPSHOT-THRESHOLD Says: | This setting does not attempt to guarantee that an error will be | generated under any particular circumstances. To perform the early cleanup more eagerly would require more locking of internal structures, which would cause a noticeable performance hit. Since the point of this setting is to improve performance (by limiting bloat), there is a trade-off; heuristics are used to balance the need for cleanup with the need to avoid as much lock contention as possible. If you have a realistic workload which allows enough bloat to have a significant affect on performance because this heuristic seems not to be striking the right balance, I would be interested in seeing the details; but this simple case seems to get there quickly enough to not pose such a risk. Even in such a case, it would likely fall more under the rubric of tuning than something that would be considered a bug. To be a bug, it would need to have some affect when disabled, prune away tuples modified by a transaction which is still active, generate an error on a snapshot not past the threshold, generate an error on access to a page which was not modified after the snapshot was taken, or something similar. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company