Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pVGvp-0003ki-CQ for pgsql-hackers@arkaria.postgresql.org; Thu, 23 Feb 2023 19:07:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pVGup-00055P-Rn for pgsql-hackers@arkaria.postgresql.org; Thu, 23 Feb 2023 19:06:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pVGup-00055F-Cu for pgsql-hackers@lists.postgresql.org; Thu, 23 Feb 2023 19:06:03 +0000 Received: from mail-ot1-x332.google.com ([2607:f8b0:4864:20::332]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pVGum-00014G-LQ for pgsql-hackers@lists.postgresql.org; Thu, 23 Feb 2023 19:06:02 +0000 Received: by mail-ot1-x332.google.com with SMTP id c2-20020a9d6842000000b0068bc93e7e34so2563101oto.4 for ; Thu, 23 Feb 2023 11:06:00 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=AhW1sdWmEFBS3oGIfLSA9wkcGK8L49PibtGa3AmU+Jw=; b=LmM9NUQ4RPUsCDae4P6Y9XgDOKRCMTrZ6zlj+t4Gj+6Oj4ENVUDux5cUWv90hyZ5gU CHzcvtefjVkgAZWpspD2KD/zKUhNEAZQD399nfzzYDn2vnuWlIDYkEF53Fu9scZfGdMc 6FEJj268fQYtmO7dkzdroKpEhKkm9W3XoLo4tEz2jWlXdDzYhu3vXVKVSm9GL1FUe4Oo C1ktTrA0fLzKF2/nlTvSWmwcZTWw05z43NoPe+xWG+gP1p+v3SP5g5GGxku++z9DvzWQ 7rfw+6lQbzadWluQXShli2WBq/3fIaiaXaFQzfd9C9sTrSsmDI4EFetYN3i/ZEjK8rDH Ec8A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=AhW1sdWmEFBS3oGIfLSA9wkcGK8L49PibtGa3AmU+Jw=; b=mQdQ/QjmsqdiEtTibU2HKVXdHimIPYe9Fs4x31cwmF9o8gBGGvtLPG1Dr4lOVkFwIG tb68DC+U2kPbV8DCNa6Guw5owkeybblXhxYJzIXrVN+0xhCL31qKqydfBajd95EInIDB fJ4HVxTyYUz93WutqGpdSuCL09gQLg/KcILadjvH9DGeurxFgn/i2OjzMCDpbuxgm0AZ LDpgD9WwDog8DsPpzm0avektwrRGmB4G8IB4NgEdBHz+i9a3UL+8ZRWTIuqvwMRVz1pZ JcRhX41KAkzE71hvHR8iDQ1Ptm2CJrW9OXDtCp0MKDhhyapJrHTA9ziSVsYVFVLWGSXC CQaQ== X-Gm-Message-State: AO0yUKV2x968Xx3TzI6yMXUgJxKvZ+gMU5Vz8ERmPSz5ax/D62ypKckm PibDCkpVjWS85p645joiDN30djZNEzQ4C9uaUtE= X-Google-Smtp-Source: AK7set/iy4iQ4xVEcVGm4/VQzY1Aqzdm/FMtD/zJYLk35ci/gGX5yM8G34GVHBBkNo7U8F1KOq16xixVZiUKNwT54+E= X-Received: by 2002:a9d:710c:0:b0:693:d403:496d with SMTP id n12-20020a9d710c000000b00693d403496dmr322636otj.5.1677179159395; Thu, 23 Feb 2023 11:05:59 -0800 (PST) MIME-Version: 1.0 References: <157609.1677088525@sss.pgh.pa.us> <2f1c8cb9-6f4d-2a03-d069-c4f9c2b574ab@enterprisedb.com> <414445.1677163935@sss.pgh.pa.us> In-Reply-To: <414445.1677163935@sss.pgh.pa.us> From: Kirk Wolak Date: Thu, 23 Feb 2023 14:05:33 -0500 Message-ID: Subject: Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has) To: Tom Lane Cc: Heikki Linnakangas , Peter Eisentraut , Nikolay Samokhvalov , PostgreSQL Hackers , Pavel Stehule , amborodin86@gmail.com, Laurenz Albe , Andrey Borodin Content-Type: multipart/mixed; boundary="0000000000000567e905f562b4c9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000567e905f562b4c9 Content-Type: multipart/alternative; boundary="0000000000000567e805f562b4c7" --0000000000000567e805f562b4c7 Content-Type: text/plain; charset="UTF-8" On Thu, Feb 23, 2023 at 9:52 AM Tom Lane wrote: > Heikki Linnakangas writes: > > On 23/02/2023 13:20, Peter Eisentraut wrote: > >> If you don't have \timing turned on before the query starts, psql won't > >> record what the time was before the query, so you can't compute the run > >> time afterwards. This kind of feature would only work if you always > >> take the start time, even if \timing is turned off. > > > Correct. That seems acceptable though? gettimeofday() can be slow on > > some platforms, but I doubt it's *that* slow, that we couldn't call it > > two times per query. > > Yeah, you'd need to capture both the start and stop times even if > \timing isn't on, in case you get asked later. But the backend is > going to call gettimeofday at least once per query, likely more > depending on what features you use. And there are inherently > multiple kernel calls involved in sending a query and receiving > a response. I tend to agree with Heikki that this overhead would > be unnoticeable. (Of course, some investigation proving that > wouldn't be unwarranted.) > > regards, tom lane > Note, for this above feature, I was thinking we have a ROW_COUNT variable I use \set to see. The simplest way to add this is maybe a set variable: EXEC_TIME And it's set when ROW_COUNT gets set. +1 == Now, since this opened a lively discussion, I am officially submitting my first patch. This includes the small change to prompt.c and the documentation. I had help from Andrey Borodin, and Pavel Stehule, who have supported me in how to propose, and use gitlab, etc. We are programmers... It's literally our job to sharpen our tools. And PSQL is one of my most used. A small frustration, felt regularly was the motive. Regards, Kirk PS: If I am supposed to edit the subject to say there is a patch here, I did not know PPS: I appreciate ANY and ALL feedback... This is how we learn! --0000000000000567e805f562b4c7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Feb 23, 2023 at 9:52 AM Tom Lane = <tgl@sss.pgh.pa.us> wrote:
Heikki Linnakangas <hlinnaka@iki.fi> writes:
> On 23/02/2023 13:20, Peter Eisentraut wrote:
>> If you don't have \timing turned on before the query starts, p= sql won't
>> record what the time was before the query, so you can't comput= e the run
>> time afterwards.=C2=A0 This kind of feature would only work if you= always
>> take the start time, even if \timing is turned off.

> Correct. That seems acceptable though? gettimeofday() can be slow on <= br> > some platforms, but I doubt it's *that* slow, that we couldn't= call it
> two times per query.

Yeah, you'd need to capture both the start and stop times even if
\timing isn't on, in case you get asked later.=C2=A0 But the backend is=
going to call gettimeofday at least once per query, likely more
depending on what features you use.=C2=A0 And there are inherently
multiple kernel calls involved in sending a query and receiving
a response.=C2=A0 I tend to agree with Heikki that this overhead would
be unnoticeable.=C2=A0 (Of course, some investigation proving that
wouldn't be unwarranted.)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 regards, tom lane

Note, for = this above feature, I was thinking we have a=C2=A0 ROW_COUNT variable I use= \set to see.
The simplest way to add this is maybe a set variable:=C2= =A0 EXEC_TIME
And it's set when ROW_COUNT gets set.
+1
=

=3D=3D
Now, since this opened a lively = discussion, I am officially submitting my first patch.
This inclu= des the small change to prompt.c and the documentation.=C2=A0 I had help fr= om Andrey Borodin,
and Pavel Stehule, who have supported me in ho= w to propose, and use gitlab, etc.

We are programm= ers... It's literally our job to sharpen our tools.=C2=A0 And PSQL is o= ne of my most used.
A small frustration, felt regularly was the m= otive.

Regards, Kirk
PS: If I am suppose= d to edit the subject to say there is a patch here, I did not know
PPS: I appreciate ANY and ALL feedback... This is how we learn!
--0000000000000567e805f562b4c7-- --0000000000000567e905f562b4c9 Content-Type: text/plain; charset="US-ASCII"; name="psql-prompt-t.patch" Content-Disposition: attachment; filename="psql-prompt-t.patch" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: f_lehgvwve0 RnJvbSBlYWY2ZDA1MDI4MDUyYTNjY2FhOGQ5ODA5NTNhYzRmZDc1MjU1MjUwIE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQ0KRnJvbTogS2lyayBXb2xhayA8d29sYWtrQGdtYWlsLmNvbT4NCkRhdGU6 IFRodSwgMjMgRmViIDIwMjMgMTc6NTI6MDkgKzAwMDANClN1YmplY3Q6IFtQQVRDSF0gVGltZSBv cHRpb24gYWRkZWQgdG8gcHNxbCBwcm9tcHQNCg0KVGhpcyBhZGRzIGEgdXNlZnVsIHRpbWUgb3B0 aW9uIHRvIHRoZSBwcm9tcHQ6ICVULiBXaGljaCBkb2VzIG5vdA0KcmVxdWlyZSBhIHdhc3RlZnVs IGJhY2txdW90ZWQgc2hlbGwgY29tbWFuZCB3aGljaCBpcyBhbHNvIG5vdA0KY29tcGF0aWJsZSBi ZXR3ZWVuIG9wZXJhdGluZyBzeXN0ZW1zLg0KVGhlIGZvcm1hdCBpcyBzaW1wbHkgSEgyNDpNSTpT UyBubyBvdGhlciBvcHRpb25zIGF2YWlsYWJsZSBieSBkZXNpZ24hDQoNCkF1dGhvcjogS2lyayBX b2xhayA8d29sYWtrQGdtYWlsLmNvbT4NClJldmlld2VkLUJ5OiBBbmRyZXkgQm9yb2RpbiA8YW1i b3JvZGluQGFjbS5vcmc+DQpSZXZpZXdlZC1CeTogTmlrb2xheSBTYW1va2h2YWxvdiA8c2Ftb2to dmFsb3ZAZ21haWwuY29tPg0KVGhyZWFkOiBodHRwczovL3Bvc3Rnci5lcy9tL0NBQ0xVNW1TUndI cl84eiUzRGVuTWotblhGMXRtQzclMkJKbjVoZVpRTmlLdUx5eFlVdEwyZmclNDBtYWlsLmdtYWls LmNvbQ0KLS0tDQogZG9jL3NyYy9zZ21sL3JlZi9wc3FsLXJlZi5zZ21sIHwgIDkgKysrKysrKysr DQogc3JjL2Jpbi9wc3FsL3Byb21wdC5jICAgICAgICAgIHwgMTAgKysrKysrKysrLQ0KIDIgZmls ZXMgY2hhbmdlZCwgMTggaW5zZXJ0aW9ucygrKSwgMSBkZWxldGlvbigtKQ0KDQpkaWZmIC0tZ2l0 IGEvZG9jL3NyYy9zZ21sL3JlZi9wc3FsLXJlZi5zZ21sIGIvZG9jL3NyYy9zZ21sL3JlZi9wc3Fs LXJlZi5zZ21sDQppbmRleCBkYzY1MjhkYzExZC4uMDRhYjllZWI4YzAgMTAwNjQ0DQotLS0gYS9k b2Mvc3JjL3NnbWwvcmVmL3BzcWwtcmVmLnNnbWwNCisrKyBiL2RvYy9zcmMvc2dtbC9yZWYvcHNx bC1yZWYuc2dtbA0KQEAgLTQ1NzUsNiArNDU3NSwxNSBAQCB0ZXN0ZGI9Jmd0OyA8dXNlcmlucHV0 PklOU0VSVCBJTlRPIG15X3RhYmxlIFZBTFVFUyAoOidjb250ZW50Jyk7PC91c2VyaW5wdXQ+DQog ICAgICAgICA8L2xpc3RpdGVtPg0KICAgICAgIDwvdmFybGlzdGVudHJ5Pg0KIA0KKyAgICAgIDx2 YXJsaXN0ZW50cnkgaWQ9ImFwcC1wc3FsLXByb21wdGluZy10LXVjIj4NCisgICAgICAgIDx0ZXJt PjxsaXRlcmFsPiVUPC9saXRlcmFsPjwvdGVybT4NCisgICAgICAgIDxsaXN0aXRlbT4NCisgICAg ICAgICA8cGFyYT4NCisgICAgICAgICAgVGhlIGN1cnJlbnQgdGltZSBvbiB0aGUgY2xpZW50IGlu IEhIMjQ6TUk6U1MgZm9ybWF0Lg0KKyAgICAgICAgIDwvcGFyYT4NCisgICAgICAgIDwvbGlzdGl0 ZW0+DQorICAgICAgPC92YXJsaXN0ZW50cnk+DQorDQogICAgICAgPHZhcmxpc3RlbnRyeSBpZD0i YXBwLXBzcWwtcHJvbXB0aW5nLXgiPg0KICAgICAgICAgPHRlcm0+PGxpdGVyYWw+JXg8L2xpdGVy YWw+PC90ZXJtPg0KICAgICAgICAgPGxpc3RpdGVtPg0KZGlmZiAtLWdpdCBhL3NyYy9iaW4vcHNx bC9wcm9tcHQuYyBiL3NyYy9iaW4vcHNxbC9wcm9tcHQuYw0KaW5kZXggOTY5Y2Q5OTA4ZTUuLmE1 OTBjMjczODliIDEwMDY0NA0KLS0tIGEvc3JjL2Jpbi9wc3FsL3Byb21wdC5jDQorKysgYi9zcmMv YmluL3BzcWwvcHJvbXB0LmMNCkBAIC00MSw2ICs0MSw3IEBADQogICoJCQlvciBhICEgaWYgc2Vz c2lvbiBpcyBub3QgY29ubmVjdGVkIHRvIGEgZGF0YWJhc2U7DQogICoJCWluIHByb21wdDIgLSwg KiwgJywgb3IgIjsNCiAgKgkJaW4gcHJvbXB0MyBub3RoaW5nDQorICogJVQgLSB0aW1lIGluIEhI MjQ6TUk6U1MgZm9ybWF0DQogICogJXggLSB0cmFuc2FjdGlvbiBzdGF0dXM6IGVtcHR5LCAqLCAh LCA/ICh1bmtub3duIG9yIG5vIGNvbm5lY3Rpb24pDQogICogJWwgLSBUaGUgbGluZSBudW1iZXIg aW5zaWRlIHRoZSBjdXJyZW50IHN0YXRlbWVudCwgc3RhcnRpbmcgZnJvbSAxLg0KICAqICU/IC0g dGhlIGVycm9yIGNvZGUgb2YgdGhlIGxhc3QgcXVlcnkgKG5vdCB5ZXQgaW1wbGVtZW50ZWQpDQpA QCAtMjIzLDcgKzIyNCwxNCBAQCBnZXRfcHJvbXB0KHByb21wdFN0YXR1c190IHN0YXR1cywgQ29u ZGl0aW9uYWxTdGFjayBjc3RhY2spDQogCQkJCQkJCWJyZWFrOw0KIAkJCQkJfQ0KIAkJCQkJYnJl YWs7DQotDQorCQkJCQkvKiBvdXRwdXQgSEgyNDpNSTpTUyAqLw0KKwkJCQljYXNlICdUJzoNCisJ CQkJCXsNCisJCQkJCQl0aW1lX3QgY3VycmVudF90aW1lID0gdGltZShOVUxMKTsNCisJCQkJCQlz dHJ1Y3QgdG0gKnRtX2luZm8gPSBsb2NhbHRpbWUoJmN1cnJlbnRfdGltZSk7DQorCQkJCQkJc3By aW50ZihidWYsICIlMDJkOiUwMmQ6JTAyZCIsIHRtX2luZm8tPnRtX2hvdXIsIHRtX2luZm8tPnRt X21pbiwgdG1faW5mby0+dG1fc2VjKTsJCQkJDQorCQkJCQl9CQkJCQkNCisJCQkJCWJyZWFrOw0K IAkJCQljYXNlICd4JzoNCiAJCQkJCWlmICghcHNldC5kYikNCiAJCQkJCQlidWZbMF0gPSAnPyc7 DQotLSANCkdpdExhYg== --0000000000000567e905f562b4c9--