Thread: Would it be possible to add functions to tab-completion in psql?

Hi,
As a dba I have to, very often, query system functions, starting with
pg_last_xact_replay_timestamp and pg_current_wal_lsn.

Would it be possible/hard/expensive, to change tab-completion so that:

select pg_<tab> would work?

Best regards,

depesz

On 8/16/22 07:28, hubert depesz lubaczewski wrote:
> Hi,
> As a dba I have to, very often, query system functions, starting with
> pg_last_xact_replay_timestamp and pg_current_wal_lsn.
>
> Would it be possible/hard/expensive, to change tab-completion so that:
>
> select pg_<tab> would work?

It does, in the psql 9.6 and 12 that I installed from it's native 
repositories, and the psql 12 installed from RHEL 7/8 native repositories.

-- 
Angular momentum makes the world go 'round.

On Tue, Aug 16, 2022 at 07:42:27AM -0500, Ron wrote:
> On 8/16/22 07:28, hubert depesz lubaczewski wrote:
> > Hi,
> > As a dba I have to, very often, query system functions, starting with
> > pg_last_xact_replay_timestamp and pg_current_wal_lsn.
> > 
> > Would it be possible/hard/expensive, to change tab-completion so that:
> > 
> > select pg_<tab> would work?
> 
> It does, in the psql 9.6 and 12 that I installed from it's native
> repositories, and the psql 12 installed from RHEL 7/8 native repositories.

Hmm .. I'm on Pg 16, linked with readline, and pressing tab after
select pg_
just produces new prompt:

https://asciinema.org/a/A8w16KhXF7bK4iz7hE7iyyo0D

Can you please show me this working, with "which psql", and "ldd $(
which psql )" using asciinema?

Best regards,

depesz

On 8/16/22 08:01, hubert depesz lubaczewski wrote:

On Tue, Aug 16, 2022 at 07:42:27AM -0500, Ron wrote:

On 8/16/22 07:28, hubert depesz lubaczewski wrote:

Hi,
As a dba I have to, very often, query system functions, starting with
pg_last_xact_replay_timestamp and pg_current_wal_lsn.

Would it be possible/hard/expensive, to change tab-completion so that:

select pg_<tab> would work?

It does, in the psql 9.6 and 12 that I installed from it's native
repositories, and the psql 12 installed from RHEL 7/8 native repositories.

Hmm .. I'm on Pg 16, linked with readline, and pressing tab after

Pg 16??

select pg_
just produces new prompt:

https://asciinema.org/a/A8w16KhXF7bK4iz7hE7iyyo0D

Can you please show me this working, with "which psql", and "ldd $(
which psql )" using asciinema?

$ alias psql12
alias psql12='/usr/lib/postgresql/12/bin/psql -p5433'

$ psql12
psql (12.11 (Ubuntu 12.11-1.pgdg18.04+1))
Type "help" for help.

postgres=# select * from pg_<tab>
Display all 130 possibilities? (y or n)


$ ldd /usr/lib/postgresql/12/bin/psql
    linux-vdso.so.1 (0x00007ffe9dfc3000)
    libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x00007f40d0cc0000)
    libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f40d0aa0000)
    libedit.so.2 => /usr/lib/x86_64-linux-gnu/libedit.so.2 (0x00007f40d0868000)
    librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f40d0660000)
    libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f40d02c0000)
    libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f40cfec8000)
    libssl.so.1.1 => /usr/lib/x86_64-linux-gnu/libssl.so.1.1 (0x00007f40cfc38000)
    libcrypto.so.1.1 => /usr/lib/x86_64-linux-gnu/libcrypto.so.1.1 (0x00007f40cf768000)
    libgssapi_krb5.so.2 => /usr/lib/x86_64-linux-gnu/libgssapi_krb5.so.2 (0x00007f40cf518000)
    libldap_r-2.4.so.2 => /usr/lib/x86_64-linux-gnu/libldap_r-2.4.so.2 (0x00007f40cf2c0000)
    /lib64/ld-linux-x86-64.so.2 (0x00007f40d11c0000)
    libtinfo.so.5 => /lib/x86_64-linux-gnu/libtinfo.so.5 (0x00007f40cf090000)
    libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f40cee88000)
    libkrb5.so.3 => /usr/lib/x86_64-linux-gnu/libkrb5.so.3 (0x00007f40cebb0000)
    libk5crypto.so.3 => /usr/lib/x86_64-linux-gnu/libk5crypto.so.3 (0x00007f40ce978000)
    libcom_err.so.2 => /lib/x86_64-linux-gnu/libcom_err.so.2 (0x00007f40ce770000)
    libkrb5support.so.0 => /usr/lib/x86_64-linux-gnu/libkrb5support.so.0 (0x00007f40ce560000)
    liblber-2.4.so.2 => /usr/lib/x86_64-linux-gnu/liblber-2.4.so.2 (0x00007f40ce350000)
    libresolv.so.2 => /lib/x86_64-linux-gnu/libresolv.so.2 (0x00007f40ce130000)
    libsasl2.so.2 => /usr/lib/x86_64-linux-gnu/libsasl2.so.2 (0x00007f40cdf10000)
    libgssapi.so.3 => /usr/lib/x86_64-linux-gnu/libgssapi.so.3 (0x00007f40cdcc8000)
    libgnutls.so.30 => /usr/lib/x86_64-linux-gnu/libgnutls.so.30 (0x00007f40cd960000)
    libkeyutils.so.1 => /lib/x86_64-linux-gnu/libkeyutils.so.1 (0x00007f40cd758000)
    libheimntlm.so.0 => /usr/lib/x86_64-linux-gnu/libheimntlm.so.0 (0x00007f40cd548000)
    libkrb5.so.26 => /usr/lib/x86_64-linux-gnu/libkrb5.so.26 (0x00007f40cd2b8000)
    libasn1.so.8 => /usr/lib/x86_64-linux-gnu/libasn1.so.8 (0x00007f40cd010000)
    libhcrypto.so.4 => /usr/lib/x86_64-linux-gnu/libhcrypto.so.4 (0x00007f40ccdd8000)
    libroken.so.18 => /usr/lib/x86_64-linux-gnu/libroken.so.18 (0x00007f40ccbc0000)
    libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007f40cc9a0000)
    libp11-kit.so.0 => /usr/lib/x86_64-linux-gnu/libp11-kit.so.0 (0x00007f40cc670000)
    libidn2.so.0 => /usr/lib/x86_64-linux-gnu/libidn2.so.0 (0x00007f40cc450000)
    libunistring.so.2 => /usr/lib/x86_64-linux-gnu/libunistring.so.2 (0x00007f40cc0d0000)
    libtasn1.so.6 => /usr/lib/x86_64-linux-gnu/libtasn1.so.6 (0x00007f40cbeb8000)
    libnettle.so.6 => /usr/lib/x86_64-linux-gnu/libnettle.so.6 (0x00007f40cbc80000)
    libhogweed.so.4 => /usr/lib/x86_64-linux-gnu/libhogweed.so.4 (0x00007f40cba48000)
    libgmp.so.10 => /usr/lib/x86_64-linux-gnu/libgmp.so.10 (0x00007f40cb7c0000)
    libwind.so.0 => /usr/lib/x86_64-linux-gnu/libwind.so.0 (0x00007f40cb590000)
    libheimbase.so.1 => /usr/lib/x86_64-linux-gnu/libheimbase.so.1 (0x00007f40cb380000)
    libhx509.so.5 => /usr/lib/x86_64-linux-gnu/libhx509.so.5 (0x00007f40cb130000)
    libsqlite3.so.0 => /usr/lib/x86_64-linux-gnu/libsqlite3.so.0 (0x00007f40cae20000)
    libcrypt.so.1 => /lib/x86_64-linux-gnu/libcrypt.so.1 (0x00007f40cabe8000)
    libffi.so.6 => /usr/lib/x86_64-linux-gnu/libffi.so.6 (0x00007f40ca9e0000)

Hi,

On Tue, Aug 16, 2022 at 02:28:49PM +0200, hubert depesz lubaczewski wrote:
> Hi,
> As a dba I have to, very often, query system functions, starting with
> pg_last_xact_replay_timestamp and pg_current_wal_lsn.
>
> Would it be possible/hard/expensive, to change tab-completion so that:
>
> select pg_<tab> would work?

That specific example may be easy to do, but others like e.g.

SELECT pg_size_pretty(pg_rel<TAB>
SELECT pg_last_xact_replay_timestamp(), pg_current_w<TAB>

wouldn't.

You can refer to [1] and [2] threads for more background, but the limitations
that prevented anything from being committed until now still exist so I'm not
really hoping for anything new on that side :(  I usually have another psql
running somewhere, where I can run \df and similar, and copy/paste stuff
around.  That's not great, but still better than trying to remember the exact
spelling of all useful functions.

[1] https://www.postgresql.org/message-id/flat/CAMyN-kB_xrU4iYdcF1j%3DtijgO1DSyjtb3j96O4UEj91XZrZcMg%40mail.gmail.com
[2] https://www.postgresql.org/message-id/flat/1328820579.11241.4.camel%40vanquo.pezone.net

On Tue, Aug 16, 2022 at 09:55:34PM +0800, Julien Rouhaud wrote:
> On Tue, Aug 16, 2022 at 02:28:49PM +0200, hubert depesz lubaczewski wrote:
> > Hi,
> > As a dba I have to, very often, query system functions, starting with
> > pg_last_xact_replay_timestamp and pg_current_wal_lsn.
> >
> > Would it be possible/hard/expensive, to change tab-completion so that:
> >
> > select pg_<tab> would work?
> That specific example may be easy to do, but others like e.g.
> SELECT pg_size_pretty(pg_rel<TAB>

While I would appreciate having full tab-completion on every level,
I wouldn't be happy with just first-level.

Basically I feel that "because we can't get it perfect" it obscuring the
fact that imperfect would be very useful.

Best regards,

depesz

On Tue, Aug 16, 2022 at 08:51:49AM -0500, Ron wrote:
> On 8/16/22 08:01, hubert depesz lubaczewski wrote:
> > On Tue, Aug 16, 2022 at 07:42:27AM -0500, Ron wrote:
> > > On 8/16/22 07:28, hubert depesz lubaczewski wrote:
> > > > Hi,
> > > > As a dba I have to, very often, query system functions, starting with
> > > > pg_last_xact_replay_timestamp and pg_current_wal_lsn.
> > > > 
> > > > Would it be possible/hard/expensive, to change tab-completion so that:
> > > > 
> > > > select pg_<tab> would work?
> > > It does, in the psql 9.6 and 12 that I installed from it's native
> > > repositories, and the psql 12 installed from RHEL 7/8 native repositories.
> > Hmm .. I'm on Pg 16, linked with readline, and pressing tab after
> 
> Pg *16*??

Yes. I use dev pg on daily basis.

> > select pg_
> > just produces new prompt:
> > 
> > https://asciinema.org/a/A8w16KhXF7bK4iz7hE7iyyo0D
> > 
> > Can you please show me this working, with "which psql", and "ldd $(
> > which psql )" using asciinema?
> 
> $ alias psql12
> alias psql12='/usr/lib/postgresql/12/bin/psql -p5433'
> 
> $ psql12
> psql (12.11 (Ubuntu 12.11-1.pgdg18.04+1))
> Type "help" for help.
> 
> postgres=# select * from pg_<tab>
> Display all 130 possibilities? (y or n)

Please note that this example is not really relevant to what I asked
about.

First of all, I asked about `select pg_<tab>`, and not `select * from
pg_<tab>`, second this tab completion lists tables and views that have
names starting with pg_*.

And I asked about functions.

I know we can tab-complete relations. But we can't functions.

Best regards,

depesz

hubert depesz lubaczewski <depesz@depesz.com> writes:
> I know we can tab-complete relations. But we can't functions.

There is nothing principled about assuming that the first word
after SELECT is a function name.  It'd be even less principled to
provide tab completion only for function names beginning with
"pg_".  So this idea seems like a wart rather than something
anybody would think is a nice improvement.

            regards, tom lane

On Tue, Aug 16, 2022 at 10:10:55AM -0400, Tom Lane wrote:
> There is nothing principled about assuming that the first word
> after SELECT is a function name.  It'd be even less principled to
> provide tab completion only for function names beginning with
> "pg_".  So this idea seems like a wart rather than something
> anybody would think is a nice improvement.

While I understand that there is nothing that would suggest it, is there
any reason why providing a thing that can legally be there would be bad
idea?

I understand that someone might want to enter pg_or_not_pg (column
name from some table), or perhaps pg.some_column_name - but what is the
harm of providing pg_* functions for pg_<tab>?

Specifically, I'd ask what is the harm of increasing what tab completion
can do by a lot - for example, make it tab-complete fields from all
tables. And make it possible to tab-complete column name anywhere in
where clause. But function name in select would be (for me(!)) great
first step, and I can't really see the drawbacks, aside from using
developer time to work on it.

Best regards,

depesz

> As a dba I have to, very often, query system functions, starting with
> pg_last_xact_replay_timestamp and pg_current_wal_lsn.
> 
> Would it be possible/hard/expensive, to change tab-completion so that:
> 
> select pg_<tab> would work?

Not what you asked for, but anyway, I use
\sf pg_<tab> and copy the desired name to fix similar problems. 

> 

On 16.08.22 16:14, hubert depesz lubaczewski wrote:
> Specifically, I'd ask what is the harm of increasing what tab completion
> can do by a lot - for example, make it tab-complete fields from all
> tables. And make it possible to tab-complete column name anywhere in
> where clause. But function name in select would be (for me(!)) great
> first step, and I can't really see the drawbacks, aside from using
> developer time to work on it.

I think in this case it would be straightforward to write a patch and 
then we can see what the experience is in practice.  I agree this could 
be a useful feature.