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 1pXAdG-0004cY-Iy for pgsql-hackers@arkaria.postgresql.org; Wed, 01 Mar 2023 00:47:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pXAdF-0005VL-ED for pgsql-hackers@arkaria.postgresql.org; Wed, 01 Mar 2023 00:47:45 +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 1pXAdF-0005VC-0v for pgsql-hackers@lists.postgresql.org; Wed, 01 Mar 2023 00:47:45 +0000 Received: from mail-oo1-xc33.google.com ([2607:f8b0:4864:20::c33]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pXAdC-0006m3-8F for pgsql-hackers@lists.postgresql.org; Wed, 01 Mar 2023 00:47:44 +0000 Received: by mail-oo1-xc33.google.com with SMTP id e12-20020a4ae0cc000000b00525034ca5e9so1869673oot.0 for ; Tue, 28 Feb 2023 16:47:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1677631661; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=16ZJCz38sj4tj+3T6FhIXwwAcVFFc/gq0jfIp0GHmIo=; b=T9AjWPCKwESaB6yiL0fs2536SYCQnWjKf8comI3xlSmkr7PihinCU5JTLQeNaRNtPy 2+VDpvnQyYY5FAuSPVpYGyNSp8/iDHnAvGaxYpQSeAUB3K1eJ60Vtucs+8DGvzdgPPBU u2KYdtfcSEEV/4gy+8Kn+NlMATwhwSDaK6ew4tgu7xbiYyAJfx/DqxsNY+rTzEmDRdN7 VEFUBWFYm60YbvuMC4TDGzGxm9BT/C2f0VBkfn/R6QX43O2gBJqtJLd0Yg/PlWrvdss9 ZPyizec2tfr2zBIxxg1XGDApmuS5LkBFZyAGMxwIMT4oFtrFB8ZjkZgXiLNeNJCYl88p qo1Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1677631661; 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=16ZJCz38sj4tj+3T6FhIXwwAcVFFc/gq0jfIp0GHmIo=; b=ntXjn0jsypUUSPJPHgjm5MEI4Hk3ow2ylTsgwbKa5kY/QSZrz3Wl+N8U+A7lt64beB gq19N0jzr2BKajV1I6ep/EUqyReTTCqH/YPZ9RmNiTnw2eQziLy+eWlcIcloywNY5JXQ SDOHUQiq7GqyUKl2ZrFtFQ/22R/0xWeKyrSZl3HB/LiH2f6PH3NjfPPD3J+r9P+hpfAY 9M00sAkK/MCw5NLSBmScaLnMxp9kJgKF7L90J42C9+/fo/+zhFZDcXVgnlFys+U4NGRJ JXZ/qV1IS+Hsy2zTXkRbpoRhFTHvvz+lrX0eV8E2BM10eCZxMxn6ELBT3A/hoa8N9bLo SVVQ== X-Gm-Message-State: AO0yUKWjKwUgvbPx6FwZnfXIKbCOthiUiNZtFxLQnuh0b88bjfsQV8L/ Nr++Ghqzpko+DgUDZvjTf0VWXTqpXwftaetQ3PM= X-Google-Smtp-Source: AK7set83Wzq9JE479R+7jhize8iFJd5bKIyaK2f9W9z3hrQVznbjkbzdb6dyeR5gZq+Cld7Joss1CO9ovYausisdVZ0= X-Received: by 2002:a4a:874e:0:b0:525:39e1:9492 with SMTP id a14-20020a4a874e000000b0052539e19492mr1522212ooi.0.1677631661663; Tue, 28 Feb 2023 16:47:41 -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: From: Kirk Wolak Date: Tue, 28 Feb 2023 19:47:15 -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="00000000000042201c05f5cc0f15" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000042201c05f5cc0f15 Content-Type: multipart/alternative; boundary="00000000000042201905f5cc0f13" --00000000000042201905f5cc0f13 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Feb 23, 2023 at 2:05=E2=80=AFPM Kirk Wolak wrote= : > 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 r= un >> >> 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 variabl= e > 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 > > =3D=3D > 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! > Patch Posted with one edit, for line editings (Thanks Jim!) --00000000000042201905f5cc0f13 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Feb 23, 2023 at 2:05=E2=80= =AFPM Kirk Wolak <wolakk@gmail.com> wrote:
Heikki Linnaka= ngas <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!

Patch Posted with one edit, for line ed= itings (Thanks Jim!)

=C2=A0
--00000000000042201905f5cc0f13-- --00000000000042201c05f5cc0f15 Content-Type: text/plain; charset="US-ASCII"; name="v2-0001-Time-option-added-to-psql-prompt.patch" Content-Disposition: attachment; filename="v2-0001-Time-option-added-to-psql-prompt.patch" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: f_leoyisvj0 RnJvbSBiOWRiMTU3MTc3YmJkZWVlYjZkMzVjMzYyM2NhOTM1NTE0MTQxOWQ3IE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBKaW0gSm9uZXMgPGppbS5qb25lc0B1bmktbXVlbnN0ZXIuZGU+ CkRhdGU6IFdlZCwgMSBNYXIgMjAyMyAwMDowNzo1NSArMDEwMApTdWJqZWN0OiBbUEFUQ0ggdjJd IFRpbWUgb3B0aW9uIGFkZGVkIHRvIHBzcWwgcHJvbXB0CgpUaGlzIGFkZHMgYSB1c2VmdWwgdGlt ZSBvcHRpb24gdG8gdGhlIHByb21wdDogJVQuIFdoaWNoIGRvZXMgbm90CnJlcXVpcmUgYSB3YXN0 ZWZ1bCBiYWNrcXVvdGVkIHNoZWxsIGNvbW1hbmQgd2hpY2ggaXMgYWxzbyBub3QKY29tcGF0aWJs ZSBiZXR3ZWVuIG9wZXJhdGluZyBzeXN0ZW1zLgpUaGUgZm9ybWF0IGlzIHNpbXBseSBISDI0Ok1J OlNTIG5vIG90aGVyIG9wdGlvbnMgYXZhaWxhYmxlIGJ5IGRlc2lnbiEKCkF1dGhvcjogS2lyayBX b2xhayA8d29sYWtrQGdtYWlsLmNvbT4KUmV2aWV3ZWQtQnk6IEFuZHJleSBCb3JvZGluIDxhbWJv cm9kaW5AYWNtLm9yZz4KUmV2aWV3ZWQtQnk6IE5pa29sYXkgU2Ftb2todmFsb3YgPHNhbW9raHZh bG92QGdtYWlsLmNvbT4KVGhyZWFkOiBodHRwczovL3Bvc3Rnci5lcy9tL0NBQ0xVNW1TUndIcl84 eiUzRGVuTWotblhGMXRtQzclMkJKbjVoZVpRTmlLdUx5eFlVdEwyZmclNDBtYWlsLmdtYWlsLmNv bQotLS0KIGRvYy9zcmMvc2dtbC9yZWYvcHNxbC1yZWYuc2dtbCB8ICA5ICsrKysrKysrKwogc3Jj L2Jpbi9wc3FsL3Byb21wdC5jICAgICAgICAgIHwgMTAgKysrKysrKysrLQogMiBmaWxlcyBjaGFu Z2VkLCAxOCBpbnNlcnRpb25zKCspLCAxIGRlbGV0aW9uKC0pCgpkaWZmIC0tZ2l0IGEvZG9jL3Ny Yy9zZ21sL3JlZi9wc3FsLXJlZi5zZ21sIGIvZG9jL3NyYy9zZ21sL3JlZi9wc3FsLXJlZi5zZ21s CmluZGV4IGRjNjUyOGRjMTEuLjA0YWI5ZWViOGMgMTAwNjQ0Ci0tLSBhL2RvYy9zcmMvc2dtbC9y ZWYvcHNxbC1yZWYuc2dtbAorKysgYi9kb2Mvc3JjL3NnbWwvcmVmL3BzcWwtcmVmLnNnbWwKQEAg LTQ1NzUsNiArNDU3NSwxNSBAQCB0ZXN0ZGI9Jmd0OyA8dXNlcmlucHV0PklOU0VSVCBJTlRPIG15 X3RhYmxlIFZBTFVFUyAoOidjb250ZW50Jyk7PC91c2VyaW5wdXQ+CiAgICAgICAgIDwvbGlzdGl0 ZW0+CiAgICAgICA8L3Zhcmxpc3RlbnRyeT4KIAorICAgICAgPHZhcmxpc3RlbnRyeSBpZD0iYXBw LXBzcWwtcHJvbXB0aW5nLXQtdWMiPgorICAgICAgICA8dGVybT48bGl0ZXJhbD4lVDwvbGl0ZXJh bD48L3Rlcm0+CisgICAgICAgIDxsaXN0aXRlbT4KKyAgICAgICAgIDxwYXJhPgorICAgICAgICAg IFRoZSBjdXJyZW50IHRpbWUgb24gdGhlIGNsaWVudCBpbiBISDI0Ok1JOlNTIGZvcm1hdC4KKyAg ICAgICAgIDwvcGFyYT4KKyAgICAgICAgPC9saXN0aXRlbT4KKyAgICAgIDwvdmFybGlzdGVudHJ5 PgorCiAgICAgICA8dmFybGlzdGVudHJ5IGlkPSJhcHAtcHNxbC1wcm9tcHRpbmcteCI+CiAgICAg ICAgIDx0ZXJtPjxsaXRlcmFsPiV4PC9saXRlcmFsPjwvdGVybT4KICAgICAgICAgPGxpc3RpdGVt PgpkaWZmIC0tZ2l0IGEvc3JjL2Jpbi9wc3FsL3Byb21wdC5jIGIvc3JjL2Jpbi9wc3FsL3Byb21w dC5jCmluZGV4IDk2OWNkOTkwOGUuLjBjMGM3MjVkZjUgMTAwNjQ0Ci0tLSBhL3NyYy9iaW4vcHNx bC9wcm9tcHQuYworKysgYi9zcmMvYmluL3BzcWwvcHJvbXB0LmMKQEAgLTQxLDYgKzQxLDcgQEAK ICAqCQkJb3IgYSAhIGlmIHNlc3Npb24gaXMgbm90IGNvbm5lY3RlZCB0byBhIGRhdGFiYXNlOwog ICoJCWluIHByb21wdDIgLSwgKiwgJywgb3IgIjsKICAqCQlpbiBwcm9tcHQzIG5vdGhpbmcKKyAq ICVUIC0gdGltZSBpbiBISDI0Ok1JOlNTIGZvcm1hdAogICogJXggLSB0cmFuc2FjdGlvbiBzdGF0 dXM6IGVtcHR5LCAqLCAhLCA/ICh1bmtub3duIG9yIG5vIGNvbm5lY3Rpb24pCiAgKiAlbCAtIFRo ZSBsaW5lIG51bWJlciBpbnNpZGUgdGhlIGN1cnJlbnQgc3RhdGVtZW50LCBzdGFydGluZyBmcm9t IDEuCiAgKiAlPyAtIHRoZSBlcnJvciBjb2RlIG9mIHRoZSBsYXN0IHF1ZXJ5IChub3QgeWV0IGlt cGxlbWVudGVkKQpAQCAtMjIzLDcgKzIyNCwxNCBAQCBnZXRfcHJvbXB0KHByb21wdFN0YXR1c190 IHN0YXR1cywgQ29uZGl0aW9uYWxTdGFjayBjc3RhY2spCiAJCQkJCQkJYnJlYWs7CiAJCQkJCX0K IAkJCQkJYnJlYWs7Ci0KKwkJCQkJLyogb3V0cHV0IEhIMjQ6TUk6U1MgKi8KKwkJCQljYXNlICdU JzoKKwkJCQkJeworCQkJCQkJdGltZV90IGN1cnJlbnRfdGltZSA9IHRpbWUoTlVMTCk7CisJCQkJ CQlzdHJ1Y3QgdG0gKnRtX2luZm8gPSBsb2NhbHRpbWUoJmN1cnJlbnRfdGltZSk7CisJCQkJCQlz cHJpbnRmKGJ1ZiwgIiUwMmQ6JTAyZDolMDJkIiwgdG1faW5mby0+dG1faG91ciwgdG1faW5mby0+ dG1fbWluLCB0bV9pbmZvLT50bV9zZWMpOworCQkJCQl9CisJCQkJCWJyZWFrOwogCQkJCWNhc2Ug J3gnOgogCQkJCQlpZiAoIXBzZXQuZGIpCiAJCQkJCQlidWZbMF0gPSAnPyc7Ci0tIAoyLjI1LjEK Cg== --00000000000042201c05f5cc0f15--