Thread: BUG #14357: BUG : old_snapshot_threshold no effect
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM1NwpMb2dnZWQgYnk6ICAg ICAgICAgIFpob3UgRGlnb2FsCkVtYWlsIGFkZHJlc3M6ICAgICAgZGlnb2Fs QDEyNi5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjYuMApPcGVyYXRpbmcg c3lzdGVtOiAgIENlbnRPUyA2LnggeDY0CkRlc2NyaXB0aW9uOiAgICAgICAg CgpJIHNldCBvbGRfc25hcHNob3RfdGhyZXNob2xkIHRvIDFtaW4sIGJ1dCBp dCdzIG5vIGVmZmVjdCB0byBwcmV2ZW50IGJsb2F0Pw0KYGBgDQpwb3N0Z3Jl cz0jIGNyZWF0ZSB0YWJsZSB0ZXN0MDEoaWQgaW50LCBpbmZvIHRleHQpOw0K Q1JFQVRFIFRBQkxFDQpwb3N0Z3Jlcz0jIGNyZWF0ZSB0YWJsZSB0ZXN0MDIo aWQgaW50LCBpbmZvIHRleHQpOw0KQ1JFQVRFIFRBQkxFDQpwb3N0Z3Jlcz0j IGJlZ2luOw0KQkVHSU4NCnBvc3RncmVzPSMgaW5zZXJ0IGludG8gdGVzdDAx IHZhbHVlcyAoMSk7DQpJTlNFUlQgMCAxDQpwb3N0Z3Jlcz0jIHNlbGVjdCBu b3coKTsNCiAgICAgICAgICAgICAgbm93ICAgICAgICAgICAgICANCi0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NCiAyMDE2LTEwLTA2IDAwOjE0 OjQxLjMzODgwNSswOA0KKDEgcm93KQ0KYGBgDQoNCmFub3RoZXIgc2Vzc2lv biA6IA0KYGBgDQpwb3N0Z3Jlcz0jIGluc2VydCBpbnRvIHRlc3QwMiBzZWxl Y3QgZ2VuZXJhdGVfc2VyaWVzKDEsMTAwMDApOw0KSU5TRVJUIDAgMTAwMDAN CnBvc3RncmVzPSMgZGVsZXRlIGZyb20gdGVzdDAyOw0KcG9zdGdyZXM9IyB2 YWN1dW0gdmVyYm9zZSB0ZXN0MDI7DQpJTkZPOiAgdmFjdXVtaW5nICJwdWJs aWMudGVzdDAyIg0KSU5GTzogICJ0ZXN0MDIiOiBmb3VuZCAwIHJlbW92YWJs ZSwgMTAwMDAgbm9ucmVtb3ZhYmxlIHJvdyB2ZXJzaW9ucyBpbiA0NQpvdXQg b2YgNDUgcGFnZXMNCkRFVEFJTDogIDEwMDAwIGRlYWQgcm93IHZlcnNpb25z IGNhbm5vdCBiZSByZW1vdmVkIHlldC4NClRoZXJlIHdlcmUgMCB1bnVzZWQg aXRlbSBwb2ludGVycy4NClNraXBwZWQgMCBwYWdlcyBkdWUgdG8gYnVmZmVy IHBpbnMuDQowIHBhZ2VzIGFyZSBlbnRpcmVseSBlbXB0eS4NCkNQVSAwLjAw cy8wLjAwdSBzZWMgZWxhcHNlZCAwLjAwIHNlYy4NCklORk86ICB2YWN1dW1p bmcgInBnX3RvYXN0LnBnX3RvYXN0Xzc1ODEzIg0KSU5GTzogIGluZGV4ICJw Z190b2FzdF83NTgxM19pbmRleCIgbm93IGNvbnRhaW5zIDAgcm93IHZlcnNp b25zIGluIDEgcGFnZXMNCkRFVEFJTDogIDAgaW5kZXggcm93IHZlcnNpb25z IHdlcmUgcmVtb3ZlZC4NCjAgaW5kZXggcGFnZXMgaGF2ZSBiZWVuIGRlbGV0 ZWQsIDAgYXJlIGN1cnJlbnRseSByZXVzYWJsZS4NCkNQVSAwLjAwcy8wLjAw dSBzZWMgZWxhcHNlZCAwLjAwIHNlYy4NCklORk86ICAicGdfdG9hc3RfNzU4 MTMiOiBmb3VuZCAwIHJlbW92YWJsZSwgMCBub25yZW1vdmFibGUgcm93IHZl cnNpb25zIGluIDAKb3V0IG9mIDAgcGFnZXMNCkRFVEFJTDogIDAgZGVhZCBy b3cgdmVyc2lvbnMgY2Fubm90IGJlIHJlbW92ZWQgeWV0Lg0KVGhlcmUgd2Vy ZSAwIHVudXNlZCBpdGVtIHBvaW50ZXJzLg0KU2tpcHBlZCAwIHBhZ2VzIGR1 ZSB0byBidWZmZXIgcGlucy4NCjAgcGFnZXMgYXJlIGVudGlyZWx5IGVtcHR5 Lg0KQ1BVIDAuMDBzLzAuMDB1IHNlYyBlbGFwc2VkIDAuMDAgc2VjLg0KVkFD VVVNDQoNCmxvbmcgdGltZSAsIGkgc2VlIHRoZSBhdXRvdmFjdXVtIHN0YXJ0 LCBhbmQgaSB1c2UgdmFjdXVtIGNvbW1hbmQgY2Fubid0CnBydW5pbmcgdGhl IGRlYWQgdHVwbGVzLg0KcG9zdGdyZXM9IyB2YWN1dW0gdmVyYm9zZSB0ZXN0 MDI7DQpJTkZPOiAgdmFjdXVtaW5nICJwdWJsaWMudGVzdDAyIg0KSU5GTzog ICJ0ZXN0MDIiOiBmb3VuZCAwIHJlbW92YWJsZSwgMjAwMDAgbm9ucmVtb3Zh YmxlIHJvdyB2ZXJzaW9ucyBpbiA4OQpvdXQgb2YgODkgcGFnZXMNCkRFVEFJ TDogIDIwMDAwIGRlYWQgcm93IHZlcnNpb25zIGNhbm5vdCBiZSByZW1vdmVk IHlldC4NClRoZXJlIHdlcmUgMCB1bnVzZWQgaXRlbSBwb2ludGVycy4NClNr aXBwZWQgMCBwYWdlcyBkdWUgdG8gYnVmZmVyIHBpbnMuDQowIHBhZ2VzIGFy ZSBlbnRpcmVseSBlbXB0eS4NCkNQVSAwLjAwcy8wLjAwdSBzZWMgZWxhcHNl ZCAwLjAwIHNlYy4NCklORk86ICB2YWN1dW1pbmcgInBnX3RvYXN0LnBnX3Rv YXN0Xzc1ODEzIg0KSU5GTzogIGluZGV4ICJwZ190b2FzdF83NTgxM19pbmRl eCIgbm93IGNvbnRhaW5zIDAgcm93IHZlcnNpb25zIGluIDEgcGFnZXMNCkRF VEFJTDogIDAgaW5kZXggcm93IHZlcnNpb25zIHdlcmUgcmVtb3ZlZC4NCjAg aW5kZXggcGFnZXMgaGF2ZSBiZWVuIGRlbGV0ZWQsIDAgYXJlIGN1cnJlbnRs eSByZXVzYWJsZS4NCkNQVSAwLjAwcy8wLjAwdSBzZWMgZWxhcHNlZCAwLjAw IHNlYy4NCklORk86ICAicGdfdG9hc3RfNzU4MTMiOiBmb3VuZCAwIHJlbW92 YWJsZSwgMCBub25yZW1vdmFibGUgcm93IHZlcnNpb25zIGluIDAKb3V0IG9m IDAgcGFnZXMNCkRFVEFJTDogIDAgZGVhZCByb3cgdmVyc2lvbnMgY2Fubm90 IGJlIHJlbW92ZWQgeWV0Lg0KVGhlcmUgd2VyZSAwIHVudXNlZCBpdGVtIHBv aW50ZXJzLg0KU2tpcHBlZCAwIHBhZ2VzIGR1ZSB0byBidWZmZXIgcGlucy4N CjAgcGFnZXMgYXJlIGVudGlyZWx5IGVtcHR5Lg0KQ1BVIDAuMDBzLzAuMDB1 IHNlYyBlbGFwc2VkIDAuMDAgc2VjLg0KVkFDVVVNDQpwb3N0Z3Jlcz0jIHNl bGVjdCAqIGZyb20gcGdfc3RhdF9hbGxfdGFibGVzIHdoZXJlIHJlbG5hbWUg PSd0ZXN0MDInOw0KLVsgUkVDT1JEIDEgXS0tLS0tLS0rLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tDQpyZWxpZCAgICAgICAgICAgICAgIHwgNzU4 MTMNCnNjaGVtYW5hbWUgICAgICAgICAgfCBwdWJsaWMNCnJlbG5hbWUgICAg ICAgICAgICAgfCB0ZXN0MDINCnNlcV9zY2FuICAgICAgICAgICAgfCAyDQpz ZXFfdHVwX3JlYWQgICAgICAgIHwgMjAwMDANCmlkeF9zY2FuICAgICAgICAg ICAgfCANCmlkeF90dXBfZmV0Y2ggICAgICAgfCANCm5fdHVwX2lucyAgICAg ICAgICAgfCAyMDAwMA0Kbl90dXBfdXBkICAgICAgICAgICB8IDANCm5fdHVw X2RlbCAgICAgICAgICAgfCAyMDAwMA0Kbl90dXBfaG90X3VwZCAgICAgICB8 IDANCm5fbGl2ZV90dXAgICAgICAgICAgfCAwDQpuX2RlYWRfdHVwICAgICAg ICAgIHwgMjAwMDANCm5fbW9kX3NpbmNlX2FuYWx5emUgfCAwDQpsYXN0X3Zh Y3V1bSAgICAgICAgIHwgMjAxNi0xMC0wNiAwMDoxODo0MS41MTA5NDErMDgN Cmxhc3RfYXV0b3ZhY3V1bSAgICAgfCAyMDE2LTEwLTA2IDAwOjE4OjM5LjY0 NDk3MyswOA0KbGFzdF9hbmFseXplICAgICAgICB8IA0KbGFzdF9hdXRvYW5h bHl6ZSAgICB8IDIwMTYtMTAtMDYgMDA6MTU6NDkuNjU1MDY2KzA4DQp2YWN1 dW1fY291bnQgICAgICAgIHwgNA0KYXV0b3ZhY3V1bV9jb3VudCAgICB8IDIy DQphbmFseXplX2NvdW50ICAgICAgIHwgMA0KYXV0b2FuYWx5emVfY291bnQg ICB8IDMNCmBgYA0KDQphbmQgc2Vzc2lvbiBhIGNhbiBhbHNvIHZpc2l0b3Ig dGhlIHBhZ2VzIHdpdGggdGVzdDAyOiAgDQpgYGANCnBvc3RncmVzPSMgc2Vs ZWN0ICogZnJvbSB0ZXN0MDE7DQogaWQgfCBpbmZvIA0KLS0tLSstLS0tLS0N CiAgMSB8IA0KKDEgcm93KQ0KDQpwb3N0Z3Jlcz0jIHNlbGVjdCAqIGZyb20g dGVzdDAyOw0KIGlkIHwgaW5mbyANCi0tLS0rLS0tLS0tDQooMCByb3dzKQ0K YGBgDQoNCndoeSA/ICBJdCdzIGEgYnVnPyAgCgo=
On Wed, Oct 5, 2016 at 11:21 AM, <digoal@126.com> wrote: > I set old_snapshot_threshold to 1min, but it's no effect to prevent bloat? > BEGIN > postgres=# insert into test01 values (1); > INSERT 0 1 > [no COMMIT or ROLLBACK of this transaction] > another session : > [other modifications and vacuums] > [no early cleanup or "snapshot too old" error] > why ? It's a bug? This feature does not attempt to perform early cleanup on (or past) a transaction which has acquired a transaction ID (normally through data modification) and which has not completed. In other words, it helps with long-running read-only transactions, but a long-running transaction which writes can still cause bloat. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company