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 1qQioe-000lxH-Lg for pgsql-hackers@arkaria.postgresql.org; Tue, 01 Aug 2023 06:25:09 +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 1qQind-0048Wq-Hv for pgsql-hackers@arkaria.postgresql.org; Tue, 01 Aug 2023 06:24:06 +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 1qQinc-0048T5-Np for pgsql-hackers@lists.postgresql.org; Tue, 01 Aug 2023 06:24:06 +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 1qQinX-0007sQ-O4 for pgsql-hackers@lists.postgresql.org; Tue, 01 Aug 2023 06:24:04 +0000 Received: from oss.nttdata.com (localhost [127.0.0.1]) by oss.nttdata.com (Postfix) with ESMTPA id D52816028B; Tue, 1 Aug 2023 15:23:54 +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="=_a6bf6e1558e29485037bb2bb36ae17fd" Content-Transfer-Encoding: 8bit Date: Tue, 01 Aug 2023 15:23:54 +0900 From: Masahiro Ikeda To: pgsql-hackers@lists.postgresql.org Cc: mitsuru.hinata.5432@gmail.com Subject: Fix pg_stat_reset_single_table_counters function User-Agent: Roundcube Webmail/1.4.11 Message-ID: <7cc69f863d9b1bc677544e3accd0e4b4@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 --=_a6bf6e1558e29485037bb2bb36ae17fd Content-Transfer-Encoding: 8bit Content-Type: text/plain; charset=UTF-8; format=flowed Hi, My colleague, Mitsuru Hinata (in CC), found the following issue. The documentation of pg_stat_reset_single_table_counters() says > pg_stat_reset_single_table_counters ( oid ) → void > Resets statistics for a single table or index in the current database > or shared across all databases in the cluster to zero. > This function is restricted to superusers by default, but other users > can be granted EXECUTE to run the function. https://www.postgresql.org/docs/devel/monitoring-stats.html But, the stats will not be reset if the table shared across all databases is specified. IIUC, 5891c7a8e seemed to have mistakenly removed the feature implemented in e04267844. What do you think? * reproduce procedure SELECT COUNT(*) FROM pg_stat_database; SELECT pg_stat_reset_single_table_counters('pg_database'::regclass); SELECT seq_scan FROM pg_stat_all_tables WHERE relid = 'pg_database'::regclass; * unexpected result * Rename OverrideSearchPath to SearchPathMatcher (current HEAD: d3a38318a) * pgstat: store statistics in shared memory (5891c7a8e) psql=# SELECT seq_scan FROM pg_stat_all_tables WHERE relid = 'pg_database'::regclass; seq_scan ---------- 11 (1 row) * expected result * Enhance pg_stat_reset_single_table_counters function (e04267844) * pgstat: normalize function naming (be902e2651), which is previous commit of 5891c7a8e. psql=# SELECT seq_scan FROM pg_stat_all_tables WHERE relid = 'pg_database'::regclass; seq_scan ---------- 0 (1 row) Regards, -- Masahiro Ikeda NTT DATA CORPORATION --=_a6bf6e1558e29485037bb2bb36ae17fd Content-Transfer-Encoding: base64 Content-Type: text/x-diff; name=v1-0001-Fix-pg_stat_reset_single_table_counters-function.patch Content-Disposition: attachment; filename=v1-0001-Fix-pg_stat_reset_single_table_counters-function.patch; size=5868 RnJvbSA1NjFjNDM5MWM5ZGFjMzBiNTQ3ODYzN2E2YmFmOGM4Njg5MjI2ZGE1IE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBNYXNhaGlybyBJa2VkYSA8bXNoci5pa2VkYUBudHQuY29tPgpE YXRlOiBUdWUsIDEgQXVnIDIwMjMgMTM6NDY6MDAgKzA5MDAKU3ViamVjdDogW1BBVENIXSBGaXgg cGdfc3RhdF9yZXNldF9zaW5nbGVfdGFibGVfY291bnRlcnMgZnVuY3Rpb24uCgpUaGlzIGNvbW1p dCByZXZpdmVzIHRoZSBmZWF0dXJlIHRvIHJlc2V0IHN0YXRpc3RpY3MgZm9yIGEgc2luZ2xlCnJl bGF0aW9uIHNoYXJlZCBhY3Jvc3MgYWxsIGRhdGFiYXNlcyBpbiB0aGUgY2x1c3RlciB0byB6ZXJv LCB3aGljaAp3YXMgaW1wbGVtZW50ZWQgYnkgdGhlIGZvbGxvd2luZyBjb21taXQuCiogRW5oYW5j ZSBwZ19zdGF0X3Jlc2V0X3NpbmdsZV90YWJsZV9jb3VudGVycyBmdW5jdGlvbihlMDQyNjc4NDQp CgpUaGUgZm9sbG93aW5nIGNvbW1pdCBhY2NpZGVudGFsbHkgZGVsZXRlZCB0aGUgZmVhdHVyZS4K KiBwZ3N0YXQ6IHN0b3JlIHN0YXRpc3RpY3MgaW4gc2hhcmVkIG1lbW9yeSg1ODkxYzdhOGUpCgpC dW1wIGNhdGFsb2cgdmVyc2lvbi4KCk5lZWQgdG8gYmFja3BhdGNoIGZyb20gMTUuCgpSZXBvcnRl ZC1ieTogTWl0c3VydSBIaW5hdGEKLS0tCiBzcmMvYmFja2VuZC91dGlscy9hZHQvcGdzdGF0ZnVu Y3MuYyB8ICA5ICsrKystCiBzcmMvaW5jbHVkZS9jYXRhbG9nL2NhdHZlcnNpb24uaCAgICB8ICAy ICstCiBzcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3N0YXRzLm91dCB8IDYwICsrKysrKysrKysr KysrKysrKysrKysrKysrKysrCiBzcmMvdGVzdC9yZWdyZXNzL3NxbC9zdGF0cy5zcWwgICAgICB8 IDE4ICsrKysrKysrKwogNCBmaWxlcyBjaGFuZ2VkLCA4NiBpbnNlcnRpb25zKCspLCAzIGRlbGV0 aW9ucygtKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9wZ3N0YXRmdW5jcy5j IGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3Bnc3RhdGZ1bmNzLmMKaW5kZXggMmE0YzhlZjg3Zi4u MmI5NzQyYWQyMSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3Bnc3RhdGZ1bmNz LmMKKysrIGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3Bnc3RhdGZ1bmNzLmMKQEAgLTE3LDYgKzE3 LDcgQEAKICNpbmNsdWRlICJhY2Nlc3MvaHR1cF9kZXRhaWxzLmgiCiAjaW5jbHVkZSAiYWNjZXNz L3hsb2cuaCIKICNpbmNsdWRlICJhY2Nlc3MveGxvZ3ByZWZldGNoZXIuaCIKKyNpbmNsdWRlICJj YXRhbG9nL2NhdGFsb2cuaCIKICNpbmNsdWRlICJjYXRhbG9nL3BnX2F1dGhpZC5oIgogI2luY2x1 ZGUgImNhdGFsb2cvcGdfdHlwZS5oIgogI2luY2x1ZGUgImNvbW1vbi9pcC5oIgpAQCAtMTc3Niwx MyArMTc3NywxNyBAQCBwZ19zdGF0X3Jlc2V0X3NoYXJlZChQR19GVU5DVElPTl9BUkdTKQogCVBH X1JFVFVSTl9WT0lEKCk7CiB9CiAKLS8qIFJlc2V0IGEgc2luZ2xlIGNvdW50ZXIgaW4gdGhlIGN1 cnJlbnQgZGF0YWJhc2UgKi8KKy8qCisgKiBSZXNldCBhIHN0YXRpc3RpY3MgZm9yIGEgc2luZ2xl IG9iamVjdCwgd2hpY2ggbWF5IGJlIG9mIGN1cnJlbnQKKyAqIGRhdGFiYXNlIG9yIHNoYXJlZCBh Y3Jvc3MgYWxsIGRhdGFiYXNlcyBpbiB0aGUgY2x1c3Rlci4KKyAqLwogRGF0dW0KIHBnX3N0YXRf cmVzZXRfc2luZ2xlX3RhYmxlX2NvdW50ZXJzKFBHX0ZVTkNUSU9OX0FSR1MpCiB7CiAJT2lkCQkJ dGFib2lkID0gUEdfR0VUQVJHX09JRCgwKTsKKwlPaWQJCQlkYm9pZCA9IChJc1NoYXJlZFJlbGF0 aW9uKHRhYm9pZCkgPyBJbnZhbGlkT2lkIDogTXlEYXRhYmFzZUlkKTsKIAotCXBnc3RhdF9yZXNl dChQR1NUQVRfS0lORF9SRUxBVElPTiwgTXlEYXRhYmFzZUlkLCB0YWJvaWQpOworCXBnc3RhdF9y ZXNldChQR1NUQVRfS0lORF9SRUxBVElPTiwgZGJvaWQsIHRhYm9pZCk7CiAKIAlQR19SRVRVUk5f Vk9JRCgpOwogfQpkaWZmIC0tZ2l0IGEvc3JjL2luY2x1ZGUvY2F0YWxvZy9jYXR2ZXJzaW9uLmgg Yi9zcmMvaW5jbHVkZS9jYXRhbG9nL2NhdHZlcnNpb24uaAppbmRleCBmNTA3YjQ5YmIyLi41YTUz NDc3MWVkIDEwMDY0NAotLS0gYS9zcmMvaW5jbHVkZS9jYXRhbG9nL2NhdHZlcnNpb24uaAorKysg Yi9zcmMvaW5jbHVkZS9jYXRhbG9nL2NhdHZlcnNpb24uaApAQCAtNTcsNiArNTcsNiBAQAogICov CiAKIC8qCQkJCQkJCXl5eXltbWRkTiAqLwotI2RlZmluZSBDQVRBTE9HX1ZFUlNJT05fTk8JMjAy MzA3MjYxCisjZGVmaW5lIENBVEFMT0dfVkVSU0lPTl9OTwkyMDIzMDgwMTEKIAogI2VuZGlmCmRp ZmYgLS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3N0YXRzLm91dCBiL3NyYy90ZXN0 L3JlZ3Jlc3MvZXhwZWN0ZWQvc3RhdHMub3V0CmluZGV4IDhlNjMzNDA3ODIuLjIzNDUwZDI4YTgg MTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvc3RhdHMub3V0CisrKyBiL3Ny Yy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvc3RhdHMub3V0CkBAIC01OTIsNiArNTkyLDY2IEBAIFNF TEVDVCBzZXFfc2NhbiwgaWR4X3NjYW4gRlJPTSBwZ19zdGF0X2FsbF90YWJsZXMgV0hFUkUgcmVs aWQgPSAndGVzdF9sYXN0X3NjYW4nCiAgICAgICAgIDAgfCAgICAgICAgMAogKDEgcm93KQogCist LSBlbnN1cmUgdG8gcmVzZXQgc3RhdGlzdGljcyBmb3IgYSB0YWJsZSBhbmQgYSBpbmRleCBzaGFy ZWQgYWNyb3NzIGFsbCBkYXRhYmFzZXMKK0JFR0lOOworU0VUIExPQ0FMIGVuYWJsZV9zZXFzY2Fu IFRPIG9uOworU0VUIExPQ0FMIGVuYWJsZV9pbmRleHNjYW4gVE8gb2ZmOworU0VUIExPQ0FMIGVu YWJsZV9pbmRleG9ubHlzY2FuIFRPIG9mZjsKK0VYUExBSU4gKENPU1RTIG9mZikgU0VMRUNUIGNv dW50KCopIEZST00gcGdfZGF0YWJhc2U7CisgICAgICAgICAgUVVFUlkgUExBTiAgICAgICAgICAg CistLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCisgQWdncmVnYXRlCisgICAtPiAgU2Vx IFNjYW4gb24gcGdfZGF0YWJhc2UKKygyIHJvd3MpCisKK1NFTEVDVCBjb3VudCgqKSBGUk9NIHBn X2RhdGFiYXNlOyAtLSBpbmNyZW1lbnQgc3RhdHMgZm9yIHRoZSB0YWJsZQorIGNvdW50IAorLS0t LS0tLQorICAgICA0CisoMSByb3cpCisKK1NFVCBMT0NBTCBlbmFibGVfc2Vxc2NhbiBUTyBvZmY7 CitTRVQgTE9DQUwgZW5hYmxlX2luZGV4c2NhbiBUTyBvbjsKK1NFVCBMT0NBTCBlbmFibGVfaW5k ZXhvbmx5c2NhbiBUTyBvZmY7CitFWFBMQUlOIChDT1NUUyBvZmYpIFNFTEVDVCBjb3VudCgqKSBG Uk9NIHBnX2RhdGFiYXNlIFdIRVJFIG9pZCA9IDE7CisgICAgICAgICAgICAgICAgICAgICAgICAg UVVFUlkgUExBTiAgICAgICAgICAgICAgICAgICAgICAgICAgCistLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCisgQWdncmVnYXRlCisg ICAtPiAgSW5kZXggU2NhbiB1c2luZyBwZ19kYXRhYmFzZV9vaWRfaW5kZXggb24gcGdfZGF0YWJh c2UKKyAgICAgICAgIEluZGV4IENvbmQ6IChvaWQgPSAnMSc6Om9pZCkKKygzIHJvd3MpCisKK1NF TEVDVCBjb3VudCgqKSBGUk9NIHBnX2RhdGFiYXNlIFdIRVJFIG9pZCA9IDE7IC0tIGluY3JlbWVu dCBzdGF0cyBmb3IgdGhlIGluZGV4CisgY291bnQgCistLS0tLS0tCisgICAgIDEKKygxIHJvdykK KworQ09NTUlUOworU0VMRUNUIHBnX3N0YXRfcmVzZXRfc2luZ2xlX3RhYmxlX2NvdW50ZXJzKCdw Z19kYXRhYmFzZSc6OnJlZ2NsYXNzKTsKKyBwZ19zdGF0X3Jlc2V0X3NpbmdsZV90YWJsZV9jb3Vu dGVycyAKKy0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KKyAKKygxIHJvdykK KworU0VMRUNUIHBnX3N0YXRfcmVzZXRfc2luZ2xlX3RhYmxlX2NvdW50ZXJzKCdwZ19kYXRhYmFz ZV9vaWRfaW5kZXgnOjpyZWdjbGFzcyk7CisgcGdfc3RhdF9yZXNldF9zaW5nbGVfdGFibGVfY291 bnRlcnMgCistLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCisgCisoMSByb3cp CisKK1NFTEVDVCBwZ19zdGF0X3Jlc2V0X3NpbmdsZV90YWJsZV9jb3VudGVycygncGdfZGF0YWJh c2VfZGF0bmFtZV9pbmRleCc6OnJlZ2NsYXNzKTsKKyBwZ19zdGF0X3Jlc2V0X3NpbmdsZV90YWJs ZV9jb3VudGVycyAKKy0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KKyAKKygx IHJvdykKKworU0VMRUNUIHNlcV9zY2FuLCBpZHhfc2NhbiBGUk9NIHBnX3N0YXRfYWxsX3RhYmxl cyBXSEVSRSByZWxpZCA9ICdwZ19kYXRhYmFzZSc6OnJlZ2NsYXNzOworIHNlcV9zY2FuIHwgaWR4 X3NjYW4gCistLS0tLS0tLS0tKy0tLS0tLS0tLS0KKyAgICAgICAgMCB8ICAgICAgICAwCisoMSBy b3cpCisKIC0tIGVuc3VyZSB3ZSBzdGFydCBvdXQgd2l0aCBleGFjdGx5IG9uZSBpbmRleCBhbmQg c2VxdWVudGlhbCBzY2FuCiBCRUdJTjsKIFNFVCBMT0NBTCBlbmFibGVfc2Vxc2NhbiBUTyBvbjsK ZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL3N0YXRzLnNxbCBiL3NyYy90ZXN0L3Jl Z3Jlc3Mvc3FsL3N0YXRzLnNxbAppbmRleCBmZGRmNWE4Mjc3Li4xZTQ1OTQ0ZTljIDEwMDY0NAot LS0gYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC9zdGF0cy5zcWwKKysrIGIvc3JjL3Rlc3QvcmVncmVz cy9zcWwvc3RhdHMuc3FsCkBAIC0zMDksNiArMzA5LDI0IEBAIENPTU1JVDsKIFNFTEVDVCBwZ19z dGF0X3Jlc2V0X3NpbmdsZV90YWJsZV9jb3VudGVycygndGVzdF9sYXN0X3NjYW4nOjpyZWdjbGFz cyk7CiBTRUxFQ1Qgc2VxX3NjYW4sIGlkeF9zY2FuIEZST00gcGdfc3RhdF9hbGxfdGFibGVzIFdI RVJFIHJlbGlkID0gJ3Rlc3RfbGFzdF9zY2FuJzo6cmVnY2xhc3M7CiAKKy0tIGVuc3VyZSB0byBy ZXNldCBzdGF0aXN0aWNzIGZvciBhIHRhYmxlIGFuZCBhIGluZGV4IHNoYXJlZCBhY3Jvc3MgYWxs IGRhdGFiYXNlcworQkVHSU47CitTRVQgTE9DQUwgZW5hYmxlX3NlcXNjYW4gVE8gb247CitTRVQg TE9DQUwgZW5hYmxlX2luZGV4c2NhbiBUTyBvZmY7CitTRVQgTE9DQUwgZW5hYmxlX2luZGV4b25s eXNjYW4gVE8gb2ZmOworRVhQTEFJTiAoQ09TVFMgb2ZmKSBTRUxFQ1QgY291bnQoKikgRlJPTSBw Z19kYXRhYmFzZTsKK1NFTEVDVCBjb3VudCgqKSBGUk9NIHBnX2RhdGFiYXNlOyAtLSBpbmNyZW1l bnQgc3RhdHMgZm9yIHRoZSB0YWJsZQorU0VUIExPQ0FMIGVuYWJsZV9zZXFzY2FuIFRPIG9mZjsK K1NFVCBMT0NBTCBlbmFibGVfaW5kZXhzY2FuIFRPIG9uOworU0VUIExPQ0FMIGVuYWJsZV9pbmRl eG9ubHlzY2FuIFRPIG9mZjsKK0VYUExBSU4gKENPU1RTIG9mZikgU0VMRUNUIGNvdW50KCopIEZS T00gcGdfZGF0YWJhc2UgV0hFUkUgb2lkID0gMTsKK1NFTEVDVCBjb3VudCgqKSBGUk9NIHBnX2Rh dGFiYXNlIFdIRVJFIG9pZCA9IDE7IC0tIGluY3JlbWVudCBzdGF0cyBmb3IgdGhlIGluZGV4CitD T01NSVQ7CitTRUxFQ1QgcGdfc3RhdF9yZXNldF9zaW5nbGVfdGFibGVfY291bnRlcnMoJ3BnX2Rh dGFiYXNlJzo6cmVnY2xhc3MpOworU0VMRUNUIHBnX3N0YXRfcmVzZXRfc2luZ2xlX3RhYmxlX2Nv dW50ZXJzKCdwZ19kYXRhYmFzZV9vaWRfaW5kZXgnOjpyZWdjbGFzcyk7CitTRUxFQ1QgcGdfc3Rh dF9yZXNldF9zaW5nbGVfdGFibGVfY291bnRlcnMoJ3BnX2RhdGFiYXNlX2RhdG5hbWVfaW5kZXgn OjpyZWdjbGFzcyk7CitTRUxFQ1Qgc2VxX3NjYW4sIGlkeF9zY2FuIEZST00gcGdfc3RhdF9hbGxf dGFibGVzIFdIRVJFIHJlbGlkID0gJ3BnX2RhdGFiYXNlJzo6cmVnY2xhc3M7CisKIC0tIGVuc3Vy ZSB3ZSBzdGFydCBvdXQgd2l0aCBleGFjdGx5IG9uZSBpbmRleCBhbmQgc2VxdWVudGlhbCBzY2Fu CiBCRUdJTjsKIFNFVCBMT0NBTCBlbmFibGVfc2Vxc2NhbiBUTyBvbjsKLS0gCjIuMjUuMQoK --=_a6bf6e1558e29485037bb2bb36ae17fd--