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 1sPFQ6-003fYD-EZ for pgsql-bugs@arkaria.postgresql.org; Thu, 04 Jul 2024 05:54:14 +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 1sPFQ4-00E3Xz-1n for pgsql-bugs@arkaria.postgresql.org; Thu, 04 Jul 2024 05:54:12 +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 1sPFQ3-00E3XV-Nb for pgsql-bugs@lists.postgresql.org; Thu, 04 Jul 2024 05:54:12 +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 1sPFQ0-000Knz-Sy for pgsql-bugs@postgresql.org; Thu, 04 Jul 2024 05:54:11 +0000 Received: from [192.168.11.6] (p4160194-ipxg13701funabasi.chiba.ocn.ne.jp [180.47.157.194]) by oss.nttdata.com (Postfix) with ESMTPSA id AEFD36187B; Thu, 4 Jul 2024 14:54:02 +0900 (JST) X-Virus-Status: Clean X-Virus-Scanned: clamav-milter 0.103.11 at oss.nttdata.com Content-Type: multipart/mixed; boundary="------------80R9N92zQuZqK5qfMVUlH421" Message-ID: <2153c106-8979-430d-ba86-34d35e565ab4@oss.nttdata.com> Date: Thu, 4 Jul 2024 14:54:01 +0900 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: [BUGS] BUG #10123: Weird entries in pg_stat_activity Content-Language: en-US To: Fujii Masao , maxim.boguk@gmail.com Cc: pgsql-bugs@postgresql.org References: <20140424101827.2714.39486@wrigleys.postgresql.org> From: Fujii Masao 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. --------------80R9N92zQuZqK5qfMVUlH421 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 2024/07/04 0:14, Fujii Masao wrote: > I'm not sure if these are bugs. However, to prevent confusion from > these odd pg_stat_activity entries, I suggest the following changes: > > 1. Modify ProcessCatchupInterrupt() and RemoveTempRelationsCallback() > to reset stmtStartTimestamp (timestamp of the last query > executed) to 0 > before starting the transaction. This way, during the transaction, > records with state="idle" and xact_start=NULL would be observed. > > 2. Update exec_describe_statement_message() and > exec_describe_portal_message() > to change the state to "active," similar to exec_simple_query(). > This would result in records with state="active" and > xact_start="current timestamp" > during those operations. Attached patch does these things. I will register it into the next CF. Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION --------------80R9N92zQuZqK5qfMVUlH421 Content-Type: text/plain; charset=UTF-8; name="v1-0001-Prevent-incorrect-entries-in-pg_stat_activity.patch" Content-Disposition: attachment; filename*0="v1-0001-Prevent-incorrect-entries-in-pg_stat_activity.patch" Content-Transfer-Encoding: base64 RnJvbSBlNjg3ZWM0NzJjNTQ3ODcxNmIzMTE4OWUyNWU5NDJmZjNhYmYxMWJmIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBGdWppaSBNYXNhbyA8ZnVqaWlAcG9zdGdyZXNxbC5v cmc+CkRhdGU6IFRodSwgNCBKdWwgMjAyNCAxMjowOTozOSArMDkwMApTdWJqZWN0OiBbUEFU Q0ggdjFdIFByZXZlbnQgaW5jb3JyZWN0IGVudHJpZXMgaW4gcGdfc3RhdF9hY3Rpdml0eS4K CkluIHBnX3N0YXRfYWN0aXZpdHksIHdoZW4gdGhlIGJhY2tlbmQncyBzdGF0ZSBpcyAiaWRs ZSIsCm5vIHRyYW5zYWN0aW9uIHNob3VsZCBiZSBydW5uaW5nLCBhbmQgeGFjdF9zdGFydAoo aW5kaWNhdGluZyB0aGUgdHJhbnNhY3Rpb24gc3RhcnQgdGltZSkgc2hvdWxkIGJlIE5VTEwu CgpQcmV2aW91c2x5LCBkdXJpbmcgdGhlIHByb2Nlc3Npbmcgb2YgIkRlc2NyaWJlIiBtZXNz YWdlcywKcmVtb3ZpbmcgdGVtcCByZWxhdGlvbnMgYXQgZXhpdCwgYW5kIGhhbmRsaW5nIGNs aWVudCByZWFkIGludGVycnVwdHMsCnRoZSBiYWNrZW5kIGRpZCBub3QgY2hhbmdlIGl0cyBz dGF0ZSBmcm9tICJpZGxlIiBidXQgc3RpbGwgc2V0IHhhY3Rfc3RhcnQsCmNhdXNpbmcgaW5j b3JyZWN0IHBnX3N0YXRfYWN0aXZpdHkgZW50cmllcy4gQWRkaXRpb25hbGx5LApkdXJpbmcg dGVtcCByZWxhdGlvbiByZW1vdmFsIG9yIGludGVycnVwdCBwcm9jZXNzaW5nLAp4YWN0X3N0 YXJ0IGNvdWxkIGluY29ycmVjdGx5IHJlZmxlY3QgdGhlIHRpbWVzdGFtcCBvZgp0aGUgbGFz dCBxdWVyeSBleGVjdXRlZC4KClRvIGZpeCB0aGlzLCB0aGUgYmFja2VuZCBub3cgc2V0cyBp dHMgc3RhdGUgdG8gImFjdGl2ZSIKd2hlbiBwcm9jZXNzaW5nIGEgIkRlc2NyaWJlIiBtZXNz YWdlLgoKRm9yIGludGVycnVwdHMgYW5kIHRlbXAgcmVsYXRpb24gcmVtb3ZhbCwgc2luY2Ug dGhlc2UKaW52b2x2ZSBicmllZiB0cmFuc2FjdGlvbnMsIGl0IGlzIHVubmVjZXNzYXJ5IHRv IHRyZWF0IHRoZW0KYXMgbm9ybWFsIHRyYW5zYWN0aW9ucy4gSW5zdGVhZCBvZiBjaGFuZ2lu ZyB0aGUgc3RhdGUgdG8KImFjdGl2ZSIgYW5kIHNldHRpbmcgeGFjdF9zdGFydCB0byB0aGUg Y3VycmVudCB0aW1lc3RhbXAsCnRoaXMgY29tbWl0IGVuc3VyZXMgdGhlIGJhY2tlbmQgcmVz ZXRzIHhhY3Rfc3RhcnQgdG8gTlVMTAppbiB0aGVzZSBjYXNlcy4KLS0tCiBzcmMvYmFja2Vu ZC9hY2Nlc3MvdHJhbnNhbS94YWN0LmMgfCAgOSArKysrKysrKysKIHNyYy9iYWNrZW5kL2Nh dGFsb2cvbmFtZXNwYWNlLmMgICB8IDEwICsrKysrKysrKysKIHNyYy9iYWNrZW5kL3Rjb3Av cG9zdGdyZXMuYyAgICAgICB8IDE1ICsrKysrKysrKysrKysrKwogc3JjL2luY2x1ZGUvYWNj ZXNzL3hhY3QuaCAgICAgICAgIHwgIDEgKwogNCBmaWxlcyBjaGFuZ2VkLCAzNSBpbnNlcnRp b25zKCspCgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvYWNjZXNzL3RyYW5zYW0veGFjdC5j IGIvc3JjL2JhY2tlbmQvYWNjZXNzL3RyYW5zYW0veGFjdC5jCmluZGV4IGQxMTlhYjkwOWQu LjU4OTIxYmM0NjkgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2FjY2Vzcy90cmFuc2FtL3hh Y3QuYworKysgYi9zcmMvYmFja2VuZC9hY2Nlc3MvdHJhbnNhbS94YWN0LmMKQEAgLTkxNyw2 ICs5MTcsMTUgQEAgU2V0Q3VycmVudFN0YXRlbWVudFN0YXJ0VGltZXN0YW1wKHZvaWQpCiAJ CUFzc2VydChzdG10U3RhcnRUaW1lc3RhbXAgIT0gMCk7CiB9CiAKKy8qCisgKiBSZXNldEN1 cnJlbnRTdGF0ZW1lbnRTdGFydFRpbWVzdGFtcAorICovCit2b2lkCitSZXNldEN1cnJlbnRT dGF0ZW1lbnRTdGFydFRpbWVzdGFtcCh2b2lkKQoreworCXN0bXRTdGFydFRpbWVzdGFtcCA9 IDA7Cit9CisKIC8qCiAgKglHZXRDdXJyZW50VHJhbnNhY3Rpb25OZXN0TGV2ZWwKICAqCmRp ZmYgLS1naXQgYS9zcmMvYmFja2VuZC9jYXRhbG9nL25hbWVzcGFjZS5jIGIvc3JjL2JhY2tl bmQvY2F0YWxvZy9uYW1lc3BhY2UuYwppbmRleCBhMjUxMGNmODBjLi43OGQ1MzAyNjNkIDEw MDY0NAotLS0gYS9zcmMvYmFja2VuZC9jYXRhbG9nL25hbWVzcGFjZS5jCisrKyBiL3NyYy9i YWNrZW5kL2NhdGFsb2cvbmFtZXNwYWNlLmMKQEAgLTQ2MTAsNiArNDYxMCwxNiBAQCBSZW1v dmVUZW1wUmVsYXRpb25zQ2FsbGJhY2soaW50IGNvZGUsIERhdHVtIGFyZykKIHsKIAlpZiAo T2lkSXNWYWxpZChteVRlbXBOYW1lc3BhY2UpKQkvKiBzaG91bGQgYWx3YXlzIGJlIHRydWUg Ki8KIAl7CisJCS8qCisJCSAqIFJlc2V0IHN0YXRlbWVudF90aW1lc3RhbXAoKSB0byAwIHNv IHRoYXQgc3Vic2VxdWVudAorCQkgKiBTdGFydFRyYW5zYWN0aW9uQ29tbWFuZCgpIHdpbGwg c2V0IHBnX3N0YXRfYWN0aXZpdHkueGFjdF9zdGFydCB0bworCQkgKiBOVUxMLiBPdGhlcndp c2UsIHhhY3Rfc3RhcnQgY291bGQgYmUgc2V0IHRvIHN0YXRlbWVudF90aW1lc3RhbXAoKQor CQkgKiB0aGF0IG1heSBpbmRpY2F0ZSB0aGUgdGltZXN0YW1wIG9mIHRoZSBsYXN0IHF1ZXJ5 IGV4ZWN1dGVkLCB3aGlsZQorCQkgKiB0aGUgYmFja2VuZCdzIHN0YXRlIHJlbWFpbnMgImlk bGUsIiBsZWFkaW5nIHRvIGluY29ycmVjdCAiaWRsZSIKKwkJICogZW50cmllcyB3aXRoIG5v bi1OVUxMIHhhY3Rfc3RhcnQgaW4gcGdfc3RhdF9hY3Rpdml0eS4KKwkJICovCisJCVJlc2V0 Q3VycmVudFN0YXRlbWVudFN0YXJ0VGltZXN0YW1wKCk7CisKIAkJLyogTmVlZCB0byBlbnN1 cmUgd2UgaGF2ZSBhIHVzYWJsZSB0cmFuc2FjdGlvbi4gKi8KIAkJQWJvcnRPdXRPZkFueVRy YW5zYWN0aW9uKCk7CiAJCVN0YXJ0VHJhbnNhY3Rpb25Db21tYW5kKCk7CmRpZmYgLS1naXQg YS9zcmMvYmFja2VuZC90Y29wL3Bvc3RncmVzLmMgYi9zcmMvYmFja2VuZC90Y29wL3Bvc3Rn cmVzLmMKaW5kZXggZTM5YzY4MDRhNy4uMDQwNDZmNDFkOCAxMDA2NDQKLS0tIGEvc3JjL2Jh Y2tlbmQvdGNvcC9wb3N0Z3Jlcy5jCisrKyBiL3NyYy9iYWNrZW5kL3Rjb3AvcG9zdGdyZXMu YwpAQCAtNTE1LDYgKzUxNSwxNyBAQCBQcm9jZXNzQ2xpZW50UmVhZEludGVycnVwdChib29s IGJsb2NrZWQpCiAJCS8qIENoZWNrIGZvciBnZW5lcmFsIGludGVycnVwdHMgdGhhdCBhcnJp dmVkIGJlZm9yZS93aGlsZSByZWFkaW5nICovCiAJCUNIRUNLX0ZPUl9JTlRFUlJVUFRTKCk7 CiAKKwkJLyoKKwkJICogUmVzZXQgc3RhdGVtZW50X3RpbWVzdGFtcCgpIHRvIDAgc28gdGhh dCBQcm9jZXNzQ2F0Y2h1cEludGVycnVwdCgpCisJCSAqIGFuZCBQcm9jZXNzTm90aWZ5SW50 ZXJydXB0KCkgd2lsbCBzZXQgcGdfc3RhdF9hY3Rpdml0eS54YWN0X3N0YXJ0CisJCSAqIHRv IE5VTEwgd2hlbiB0aGV5IHN0YXJ0IG5ldyB0cmFuc2FjdGlvbi4gT3RoZXJ3aXNlLCB4YWN0 X3N0YXJ0CisJCSAqIGNvdWxkIGJlIHNldCB0byBzdGF0ZW1lbnRfdGltZXN0YW1wKCkgdGhh dCBtYXkgaW5kaWNhdGUgdGhlCisJCSAqIHRpbWVzdGFtcCBvZiB0aGUgbGFzdCBxdWVyeSBl eGVjdXRlZCwgd2hpbGUgdGhlIGJhY2tlbmQncyBzdGF0ZQorCQkgKiByZW1haW5zICJpZGxl LCIgbGVhZGluZyB0byBpbmNvcnJlY3QgImlkbGUiIGVudHJpZXMgd2l0aCBub24tTlVMTAor CQkgKiB4YWN0X3N0YXJ0IGluIHBnX3N0YXRfYWN0aXZpdHkuCisJCSAqLworCQlSZXNldEN1 cnJlbnRTdGF0ZW1lbnRTdGFydFRpbWVzdGFtcCgpOworCiAJCS8qIFByb2Nlc3Mgc2ludmFs IGNhdGNodXAgaW50ZXJydXB0cywgaWYgYW55ICovCiAJCWlmIChjYXRjaHVwSW50ZXJydXB0 UGVuZGluZykKIAkJCVByb2Nlc3NDYXRjaHVwSW50ZXJydXB0KCk7CkBAIC0yNTk4LDYgKzI2 MDksOCBAQCBleGVjX2Rlc2NyaWJlX3N0YXRlbWVudF9tZXNzYWdlKGNvbnN0IGNoYXIgKnN0 bXRfbmFtZSkKIHsKIAlDYWNoZWRQbGFuU291cmNlICpwc3JjOwogCisJcGdzdGF0X3JlcG9y dF9hY3Rpdml0eShTVEFURV9SVU5OSU5HLCBOVUxMKTsKKwogCS8qCiAJICogU3RhcnQgdXAg YSB0cmFuc2FjdGlvbiBjb21tYW5kLiAoTm90ZSB0aGF0IHRoaXMgd2lsbCBub3JtYWxseSBj aGFuZ2UKIAkgKiBjdXJyZW50IG1lbW9yeSBjb250ZXh0LikgTm90aGluZyBoYXBwZW5zIGlm IHdlIGFyZSBhbHJlYWR5IGluIG9uZS4KQEAgLTI2OTIsNiArMjcwNSw4IEBAIGV4ZWNfZGVz Y3JpYmVfcG9ydGFsX21lc3NhZ2UoY29uc3QgY2hhciAqcG9ydGFsX25hbWUpCiB7CiAJUG9y dGFsCQlwb3J0YWw7CiAKKwlwZ3N0YXRfcmVwb3J0X2FjdGl2aXR5KFNUQVRFX1JVTk5JTkcs IE5VTEwpOworCiAJLyoKIAkgKiBTdGFydCB1cCBhIHRyYW5zYWN0aW9uIGNvbW1hbmQuIChO b3RlIHRoYXQgdGhpcyB3aWxsIG5vcm1hbGx5IGNoYW5nZQogCSAqIGN1cnJlbnQgbWVtb3J5 IGNvbnRleHQuKSBOb3RoaW5nIGhhcHBlbnMgaWYgd2UgYXJlIGFscmVhZHkgaW4gb25lLgpk aWZmIC0tZ2l0IGEvc3JjL2luY2x1ZGUvYWNjZXNzL3hhY3QuaCBiL3NyYy9pbmNsdWRlL2Fj Y2Vzcy94YWN0LmgKaW5kZXggNmQ0NDM5ZjA1Mi4uODQ3NDU1NzViZSAxMDA2NDQKLS0tIGEv c3JjL2luY2x1ZGUvYWNjZXNzL3hhY3QuaAorKysgYi9zcmMvaW5jbHVkZS9hY2Nlc3MveGFj dC5oCkBAIC00NTAsNiArNDUwLDcgQEAgZXh0ZXJuIFRpbWVzdGFtcFR6IEdldEN1cnJlbnRU cmFuc2FjdGlvblN0YXJ0VGltZXN0YW1wKHZvaWQpOwogZXh0ZXJuIFRpbWVzdGFtcFR6IEdl dEN1cnJlbnRTdGF0ZW1lbnRTdGFydFRpbWVzdGFtcCh2b2lkKTsKIGV4dGVybiBUaW1lc3Rh bXBUeiBHZXRDdXJyZW50VHJhbnNhY3Rpb25TdG9wVGltZXN0YW1wKHZvaWQpOwogZXh0ZXJu IHZvaWQgU2V0Q3VycmVudFN0YXRlbWVudFN0YXJ0VGltZXN0YW1wKHZvaWQpOworZXh0ZXJu IHZvaWQgUmVzZXRDdXJyZW50U3RhdGVtZW50U3RhcnRUaW1lc3RhbXAodm9pZCk7CiBleHRl cm4gaW50CUdldEN1cnJlbnRUcmFuc2FjdGlvbk5lc3RMZXZlbCh2b2lkKTsKIGV4dGVybiBi b29sIFRyYW5zYWN0aW9uSWRJc0N1cnJlbnRUcmFuc2FjdGlvbklkKFRyYW5zYWN0aW9uSWQg eGlkKTsKIGV4dGVybiB2b2lkIENvbW1hbmRDb3VudGVySW5jcmVtZW50KHZvaWQpOwotLSAK Mi40NS4xCgo= --------------80R9N92zQuZqK5qfMVUlH421--