Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVSiA-000GBM-Sb for pgsql-hackers@arkaria.postgresql.org; Mon, 14 Aug 2023 08:14:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qVShA-002Gaw-A5 for pgsql-hackers@arkaria.postgresql.org; Mon, 14 Aug 2023 08:13:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVSh9-002Gan-Sk for pgsql-hackers@lists.postgresql.org; Mon, 14 Aug 2023 08:13:00 +0000 Received: from oss.nttdata.com ([49.212.34.109]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVSh2-0002rE-0P for pgsql-hackers@lists.postgresql.org; Mon, 14 Aug 2023 08:12:58 +0000 Received: from oss.nttdata.com (localhost [127.0.0.1]) by oss.nttdata.com (Postfix) with ESMTPA id 3F951608F7; Mon, 14 Aug 2023 17:12:47 +0900 (JST) X-Virus-Status: Clean X-Virus-Scanned: clamav-milter 0.103.8 at oss.nttdata.com MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=_60d648cc95f94bd07e014c69fb92413c" Content-Transfer-Encoding: 7bit Date: Mon, 14 Aug 2023 17:12:47 +0900 From: Masahiro Ikeda To: Andres Freund Cc: Masahiko Sawada , pgsql-hackers@lists.postgresql.org, mitsuru.hinata.5432@gmail.com Subject: Re: Fix pg_stat_reset_single_table_counters function In-Reply-To: <20230812191207.aerskqox7aoan3ud@awork3.anarazel.de> References: <7cc69f863d9b1bc677544e3accd0e4b4@oss.nttdata.com> <9a4a6718abb47647ae83f1f4b3f9bf68@oss.nttdata.com> <20230812191207.aerskqox7aoan3ud@awork3.anarazel.de> User-Agent: Roundcube Webmail/1.4.11 Message-ID: <97c9f7a46681f051b1356e2987c3fd6c@oss.nttdata.com> X-Sender: ikedamsh@oss.nttdata.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_60d648cc95f94bd07e014c69fb92413c Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed Hi, On 2023-08-13 04:12, Andres Freund wrote: > On 2023-08-10 17:48:10 +0900, Masahiko Sawada wrote: >> Good catch! I've confirmed that the issue has been fixed by your >> patch. > > Indeed. Thanks for your responses! >> However, I'm not sure the added regression tests are stable since >> autovacuum workers may scan the pg_database and increment the >> statistics after resetting the stats. > > What about updating the table and checking the update count is reset? > That'd > not be reset by autovacuum. Yes. I confirmed that the stats are incremented by autovacuum as you said. I updated the patch to v3. * remove the code to bump the CATALOG_VERSION_NO because I misunderstood * change the test logic to check the update count instead of scan count I changed the table to check the stats from pg_database to pg_shdescription because the stats can update via the SQL interface COMMENT command. Regards, -- Masahiro Ikeda NTT DATA CORPORATION --=_60d648cc95f94bd07e014c69fb92413c Content-Transfer-Encoding: base64 Content-Type: text/x-diff; name=v3-0001-Fix-pg_stat_reset_single_table_counters-function.patch Content-Disposition: attachment; filename=v3-0001-Fix-pg_stat_reset_single_table_counters-function.patch; size=4227 RnJvbSAyMTVlZjhlZjY4YWYzMDc1M2NmY2Q0MzM2YjFmNmJkOTIwM2FjMDE0IE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBNYXNhaGlybyBJa2VkYSA8bXNoci5pa2VkYUBudHQuY29tPgpE YXRlOiBNb24sIDE0IEF1ZyAyMDIzIDE2OjQ4OjMwICswOTAwClN1YmplY3Q6IFtQQVRDSF0gRml4 IHBnX3N0YXRfcmVzZXRfc2luZ2xlX3RhYmxlX2NvdW50ZXJzIGZ1bmN0aW9uLgoKVGhpcyBjb21t aXQgcmV2aXZlcyB0aGUgZmVhdHVyZSB0byByZXNldCBzdGF0aXN0aWNzIGZvciBhIHNpbmdsZQpy ZWxhdGlvbiBzaGFyZWQgYWNyb3NzIGFsbCBkYXRhYmFzZXMgaW4gdGhlIGNsdXN0ZXIgdG8gemVy bywgd2hpY2gKd2FzIGltcGxlbWVudGVkIGJ5IHRoZSBmb2xsb3dpbmcgY29tbWl0LgoqIEVuaGFu Y2UgcGdfc3RhdF9yZXNldF9zaW5nbGVfdGFibGVfY291bnRlcnMgZnVuY3Rpb24oZTA0MjY3ODQ0 KQoKVGhlIGZvbGxvd2luZyBjb21taXQgYWNjaWRlbnRhbGx5IGRlbGV0ZWQgdGhlIGZlYXR1cmUu CiogcGdzdGF0OiBzdG9yZSBzdGF0aXN0aWNzIGluIHNoYXJlZCBtZW1vcnkoNTg5MWM3YThlKQoK TmVlZCB0byBiYWNrcGF0Y2ggZnJvbSAxNS4KClJlcG9ydGVkLWJ5OiBNaXRzdXJ1IEhpbmF0YQot LS0KIHNyYy9iYWNrZW5kL3V0aWxzL2FkdC9wZ3N0YXRmdW5jcy5jIHwgIDkgKysrKysrKy0tCiBz cmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3N0YXRzLm91dCB8IDMxICsrKysrKysrKysrKysrKysr KysrKysrKysrKysrCiBzcmMvdGVzdC9yZWdyZXNzL3NxbC9zdGF0cy5zcWwgICAgICB8IDEzICsr KysrKysrKysrKwogMyBmaWxlcyBjaGFuZ2VkLCA1MSBpbnNlcnRpb25zKCspLCAyIGRlbGV0aW9u cygtKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9wZ3N0YXRmdW5jcy5jIGIv c3JjL2JhY2tlbmQvdXRpbHMvYWR0L3Bnc3RhdGZ1bmNzLmMKaW5kZXggMmE0YzhlZjg3Zi4uMmI5 NzQyYWQyMSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3Bnc3RhdGZ1bmNzLmMK KysrIGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3Bnc3RhdGZ1bmNzLmMKQEAgLTE3LDYgKzE3LDcg QEAKICNpbmNsdWRlICJhY2Nlc3MvaHR1cF9kZXRhaWxzLmgiCiAjaW5jbHVkZSAiYWNjZXNzL3hs b2cuaCIKICNpbmNsdWRlICJhY2Nlc3MveGxvZ3ByZWZldGNoZXIuaCIKKyNpbmNsdWRlICJjYXRh bG9nL2NhdGFsb2cuaCIKICNpbmNsdWRlICJjYXRhbG9nL3BnX2F1dGhpZC5oIgogI2luY2x1ZGUg ImNhdGFsb2cvcGdfdHlwZS5oIgogI2luY2x1ZGUgImNvbW1vbi9pcC5oIgpAQCAtMTc3NiwxMyAr MTc3NywxNyBAQCBwZ19zdGF0X3Jlc2V0X3NoYXJlZChQR19GVU5DVElPTl9BUkdTKQogCVBHX1JF VFVSTl9WT0lEKCk7CiB9CiAKLS8qIFJlc2V0IGEgc2luZ2xlIGNvdW50ZXIgaW4gdGhlIGN1cnJl bnQgZGF0YWJhc2UgKi8KKy8qCisgKiBSZXNldCBhIHN0YXRpc3RpY3MgZm9yIGEgc2luZ2xlIG9i amVjdCwgd2hpY2ggbWF5IGJlIG9mIGN1cnJlbnQKKyAqIGRhdGFiYXNlIG9yIHNoYXJlZCBhY3Jv c3MgYWxsIGRhdGFiYXNlcyBpbiB0aGUgY2x1c3Rlci4KKyAqLwogRGF0dW0KIHBnX3N0YXRfcmVz ZXRfc2luZ2xlX3RhYmxlX2NvdW50ZXJzKFBHX0ZVTkNUSU9OX0FSR1MpCiB7CiAJT2lkCQkJdGFi b2lkID0gUEdfR0VUQVJHX09JRCgwKTsKKwlPaWQJCQlkYm9pZCA9IChJc1NoYXJlZFJlbGF0aW9u KHRhYm9pZCkgPyBJbnZhbGlkT2lkIDogTXlEYXRhYmFzZUlkKTsKIAotCXBnc3RhdF9yZXNldChQ R1NUQVRfS0lORF9SRUxBVElPTiwgTXlEYXRhYmFzZUlkLCB0YWJvaWQpOworCXBnc3RhdF9yZXNl dChQR1NUQVRfS0lORF9SRUxBVElPTiwgZGJvaWQsIHRhYm9pZCk7CiAKIAlQR19SRVRVUk5fVk9J RCgpOwogfQpkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9zdGF0cy5vdXQg Yi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3N0YXRzLm91dAppbmRleCAzMTkxNjRhNWU5Li4x MWNiODQxMzg2IDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3N0YXRzLm91 dAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3N0YXRzLm91dApAQCAtNzY0LDYgKzc2 NCwzNyBAQCBGUk9NIHBnX3N0YXRfYWxsX3RhYmxlcyBXSEVSRSByZWxpZCA9ICd0ZXN0X2xhc3Rf c2Nhbic6OnJlZ2NsYXNzOwogICAgICAgICAyIHwgdCAgICAgIHwgICAgICAgIDMgfCB0CiAoMSBy b3cpCiAKKy0tLS0tCistLSBUZXN0IHRvIHJlc2V0IHN0YXRzIGZvciBhIHRhYmxlIHNoYXJlZCBh Y3Jvc3MgYWxsIGRhdGFiYXNlcyAoZXguIHBnX3NoZGVzY3JpcHRpb24pCistLS0tLQorQkVHSU47 CitTRUxFQ1QgY3VycmVudF9kYXRhYmFzZSgpIGFzIGN1cnJlbnRfZGF0YWJhc2UgXGdzZXQKK0NP TU1FTlQgT04gREFUQUJBU0UgOmN1cnJlbnRfZGF0YWJhc2UgSVMgJ1RoaXMgaXMgYSB0ZXN0IGNv bW1lbnQnOyAgLS0gaW5zZXJ0IG9yIHVwZGF0ZSBpbiAncGdfc2hkZXNjcmlwdGlvbicKK1NFTEVD VCBwZ19zdGF0X2ZvcmNlX25leHRfZmx1c2goKTsKKyBwZ19zdGF0X2ZvcmNlX25leHRfZmx1c2gg CistLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQorIAorKDEgcm93KQorCitDT01NSVQ7CitTRUxF Q1Qgbl90dXBfaW5zICsgbl90dXBfdXBkID4gMCBGUk9NIHBnX3N0YXRfYWxsX3RhYmxlcyBXSEVS RSByZWxpZCA9ICdwZ19zaGRlc2NyaXB0aW9uJzo6cmVnY2xhc3M7CisgP2NvbHVtbj8gCistLS0t LS0tLS0tCisgdAorKDEgcm93KQorCitTRUxFQ1QgcGdfc3RhdF9yZXNldF9zaW5nbGVfdGFibGVf Y291bnRlcnMoJ3BnX3NoZGVzY3JpcHRpb24nOjpyZWdjbGFzcyk7CisgcGdfc3RhdF9yZXNldF9z aW5nbGVfdGFibGVfY291bnRlcnMgCistLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tCisgCisoMSByb3cpCisKK1NFTEVDVCBuX3R1cF9pbnMgKyBuX3R1cF91cGQgRlJPTSBwZ19z dGF0X2FsbF90YWJsZXMgV0hFUkUgcmVsaWQgPSAncGdfc2hkZXNjcmlwdGlvbic6OnJlZ2NsYXNz OworID9jb2x1bW4/IAorLS0tLS0tLS0tLQorICAgICAgICAwCisoMSByb3cpCisKIC0tLS0tCiAt LSBUZXN0IHRoYXQgdmFyaW91cyBzdGF0cyB2aWV3cyBhcmUgYmVpbmcgcHJvcGVybHkgcG9wdWxh dGVkCiAtLS0tLQpkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvc3RhdHMuc3FsIGIv c3JjL3Rlc3QvcmVncmVzcy9zcWwvc3RhdHMuc3FsCmluZGV4IDlhMTZkZjFjNDkuLmQxMTNhZWQy NTcgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL3N0YXRzLnNxbAorKysgYi9zcmMv dGVzdC9yZWdyZXNzL3NxbC9zdGF0cy5zcWwKQEAgLTM3Niw2ICszNzYsMTkgQEAgQ09NTUlUOwog U0VMRUNUIHNlcV9zY2FuLCA6J3Rlc3RfbGFzdF9zZXEnID0gbGFzdF9zZXFfc2NhbiBBUyBzZXFf b2ssIGlkeF9zY2FuLCA6J3Rlc3RfbGFzdF9pZHgnIDwgbGFzdF9pZHhfc2NhbiBBUyBpZHhfb2sK IEZST00gcGdfc3RhdF9hbGxfdGFibGVzIFdIRVJFIHJlbGlkID0gJ3Rlc3RfbGFzdF9zY2FuJzo6 cmVnY2xhc3M7CiAKKy0tLS0tCistLSBUZXN0IHRvIHJlc2V0IHN0YXRzIGZvciBhIHRhYmxlIHNo YXJlZCBhY3Jvc3MgYWxsIGRhdGFiYXNlcyAoZXguIHBnX3NoZGVzY3JpcHRpb24pCistLS0tLQor CitCRUdJTjsKK1NFTEVDVCBjdXJyZW50X2RhdGFiYXNlKCkgYXMgY3VycmVudF9kYXRhYmFzZSBc Z3NldAorQ09NTUVOVCBPTiBEQVRBQkFTRSA6Y3VycmVudF9kYXRhYmFzZSBJUyAnVGhpcyBpcyBh IHRlc3QgY29tbWVudCc7ICAtLSBpbnNlcnQgb3IgdXBkYXRlIGluICdwZ19zaGRlc2NyaXB0aW9u JworU0VMRUNUIHBnX3N0YXRfZm9yY2VfbmV4dF9mbHVzaCgpOworQ09NTUlUOworCitTRUxFQ1Qg bl90dXBfaW5zICsgbl90dXBfdXBkID4gMCBGUk9NIHBnX3N0YXRfYWxsX3RhYmxlcyBXSEVSRSBy ZWxpZCA9ICdwZ19zaGRlc2NyaXB0aW9uJzo6cmVnY2xhc3M7CitTRUxFQ1QgcGdfc3RhdF9yZXNl dF9zaW5nbGVfdGFibGVfY291bnRlcnMoJ3BnX3NoZGVzY3JpcHRpb24nOjpyZWdjbGFzcyk7CitT RUxFQ1Qgbl90dXBfaW5zICsgbl90dXBfdXBkIEZST00gcGdfc3RhdF9hbGxfdGFibGVzIFdIRVJF IHJlbGlkID0gJ3BnX3NoZGVzY3JpcHRpb24nOjpyZWdjbGFzczsKIAogLS0tLS0KIC0tIFRlc3Qg dGhhdCB2YXJpb3VzIHN0YXRzIHZpZXdzIGFyZSBiZWluZyBwcm9wZXJseSBwb3B1bGF0ZWQKLS0g CjIuMjUuMQoK --=_60d648cc95f94bd07e014c69fb92413c--