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 1t1Hzc-00FY1I-Dc for pgsql-bugs@arkaria.postgresql.org; Thu, 17 Oct 2024 04:20:08 +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 1t1Hza-00FnGw-J3 for pgsql-bugs@arkaria.postgresql.org; Thu, 17 Oct 2024 04:20:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1Hza-00FnDi-Am for pgsql-bugs@lists.postgresql.org; Thu, 17 Oct 2024 04:20:06 +0000 Received: from oss.nttdata.com ([49.212.34.109]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1HzT-001TIs-EA for pgsql-bugs@postgresql.org; Thu, 17 Oct 2024 04:20:06 +0000 Received: from [192.168.11.3] (p4010149-ipxg13601funabasi.chiba.ocn.ne.jp [153.165.135.149]) by oss.nttdata.com (Postfix) with ESMTPSA id 48DA36139E; Thu, 17 Oct 2024 13:19:53 +0900 (JST) X-Virus-Status: Clean X-Virus-Scanned: clamav-milter 0.103.11 at oss.nttdata.com Content-Type: multipart/mixed; boundary="------------zdJ0DCaiWP0v6NmxTad1q5pp" Message-ID: Date: Thu, 17 Oct 2024 13:19:52 +0900 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: [BUGS] BUG #10123: Weird entries in pg_stat_activity From: Fujii Masao To: Andres Freund Cc: Fujii Masao , maxim.boguk@gmail.com, pgsql-bugs@postgresql.org References: <20140424101827.2714.39486@wrigleys.postgresql.org> <2153c106-8979-430d-ba86-34d35e565ab4@oss.nttdata.com> <20240704220316.srzqzkb7zgzh76xy@alap3.anarazel.de> Content-Language: en-US In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------zdJ0DCaiWP0v6NmxTad1q5pp Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 2024/10/17 1:59, Fujii Masao wrote: > > > On 2024/07/05 7:03, Andres Freund wrote: >> Calls to pgstat_report_activity() aren't exactly free. This substantially >> increases the number of calls to it for common workloads. There got to be a >> more targeted way of dealing with this. > > Yes. So, as an alternative approach, how about setting xact_start and > query_start to NULL whenever the state is idle, since non-NULL values > aren't expected in that case? This would prevent the weired pg_stat_activity > entries I mentioned earlier, with minimal performance impact. Patch attached. Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION --------------zdJ0DCaiWP0v6NmxTad1q5pp Content-Type: text/plain; charset=UTF-8; name="v2-0001-Prevent-misleading-entries-in-pg_stat_activity.patch" Content-Disposition: attachment; filename*0="v2-0001-Prevent-misleading-entries-in-pg_stat_activity.patch" Content-Transfer-Encoding: base64 RnJvbSBlOTA0MGZlNGMyNjM3OGRhYTQ1YjhkZWE3NTJhMGMwZWU4YjI1NmE5IE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBGdWppaSBNYXNhbyA8ZnVqaWlAcG9zdGdyZXNxbC5v cmc+CkRhdGU6IFRodSwgMTcgT2N0IDIwMjQgMTE6NTI6MjAgKzA5MDAKU3ViamVjdDogW1BB VENIIHYyXSBQcmV2ZW50IG1pc2xlYWRpbmcgZW50cmllcyBpbiBwZ19zdGF0X2FjdGl2aXR5 LgoKSW4gc29tZSBjYXNlcywgc3VjaCBhcyBoYW5kbGluZyAiRGVzY3JpYmUiIG1lc3NhZ2Vz LCBjbGVhbmluZyB1cAp0ZW1wb3JhcnkgcmVsYXRpb25zIGF0IGV4aXQsIG9yIG1hbmFnaW5n IGNsaWVudCByZWFkIGludGVycnVwdHMsCmJhY2tlbmRzIGNhbiByZW1haW4gaW4gYW4gImlk bGUiIHN0YXRlIGJ1dCBzdGlsbCBzZXQgeGFjdF9zdGFydAppbiBwZ19zdGF0X2FjdGl2aXR5 LiBUaGlzIHJlc3VsdHMgaW4gbWlzbGVhZGluZyBlbnRyaWVzIHdoZXJlCiJpZGxlIiBiYWNr ZW5kcyBhcHBlYXIgdG8gaGF2ZSBub24tTlVMTCB0cmFuc2FjdGlvbiB0aW1lcy4KQWRkaXRp b25hbGx5LCBkdXJpbmcgdGVtcCByZWxhdGlvbiBjbGVhbnVwIG9yIGludGVycnVwdCBoYW5k bGluZywKeGFjdF9zdGFydCBtaWdodCBpbmNvcnJlY3RseSByZWZsZWN0IHRoZSB0aW1lc3Rh bXAgb2YKdGhlIGxhc3QgZXhlY3V0ZWQgcXVlcnksIGZ1cnRoZXIgY29uZnVzaW5nIHVzZXJz LgoKVGhpcyBjb21taXQgYWRkcmVzc2VzIHRoZSBpc3N1ZSBieSBlbnN1cmluZyBwZ19zdGF0 X2FjdGl2aXR5CmRvZXMgbm90IHNob3cgdHJhbnNhY3Rpb24gYW5kIHF1ZXJ5IHN0YXJ0IHRp bWVzIGZvciAiaWRsZSIgYmFja2VuZHMsCnNldHRpbmcgdGhlbSB0byBOVUxMIGluc3RlYWQu IFRoaXMgcHJldmVudHMgY29uZnVzaW5nIGVudHJpZXMKZm9yICJpZGxlIiBiYWNrZW5kcy4K CldoaWxlIGl0IHdvdWxkIGJlIHBvc3NpYmxlIHRvIGNvcnJlY3RseSB0cmFjayBiYWNrZW5k IHN0YXRlcyBhbmQKc2V0IHRpbWVzdGFtcHMsIHRoaXMgYXBwcm9hY2ggd291bGQgaW50cm9k dWNlIHBlcmZvcm1hbmNlIG92ZXJoZWFkCmR1ZSB0byBmcmVxdWVudCB1cGRhdGVzIG9mIHN0 YXRlIGFuZCB0aW1lc3RhbXAuIE1vcmVvdmVyLCBpdCBpcyBub3QKcGFydGljdWxhcmx5IGJl bmVmaWNpYWwgdG8gdHJlYXQgcHJvY2Vzc2VzIGxpa2UgIkRlc2NyaWJlIiBtZXNzYWdlIGhh bmRsaW5nLAp0ZW1wIHJlbGF0aW9uIGNsZWFudXAsIG9yIGNsaWVudCByZWFkIGludGVycnVw dHMgYXMgcmVndWxhciB0cmFuc2FjdGlvbnMuClRoZXJlZm9yZSwgdGhlIHNpbXBsZXIgYW5k IG1vcmUgZWZmaWNpZW50IHNvbHV0aW9uIHdhcyBhZG9wdGVkLgoKRGlzY3Vzc2lvbjogaHR0 cHM6Ly9wb3N0Z3IuZXMvbS8yMDE0MDQyNDEwMTgyNy4yNzE0LjM5NDg2QHdyaWdsZXlzLnBv c3RncmVzcWwub3JnCi0tLQogc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3Bnc3RhdGZ1bmNzLmMg fCAyMSArKysrKysrKysrKysrKysrKysrLS0KIDEgZmlsZSBjaGFuZ2VkLCAxOSBpbnNlcnRp b25zKCspLCAyIGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxz L2FkdC9wZ3N0YXRmdW5jcy5jIGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3Bnc3RhdGZ1bmNz LmMKaW5kZXggZjdiNTBlMGI1YS4uNTk2N2I2YWZiNSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tl bmQvdXRpbHMvYWR0L3Bnc3RhdGZ1bmNzLmMKKysrIGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0 L3Bnc3RhdGZ1bmNzLmMKQEAgLTQ2MCwxNCArNDYwLDMxIEBAIHBnX3N0YXRfZ2V0X2FjdGl2 aXR5KFBHX0ZVTkNUSU9OX0FSR1MpCiAJCQkgKiBEb24ndCBleHBvc2UgdHJhbnNhY3Rpb24g dGltZSBmb3Igd2Fsc2VuZGVyczsgaXQgY29uZnVzZXMKIAkJCSAqIG1vbml0b3JpbmcsIHBh cnRpY3VsYXJseSBiZWNhdXNlIHdlIGRvbid0IGtlZXAgdGhlIHRpbWUgdXAtdG8tCiAJCQkg KiBkYXRlLgorCQkJICoKKwkJCSAqIEFsc28sIGRvbid0IHNob3cgdHJhbnNhY3Rpb24gdGlt ZSBmb3IgYmFja2VuZHMgaW4gdGhlICJpZGxlIgorCQkJICogc3RhdGUuIFRoZXJlIGFyZSBj YXNlcywgbGlrZSBkdXJpbmcgIkRlc2NyaWJlIiBtZXNzYWdlCisJCQkgKiBoYW5kbGluZywg cmVtb3ZpbmcgdGVtcG9yYXJ5IHJlbGF0aW9ucyBhdCBleGl0LCBvciBwcm9jZXNzaW5nCisJ CQkgKiBjbGllbnQgcmVhZCBpbnRlcnJ1cHRzLCB3aGVyZSB0aGUgYmFja2VuZCByZW1haW5z ICJpZGxlIiBidXQKKwkJCSAqIHN0aWxsIHNldHMgdHJhbnNhY3Rpb24gdGltZS4gVGhpcyBj YW4gbGVhZCB0byBpbmNvcnJlY3QgImlkbGUiCisJCQkgKiBlbnRyaWVzIHdpdGggbm9uLU5V TEwgdHJhbnNhY3Rpb24gdGltZXMgaW4gcGdfc3RhdF9hY3Rpdml0eS4gVG8KKwkJCSAqIHBy ZXZlbnQgdGhlc2UgbWlzbGVhZGluZyBlbnRyaWVzLCBhdm9pZCBleHBvc2luZyB0cmFuc2Fj dGlvbgorCQkJICogdGltZSBmb3IgaWRsZSBiYWNrZW5kcy4KIAkJCSAqLwogCQkJaWYgKGJl ZW50cnktPnN0X3hhY3Rfc3RhcnRfdGltZXN0YW1wICE9IDAgJiYKLQkJCQliZWVudHJ5LT5z dF9iYWNrZW5kVHlwZSAhPSBCX1dBTF9TRU5ERVIpCisJCQkJYmVlbnRyeS0+c3RfYmFja2Vu ZFR5cGUgIT0gQl9XQUxfU0VOREVSICYmCisJCQkJKGJlZW50cnktPnN0X3N0YXRlICE9IFNU QVRFX0lETEUgfHwKKwkJCQkgYmVlbnRyeS0+c3RfYmFja2VuZFR5cGUgIT0gQl9CQUNLRU5E KSkKIAkJCQl2YWx1ZXNbOF0gPSBUaW1lc3RhbXBUekdldERhdHVtKGJlZW50cnktPnN0X3hh Y3Rfc3RhcnRfdGltZXN0YW1wKTsKIAkJCWVsc2UKIAkJCQludWxsc1s4XSA9IHRydWU7CiAK LQkJCWlmIChiZWVudHJ5LT5zdF9hY3Rpdml0eV9zdGFydF90aW1lc3RhbXAgIT0gMCkKKwkJ CS8qCisJCQkgKiBEb24ndCBleHBvc2UgcXVlcnkgc3RhcnQgdGltZSBmb3IgaWRsZSBiYWNr ZW5kcyBmb3IgdGhlIHNhbWUKKwkJCSAqIHJlYXNvbnMgbWVudGlvbmVkIGFib3ZlIHJlZ2Fy ZGluZyB0cmFuc2FjdGlvbiB0aW1lLgorCQkJICovCisJCQlpZiAoYmVlbnRyeS0+c3RfYWN0 aXZpdHlfc3RhcnRfdGltZXN0YW1wICE9IDAgJiYKKwkJCQkoYmVlbnRyeS0+c3Rfc3RhdGUg IT0gU1RBVEVfSURMRSB8fAorCQkJCSBiZWVudHJ5LT5zdF9iYWNrZW5kVHlwZSAhPSBCX0JB Q0tFTkQpKQogCQkJCXZhbHVlc1s5XSA9IFRpbWVzdGFtcFR6R2V0RGF0dW0oYmVlbnRyeS0+ c3RfYWN0aXZpdHlfc3RhcnRfdGltZXN0YW1wKTsKIAkJCWVsc2UKIAkJCQludWxsc1s5XSA9 IHRydWU7Ci0tIAoyLjQ2LjIKCg== --------------zdJ0DCaiWP0v6NmxTad1q5pp--