Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kcAIG-0006SA-45 for pgsql-hackers@arkaria.postgresql.org; Mon, 09 Nov 2020 16:45:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kcAIF-00005j-1r for pgsql-hackers@arkaria.postgresql.org; Mon, 09 Nov 2020 16:45:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kcAIE-000051-N9 for pgsql-hackers@lists.postgresql.org; Mon, 09 Nov 2020 16:45:22 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kcAIB-0003ae-Tn for pgsql-hackers@lists.postgresql.org; Mon, 09 Nov 2020 16:45:22 +0000 Received: from mail.postgrespro.ru (cyclops.postgrespro.ru [93.174.131.138]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id EEF1321C1738; Mon, 9 Nov 2020 19:45:18 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1604940319; bh=IsDGZVdY83cCrffOkZlAF3/nOKgJb5Em5URrHZAP+V0=; h=Date:From:To:Cc:Subject; b=WUtfMhty+mOW2gRbBfZJTpJlWkZ6aW580Fx8LpYbecVAimvmFaCLBOYHSghg5YHvf ijT9TUOj24KOdp8In40XNdy5Zawb7Rd3t1siXXW9W7qU4+SGnZddOHk93KO57lOyVh +lDaaXkjWPashYjBcMWREhACOBHVWW3cLBfHOQLA= MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=_fa77866419adc5f5b3e9c11dc21db62f" Content-Transfer-Encoding: 7bit Date: Mon, 09 Nov 2020 19:45:18 +0300 From: Alexey Kondratov To: PostgreSQL Hackers Cc: Robert Haas Subject: Misuse of TimestampDifference() in the autoprewarm feature of pg_prewarm User-Agent: Roundcube Webmail/1.4.0 Message-ID: <3b1c053a21c07c1ed5e00be3b2b855ef@postgrespro.ru> X-Sender: a.kondratov@postgrespro.ru List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --=_fa77866419adc5f5b3e9c11dc21db62f Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed Hi Hackers, Today I have accidentally noticed that autoprewarm feature of pg_prewarm used TimestampDifference()'s results in a wrong way. First, it used *seconds* result from it as a *milliseconds*. It was causing it to make dump file autoprewarm.blocks ~every second with default setting of autoprewarm_interval = 300s. Here is a log part with debug output in this case: ``` 2020-11-09 19:09:00.162 MSK [85328] LOG: dumping autoprewarm.blocks 2020-11-09 19:09:01.161 MSK [85328] LOG: dumping autoprewarm.blocks 2020-11-09 19:09:02.160 MSK [85328] LOG: dumping autoprewarm.blocks 2020-11-09 19:09:03.159 MSK [85328] LOG: dumping autoprewarm.blocks ``` After fixing this issue I have noticed that it still dumps blocks twice at each timeout (here I set autoprewarm_interval to 15s): ``` 2020-11-09 19:18:59.692 MSK [85662] LOG: dumping autoprewarm.blocks 2020-11-09 19:18:59.700 MSK [85662] LOG: dumping autoprewarm.blocks 2020-11-09 19:19:14.694 MSK [85662] LOG: dumping autoprewarm.blocks 2020-11-09 19:19:14.704 MSK [85662] LOG: dumping autoprewarm.blocks ``` This happens because at timeout time we were using continue, but actually we still have to wait the entire autoprewarm_interval after successful dumping. I have fixed both issues in the attached patches and also added a minimalistic tap test as a first one to verify that this automatic damping still works after refactoring. I put Robert into CC, since he is an author of this feature. What do you think? Regards -- Alexey Kondratov Postgres Professional https://www.postgrespro.com Russian Postgres Company --=_fa77866419adc5f5b3e9c11dc21db62f Content-Transfer-Encoding: base64 Content-Type: text/x-diff; name=v1-0003-pg_prewarm-refactor-autoprewarm-waits.patch Content-Disposition: attachment; filename=v1-0003-pg_prewarm-refactor-autoprewarm-waits.patch; size=892 RnJvbSA2ZDRiYWI3ZjIxYzM2NjFkZDRkZDVhMGRlN2UwOTdiMWRlM2Y2NDJjIE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBBbGV4ZXkgS29uZHJhdG92IDxrb25kcmF0b3YuYWxla3NleUBn bWFpbC5jb20+CkRhdGU6IE1vbiwgOSBOb3YgMjAyMCAxOToyNDo1NSArMDMwMApTdWJqZWN0OiBb UEFUQ0ggdjEgMy8zXSBwZ19wcmV3YXJtOiByZWZhY3RvciBhdXRvcHJld2FybSB3YWl0cwoKUHJl dmlvdXNseSBpdCB3YXMgZHVtcGluZyB0d2ljZSBhdCBldmVyeSB0aW1lb3V0IHRpbWUuCi0tLQog Y29udHJpYi9wZ19wcmV3YXJtL2F1dG9wcmV3YXJtLmMgfCA0ICsrKy0KIDEgZmlsZSBjaGFuZ2Vk LCAzIGluc2VydGlvbnMoKyksIDEgZGVsZXRpb24oLSkKCmRpZmYgLS1naXQgYS9jb250cmliL3Bn X3ByZXdhcm0vYXV0b3ByZXdhcm0uYyBiL2NvbnRyaWIvcGdfcHJld2FybS9hdXRvcHJld2FybS5j CmluZGV4IGIxOGEwNjVlZDUuLmY1MmM4M2RlMWUgMTAwNjQ0Ci0tLSBhL2NvbnRyaWIvcGdfcHJl d2FybS9hdXRvcHJld2FybS5jCisrKyBiL2NvbnRyaWIvcGdfcHJld2FybS9hdXRvcHJld2FybS5j CkBAIC0yMzgsNyArMjM4LDkgQEAgYXV0b3ByZXdhcm1fbWFpbihEYXR1bSBtYWluX2FyZykKIAkJ CXsKIAkJCQlsYXN0X2R1bXBfdGltZSA9IEdldEN1cnJlbnRUaW1lc3RhbXAoKTsKIAkJCQlhcHdf ZHVtcF9ub3codHJ1ZSwgZmFsc2UpOwotCQkJCWNvbnRpbnVlOworCisJCQkJLyogV2UgaGF2ZSB0 byBzbGVlcCBldmVuIGFmdGVyIGEgc3VjY2Vzc2Z1bGwgZHVtcCAqLworCQkJCWRlbGF5X2luX21z ID0gYXV0b3ByZXdhcm1faW50ZXJ2YWwgKiAxMDAwOwogCQkJfQogCiAJCQkvKiBTbGVlcCB1bnRp bCB0aGUgbmV4dCBkdW1wIHRpbWUuICovCi0tIAoyLjE5LjEKCg== --=_fa77866419adc5f5b3e9c11dc21db62f Content-Transfer-Encoding: base64 Content-Type: text/x-diff; name=v1-0002-pg_prewarm-fix-autoprewarm_interval-behaviour.patch Content-Disposition: attachment; filename=v1-0002-pg_prewarm-fix-autoprewarm_interval-behaviour.patch; size=1046 RnJvbSA4NzkzYjhiZWI2YTVjMWFlNzMwZjFmZmZiMDlkZmY2NGM4M2JjNjMxIE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBBbGV4ZXkgS29uZHJhdG92IDxrb25kcmF0b3YuYWxla3NleUBn bWFpbC5jb20+CkRhdGU6IE1vbiwgOSBOb3YgMjAyMCAxOToxMjowMCArMDMwMApTdWJqZWN0OiBb UEFUQ0ggdjEgMi8zXSBwZ19wcmV3YXJtOiBmaXggYXV0b3ByZXdhcm1faW50ZXJ2YWwgYmVoYXZp b3VyLgoKUHJldmlvdXNseSBpdCBtaXN1c2VkIHNlY29uZHMgZnJvbSBUaW1lc3RhbXBEaWZmZXJl bmNlKCkgYXMKbWlsbGlzZWNvbmRzLCBzbyBpdCB3YXMgZHVtcGluZyBhdXRvcHJld2FybS5ibG9j a3MgfmV2ZXJ5IHNlY29uZApldmVudCB3aXRoIGRlZmF1bHQgYXV0b3ByZXdhcm1faW50ZXJ2YWwg PSAzMDBzLgotLS0KIGNvbnRyaWIvcGdfcHJld2FybS9hdXRvcHJld2FybS5jIHwgMiArLQogMSBm aWxlIGNoYW5nZWQsIDEgaW5zZXJ0aW9uKCspLCAxIGRlbGV0aW9uKC0pCgpkaWZmIC0tZ2l0IGEv Y29udHJpYi9wZ19wcmV3YXJtL2F1dG9wcmV3YXJtLmMgYi9jb250cmliL3BnX3ByZXdhcm0vYXV0 b3ByZXdhcm0uYwppbmRleCBkM2RlYzZlM2VjLi5iMThhMDY1ZWQ1IDEwMDY0NAotLS0gYS9jb250 cmliL3BnX3ByZXdhcm0vYXV0b3ByZXdhcm0uYworKysgYi9jb250cmliL3BnX3ByZXdhcm0vYXV0 b3ByZXdhcm0uYwpAQCAtMjMxLDcgKzIzMSw3IEBAIGF1dG9wcmV3YXJtX21haW4oRGF0dW0gbWFp bl9hcmcpCiAJCQkJCQkJCQkJCWF1dG9wcmV3YXJtX2ludGVydmFsICogMTAwMCk7CiAJCQlUaW1l c3RhbXBEaWZmZXJlbmNlKEdldEN1cnJlbnRUaW1lc3RhbXAoKSwgbmV4dF9kdW1wX3RpbWUsCiAJ CQkJCQkJCSZzZWNzLCAmdXNlY3MpOwotCQkJZGVsYXlfaW5fbXMgPSBzZWNzICsgKHVzZWNzIC8g MTAwMCk7CisJCQlkZWxheV9pbl9tcyA9IHNlY3MgKiAxMDAwICsgKHVzZWNzIC8gMTAwMCk7CiAK IAkJCS8qIFBlcmZvcm0gYSBkdW1wIGlmIGl0J3MgdGltZS4gKi8KIAkJCWlmIChkZWxheV9pbl9t cyA8PSAwKQotLSAKMi4xOS4xCgo= --=_fa77866419adc5f5b3e9c11dc21db62f Content-Transfer-Encoding: base64 Content-Type: text/x-diff; name=v1-0001-pg_prewarm-add-tap-test-for-autoprewarm-feature.patch Content-Disposition: attachment; filename=v1-0001-pg_prewarm-add-tap-test-for-autoprewarm-feature.patch; size=2426 RnJvbSAzMWRjMzBjOTc4NjFhZmFlOWMzNDg1MmFmYzVhNWIxYzkxYmJlYWRjIE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBBbGV4ZXkgS29uZHJhdG92IDxrb25kcmF0b3YuYWxla3NleUBn bWFpbC5jb20+CkRhdGU6IE1vbiwgOSBOb3YgMjAyMCAxOTowNDoxMCArMDMwMApTdWJqZWN0OiBb UEFUQ0ggdjEgMS8zXSBwZ19wcmV3YXJtOiBhZGQgdGFwIHRlc3QgZm9yIGF1dG9wcmV3YXJtIGZl YXR1cmUKCi0tLQogY29udHJpYi9wZ19wcmV3YXJtL01ha2VmaWxlICAgICAgICAgICAgIHwgIDIg KwogY29udHJpYi9wZ19wcmV3YXJtL3QvMDAxX2F1dG9wcmV3YXJtLnBsIHwgNTEgKysrKysrKysr KysrKysrKysrKysrKysrKwogMiBmaWxlcyBjaGFuZ2VkLCA1MyBpbnNlcnRpb25zKCspCiBjcmVh dGUgbW9kZSAxMDA2NDQgY29udHJpYi9wZ19wcmV3YXJtL3QvMDAxX2F1dG9wcmV3YXJtLnBsCgpk aWZmIC0tZ2l0IGEvY29udHJpYi9wZ19wcmV3YXJtL01ha2VmaWxlIGIvY29udHJpYi9wZ19wcmV3 YXJtL01ha2VmaWxlCmluZGV4IGIxM2FjM2M4MTMuLjljZmRlOGM0ZTQgMTAwNjQ0Ci0tLSBhL2Nv bnRyaWIvcGdfcHJld2FybS9NYWtlZmlsZQorKysgYi9jb250cmliL3BnX3ByZXdhcm0vTWFrZWZp bGUKQEAgLTEwLDYgKzEwLDggQEAgRVhURU5TSU9OID0gcGdfcHJld2FybQogREFUQSA9IHBnX3By ZXdhcm0tLTEuMS0tMS4yLnNxbCBwZ19wcmV3YXJtLS0xLjEuc3FsIHBnX3ByZXdhcm0tLTEuMC0t MS4xLnNxbAogUEdGSUxFREVTQyA9ICJwZ19wcmV3YXJtIC0gcHJlbG9hZCByZWxhdGlvbiBkYXRh IGludG8gc3lzdGVtIGJ1ZmZlciBjYWNoZSIKIAorVEFQX1RFU1RTID0gMQorCiBpZmRlZiBVU0Vf UEdYUwogUEdfQ09ORklHID0gcGdfY29uZmlnCiBQR1hTIDo9ICQoc2hlbGwgJChQR19DT05GSUcp IC0tcGd4cykKZGlmZiAtLWdpdCBhL2NvbnRyaWIvcGdfcHJld2FybS90LzAwMV9hdXRvcHJld2Fy bS5wbCBiL2NvbnRyaWIvcGdfcHJld2FybS90LzAwMV9hdXRvcHJld2FybS5wbApuZXcgZmlsZSBt b2RlIDEwMDY0NAppbmRleCAwMDAwMDAwMDAwLi5iNTY0YzI5OTMxCi0tLSAvZGV2L251bGwKKysr IGIvY29udHJpYi9wZ19wcmV3YXJtL3QvMDAxX2F1dG9wcmV3YXJtLnBsCkBAIC0wLDAgKzEsNTEg QEAKKyMKKyMgQ2hlY2sgdGhhdCBwZ19wcmV3YXJtIGNhbiBkdW1wIGJsb2NrcyBmcm9tIHNoYXJl ZCBidWZmZXJzCisjIHRvIFBHREFUQS9hdXRvcHJld2FybS5ibG9ja3MuCisjCisKK3VzZSBzdHJp Y3Q7Cit1c2UgVGVzdDo6TW9yZTsKK3VzZSBUZXN0TGliOwordXNlIFRpbWU6OkhpUmVzIHF3KHVz bGVlcCk7Cit1c2Ugd2FybmluZ3M7CisKK3VzZSBQb3N0Z3Jlc05vZGU7CisKK3BsYW4gdGVzdHMg PT4gMzsKKworbXkgJG5vZGUgPSBnZXRfbmV3X25vZGUoIm5vZGUiKTsKKyRub2RlLT5pbml0Owor JG5vZGUtPmFwcGVuZF9jb25mKAorICAgICdwb3N0Z3Jlc3FsLmNvbmYnLCBxcSgKK3NoYXJlZF9w cmVsb2FkX2xpYnJhcmllcyA9ICdwZ19wcmV3YXJtJworcGdfcHJld2FybS5hdXRvcHJld2FybSA9 ICdvbicKK3BnX3ByZXdhcm0uYXV0b3ByZXdhcm1faW50ZXJ2YWwgPSAxCispKTsKKyRub2RlLT5z dGFydDsKKworbXkgJGJsb2Nrc19wYXRoID0gJG5vZGUtPmRhdGFfZGlyIC4gJy9hdXRvcHJld2Fy bS5ibG9ja3MnOworCisjIENoZWNrIHRoYXQgd2UgY2FuIGR1bXAgYmxvY2tzIG9uIHRpbWVvdXQu CisjIFdhaXQgdXAgdG8gMTgwcyBmb3IgcGdfcHJld2FybSB0byBkdW1wIGJsb2Nrcy4KK2ZvcmVh Y2ggbXkgJGkgKDAgLi4gMTgwMCkKK3sKKwlsYXN0IGlmIC1lICRibG9ja3NfcGF0aDsKKwl1c2xl ZXAoMTAwXzAwMCk7Cit9CitvaygtZSAkYmxvY2tzX3BhdGgsICdmaWxlIGF1dG9wcmV3YXJtLmJs b2NrcyBzaG91bGQgYmUgcHJlc2VudCBpbiB0aGUgUEdEQVRBJyk7CisKKyMgQ2hlY2sgdGhhdCB3 ZSBjYW4gZHVtcCBibG9ja3Mgb24gc2h1dGRvd24uCiskbm9kZS0+c3RvcDsKKyRub2RlLT5hcHBl bmRfY29uZigKKyAgICAncG9zdGdyZXNxbC5jb25mJywgcXEoCitwZ19wcmV3YXJtLmF1dG9wcmV3 YXJtX2ludGVydmFsID0gMAorKSk7CisKKyMgUmVtb3ZlIGF1dG9wcmV3YXJtLmJsb2NrcwordW5s aW5rKCRibG9ja3NfcGF0aCkgfHwgZGllICIkYmxvY2tzX3BhdGg6ICQhIjsKK29rKCEtZSAkYmxv Y2tzX3BhdGgsICdzYW5pdHkgY2hlY2ssIGR1bXAgb24gdGltZW91dCBpcyB0dXJuZWQgb2ZmJyk7 CisKKyRub2RlLT5zdGFydDsKKyRub2RlLT5zdG9wOworCitvaygtZSAkYmxvY2tzX3BhdGgsICdm aWxlIGF1dG9wcmV3YXJtLmJsb2NrcyBzaG91bGQgYmUgcHJlc2VudCBpbiB0aGUgUEdEQVRBIGFm dGVyIGNsZWFuIHNodXRkb3duJyk7CgpiYXNlLWNvbW1pdDogOGYxMTM2OThiNjNiMTVhNGUwYTRi MTVkM2VlMzcyMzhjMWQxODIxZAotLSAKMi4xOS4xCgo= --=_fa77866419adc5f5b3e9c11dc21db62f--