Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g5nK2-00071M-Me for pgsql-hackers@arkaria.postgresql.org; Fri, 28 Sep 2018 07:36:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g5nK1-0001nJ-4K for pgsql-hackers@arkaria.postgresql.org; Fri, 28 Sep 2018 07:36:21 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g5nK0-0001nC-5a for pgsql-hackers@lists.postgresql.org; Fri, 28 Sep 2018 07:36:20 +0000 Received: from [66.111.4.26] (helo=out2-smtp.messagingengine.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g5nJs-0007E6-Kj for pgsql-hackers@postgresql.org; Fri, 28 Sep 2018 07:36:19 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.47]) by mailout.nyi.internal (Postfix) with ESMTP id BB7D821E59; Fri, 28 Sep 2018 03:35:51 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute7.internal (MEProxy); Fri, 28 Sep 2018 03:35:51 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-sender :x-me-sender:x-sasl-enc; s=fm3; bh=uxJMDfVj0siz9VeXZZTbhEbORNUNK rKw91JFFC3GsXU=; b=slwAY+jfMtv77RKA3KTLshLziR8IEXwYSHxGZWL5H/VuR hV84LPOiVTKpfAGNJudiojFkjrUeO9svguMqBw8+HdCrHiHCKVDjwx15TC0JA9EB g0DuZuWHoBR08ZX59MCdFnde9c8AwpI/Z1NUhXVXbHG3Tpht3Dj0CJGE6JFYiD7V 8kwqFWtC2yY7JLlIdxD7EwoSrUCxeOrcCXcuuZs3w/R3lLWeuFGFRyW/1BAbTsqW 61fmT9wK+DdbocQw3l/KUMC1DLYO41Q666Av1/lj6PG30v5xbVx4ZxN01E/yP5ju +ut86zeNflB5cCihSbepL3U4ZFfAY3D3dc9NrMifg== X-ME-Proxy: X-ME-Sender: Received: from april.pezone.net (p5df2a2d9.dip0.t-ipconnect.de [93.242.162.217]) by mail.messagingengine.com (Postfix) with ESMTPA id 8CE8A102DE; Fri, 28 Sep 2018 03:35:49 -0400 (EDT) Subject: Re: transction_timestamp() inside of procedures From: Peter Eisentraut To: PostgreSQL-development References: <20180926155443.u4zh7jactzflys3z@alvherre.pgsql> Cc: Alvaro Herrera , Tom Lane , Bruce Momjian , Michael Paquier , Andres Freund Openpgp: preference=signencrypt Autocrypt: addr=peter.eisentraut@2ndquadrant.com; prefer-encrypt=mutual; keydata= xsFNBFUUt6kBEADZd9NNTBJbHmAXXJgPGOdd6Q73l80oifIVgn2tRxP9dYkCfTlo/yLqbKTl JNqTcyM6XrJDnB2sMEUGYCewYZXl+9C4bBJWyNqcEI1ae9u71vEq/fknFLArJ+2wN+f7QuRx Q8d94ZJAiJQ5g3fNk2HVeedCQCKP5dCzU+ZLt2dbsx1PDZLjTO9gf+JswWuGFb5wY5PZVM5V hq66Xk7ZJzHuARacza2RT7J2fkMmQBY+kr7L98Perb0GhafnlhXB0PvUB+N3xpmggMfwuhTu BngS/G/r3GvemxJuFxouw8xsMqrsfdksJXA2gLXYtwRCssdPV3PO0rlD9twqDo8URfIJ5fb6 1g/MV8aTOKjg3Q8MpJPtkwJqN4o3PvbwYdedeFnOVkkYAJdu/aX1v1BeCBDu7Hy3sF52scD3 pQbyQSl9vUDpTLarASAxNbAV0NRJE8dp4vfW6AH1ccryGiFqw/czPK/iN0+MT8wsjAW6WW4w hvjm5UPA1KTwi3ZT8eT5uycuH7QpuNk7LnGGXQ6KaRALFywzkr0A0V0AvFNa+l4wstMaXaB6 dmMXyF0/hfryqyOeu1gAdq4q0/PNC2dPgb/6uoh4eMjN6A5GXcprilhz5DulmsilY21hfSoU S64ODed96pKhprsOxjZiDRsN1QM5MwB+uwVWUNfsX7fIxE0BrwARAQABzSdQZXRlciBFaXNl bnRyYXV0IDxwZXRlckBlaXNlbnRyYXV0Lm9yZz7CwXoEEwEIACQCGwMFCwkIBwMFFQoJCAsF FgIDAQACHgECF4AFAlUUuKQCGQEACgkQoZ8SQ+xBJp8llw//RbGoXnF72FZTlOFsnNyxnWbF vViPCfdAlZl1GGK9OYCvV1dp/956HuhB0wpaGQHiBmQfOPJf/jLOPjXFYm2iEChEycrBChuE j4TSYemMp5BSPILYk9Uwd16kVD/+U6fQfK6H5pOhfBvbM6asMnmpY2wQuvktxHvaGlytFw84 EzFWNZtAZfxkw28w3AhIQkKe9hJanfyDL750RcbyEr8AAHty5IErrvS7tM5vQwIeh5BF26ND CyKPRdZgOgMpcwFfROKBDpAt052CgWIpNdhBdfIKlkCs4/ILxmC3rT/Mb/qZiHcoMMwEu6k+ T7vRAFZZAk62Yrr/U8/vUGKlzRZQoo86fyojCfI0ibCIc0z8rwCVY9P40G9wD9BTCZS/z8Vh jjpBlNnIKw2hM6hbxavKndCJjFFJD08rXrTyrLF/of18xD37vNdYooP9VATB5QjOWFRrscSZ 4a89e+to3m7YVnd4xdXwGp47dYusoax84C8tg1gZrH38ol9dPmnKX6GZrXeS7tH9k8VeUpJ8 HQbRThQRWAT936QpYbLConBmOtmbLmjQOGtiXPo1oK57X/7U/WBja5vWmm2q+Yxtt+3uHGVg DdDU4CJpXo4lX0v2n3QV5EBSW9hjdF3bNfl6GFBb1pPEhqNcMDUc7zkx/2uENdAdk/BnqMgH umip7b0pVfvOwU0EVRS3qQEQAJf6p0P7D+hMYUgCPkvgrcz6OJYsAYdUMUmokfqmkKwK0oJM ew8TNGM53ocKAgQGma5f10rwbZNPlpVPNRX5hwUmwVlcwSut0oE+JfUBH7mpysWvpF05RKIy q9RCoJZrAbXS7Tig56A1QD8IcM00rERZMcCOlI1+q9m4u9AOI+0LyGGSH+VvoyDHD/Jew4R2 TmPEF8Pu7lYiNYK88Q6u0kBkc+Tz3BNlyc6fi3HxnFfp0b75HLXPSTu2SwA/0Q31wHZXwrEU BR9KgSn/qbgy8ydPc97i82fhiFE3cbGexfPrQSMYmDGi+dcQ0s+AMQwC3XyMC0JkHpYUuFkR QdlIvaIhgFUUN2Vr4xF2ADaNTNFc4DYaiPHFAgHZw+3pZWqS4uIiWgJynrEaHw4F3QWU8/JB iyEWDhNq4WBrRA0u914TCCAoFOOKMnRdl8eJrRQKM/5/lXbHFjjlI/LDKhLRFsuQsnJJ3331 RjCzpHPZG5/p5KR1RHiAXa0p/DW5lXyq21hS3kjDlleoHqOxCN/cy0Jgz7qLDq1vZoCl67Sy RAH60c2Xu4may9SVltmot0OzWVHdrfS+85FQGp9580DaeLio7YMFKy2bfEvwQJ/c9xD2QPB+ oSb3V8ueSznXBG5+dyfOsZFRPxceA0wA2rSWZSjxLdKxvyGTj9vsCjaDu13rABEBAAHCwV8E GAEIAAkFAlUUt6kCGwwACgkQoZ8SQ+xBJp+HJhAAxmSTsC5y7p9Zu+nnLpucv/h88oEx985e qWeXzQiwM3pfRZS+PdTH9TyzHK0KOgKoAiubk0vt+BrT91D9d0G5kVQaTctAErGBoMkTaZJT XVEcNphiPzysdSqvprjojMp1y7rG8AP+Xd6m0rE1LRnkRRm8HyI8vdXsuLQPITGWXDLMjaNq AOV3BUnzkgtjMuIYw7SCr+12ZOb5dEKvaG4kjC4kaJ/TLbuBhVANcXkC90yp0AOZLs/cB1Dj OAXno/RUUruJ3r27a+pFv768UAi9QWcdw58yPxreMNMLqAgsS9oTHwHfGNWcB2dJ4UW+GMLJ hTf9TjQ2f+N/sIx5XMQ9pYw6CAJMK29qPx6/ZayrPxf22EXf0D3zTwIEZgwfSkynFVwV25L7 awQq60fifdMYheSxq7GbhpsixmcD+e161cCmLIt75YDt5bQ0B1yf7EmxG8n7pGZAOM6LoNsU fkSldba5XVwh8LuN5mK0nQBELGW6Os4TDq4REggLpfcvFC215zpuo0iCGnmE/Bns4nowqUSP u4tkWL+nxW4qgv8wo8HJ52jd79EJ8iqfIBoidBfy9AIUiEUApGQzWPu6yWcI8fY0rN5D+tc+ Yw1Zt6Mm+huYJpgCRsTcfzEcahnBn521CWcWvhW1ff3KFileFMtNmnBBhMncmRKHphtTKEpN 3jI= Organization: 2ndQuadrant Message-ID: Date: Fri, 28 Sep 2018 09:35:48 +0200 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------D7B802A9DACBDF98328E3B18" Content-Language: en-US X-Host-Lookup-Failed: Reverse DNS lookup failed for 66.111.4.26 (deferred) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------D7B802A9DACBDF98328E3B18 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit On 26/09/2018 23:48, Peter Eisentraut wrote: > On 26/09/2018 17:54, Alvaro Herrera wrote: >> What could be the use for the transaction timestamp? I think one of the >> most important uses (at least in pg_stat_activity) is to verify that >> transactions are not taking excessively long time to complete; that's >> known to cause all sorts of trouble in Postgres, and probably other >> DBMSs too. If we don't accurately measure what it really is, and >> instead keep the compatibility behavior, we risk panicking people >> because they think some transaction has been running for a long time >> when in reality it's just a very long procedure which commits frequently >> enough not to be a problem. > > That's certainly a good argument. Note that if we implemented that the > transaction timestamp is advanced inside procedures, that would also > mean that the transaction timestamp as observed in pg_stat_activity > would move during VACUUM, for example. That might or might not be > desirable. Attached is a rough implementation. I'd be mildly in favor of doing this, but we have mentioned tradeoffs in this thread. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services --------------D7B802A9DACBDF98328E3B18 Content-Type: text/plain; charset=UTF-8; x-mac-type="0"; x-mac-creator="0"; name="0001-Advance-transaction-timestamp-in-intra-procedure-tra.patch" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename*0="0001-Advance-transaction-timestamp-in-intra-procedure-tra.pa"; filename*1="tch" RnJvbSAyYThjMjE0NDc5YWRiNGI5OGM4YjZjOTU4NzVkOGJlYmQ4OGNiOTQwIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBQZXRlciBFaXNlbnRyYXV0IDxwZXRlcl9lQGdteC5u ZXQ+CkRhdGU6IEZyaSwgMjggU2VwIDIwMTggMDk6MzM6MjQgKzAyMDAKU3ViamVjdDogW1BB VENIXSBBZHZhbmNlIHRyYW5zYWN0aW9uIHRpbWVzdGFtcCBpbiBpbnRyYS1wcm9jZWR1cmUg dHJhbnNhY3Rpb25zCgotLS0KIHNyYy9iYWNrZW5kL2FjY2Vzcy90cmFuc2FtL3hhY3QuYyB8 IDE5ICsrKysrKysrKysrKy0tLS0tLS0KIDEgZmlsZSBjaGFuZ2VkLCAxMiBpbnNlcnRpb25z KCspLCA3IGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2FjY2Vzcy90 cmFuc2FtL3hhY3QuYyBiL3NyYy9iYWNrZW5kL2FjY2Vzcy90cmFuc2FtL3hhY3QuYwppbmRl eCA5YWE2M2M4NzkyLi4yNDU3MzU0MjBjIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9hY2Nl c3MvdHJhbnNhbS94YWN0LmMKKysrIGIvc3JjL2JhY2tlbmQvYWNjZXNzL3RyYW5zYW0veGFj dC5jCkBAIC0xODg0LDE0ICsxODg0LDE5IEBAIFN0YXJ0VHJhbnNhY3Rpb24odm9pZCkKIAlU UkFDRV9QT1NUR1JFU1FMX1RSQU5TQUNUSU9OX1NUQVJUKHZ4aWQubG9jYWxUcmFuc2FjdGlv bklkKTsKIAogCS8qCi0JICogc2V0IHRyYW5zYWN0aW9uX3RpbWVzdGFtcCgpIChhL2svYSBu b3coKSkuICBXZSB3YW50IHRoaXMgdG8gYmUgdGhlIHNhbWUKLQkgKiBhcyB0aGUgZmlyc3Qg Y29tbWFuZCdzIHN0YXRlbWVudF90aW1lc3RhbXAoKSwgc28gZG9uJ3QgZG8gYSBmcmVzaAot CSAqIEdldEN1cnJlbnRUaW1lc3RhbXAoKSBjYWxsICh3aGljaCdkIGJlIGV4cGVuc2l2ZSBh bnl3YXkpLiAgQWxzbywgbWFyawotCSAqIHhhY3RTdG9wVGltZXN0YW1wIGFzIHVuc2V0Lgot CSAqLwotCXhhY3RTdGFydFRpbWVzdGFtcCA9IHN0bXRTdGFydFRpbWVzdGFtcDsKLQl4YWN0 U3RvcFRpbWVzdGFtcCA9IDA7CisJICogc2V0IHRyYW5zYWN0aW9uX3RpbWVzdGFtcCgpIChh L2svYSBub3coKSkuICBOb3JtYWxseSwgd2Ugd2FudCB0aGlzIHRvCisJICogYmUgdGhlIHNh bWUgYXMgdGhlIGZpcnN0IGNvbW1hbmQncyBzdGF0ZW1lbnRfdGltZXN0YW1wKCksIHNvIGRv bid0IGRvIGEKKwkgKiBmcmVzaCBHZXRDdXJyZW50VGltZXN0YW1wKCkgY2FsbCAod2hpY2gn ZCBiZSBleHBlbnNpdmUgYW55d2F5KS4gIEJ1dAorCSAqIGZvciB0cmFuc2FjdGlvbnMgc3Rh cnRlZCBpbnNpZGUgc3RhdGVtZW50cyAoZS5nLiwgcHJvY2VkdXJlIGNhbGxzKSwgd2UKKwkg KiB3YW50IHRvIGFkdmFuY2UgdGhlIHRpbWVzdGFtcC4KKwkgKi8KKwlpZiAoeGFjdFN0YXJ0 VGltZXN0YW1wIDwgc3RtdFN0YXJ0VGltZXN0YW1wKQorCQl4YWN0U3RhcnRUaW1lc3RhbXAg PSBzdG10U3RhcnRUaW1lc3RhbXA7CisJZWxzZQorCQl4YWN0U3RhcnRUaW1lc3RhbXAgPSBH ZXRDdXJyZW50VGltZXN0YW1wKCk7CiAJcGdzdGF0X3JlcG9ydF94YWN0X3RpbWVzdGFtcCh4 YWN0U3RhcnRUaW1lc3RhbXApOworCS8qIE1hcmsgeGFjdFN0b3BUaW1lc3RhbXAgYXMgdW5z ZXQuICovCisJeGFjdFN0b3BUaW1lc3RhbXAgPSAwOwogCiAJLyoKIAkgKiBpbml0aWFsaXpl IGN1cnJlbnQgdHJhbnNhY3Rpb24gc3RhdGUgZmllbGRzCi0tIAoyLjE5LjAKCg== --------------D7B802A9DACBDF98328E3B18--