Can you use fzf for psql history?

FZF is quite popular tool for fuzzy string finder. Very helpful for checking history of commands.

But, can I use it in psql?

Initially, the tests seemed promising. Within psql, I can:

=$ \SET x `fzf < :HISTFILE`

And then I can:

$ :x
                   age
──────────────────────────────────────────
 14 years 11 mons 28 days 14:16:01.493027
(1 ROW)

But there are immediately problems.

Specifically:

  1. History file is written when psql exists. So if I'd run a command, and immediately afterwards ran the fzf < :HISTFILE - it will not have the latest command
  2. While I can run the command that I had previously, I can't edit it.

First problem is actually mostly simple to solve, instead of relying on HISTFILE, use \s command in psql to write history, including newest elements, and then run fzf on this.

But what about ability to edit the command?

I don't think it can be done without hacking psql itself. But luckily there is a workaround. I usually run psql inside tmux. So we can abuse this. A bit.

I will need this small script for psql:

\set prevpager `echo $PAGER`
\setenv PAGER 'tmux send-keys -t "${TMUX_PANE}" "$( fzf | sed "s/;$/\\\\\\;/" )"'
\s
\setenv PAGER :prevpager

What it does?

Well, it's relatively simple:

  1. saves current value of environment variable PAGER to helper psql variable prevpager - this is so that we can restore PAGER to what it was before after we're done.
  2. sets PAGER env to one-liner that does the magic (more on it in a moment)
  3. calls psql's \s command, which basically outputs current history, including unsaved lines, to screen, but via PAGER, if it's set
  4. finally, sets PAGER back to whatever it was

The whole magic is in PAGER line. What it does?

First you have to know that when inside tmux you have $TMUX_PANE variable set, which gives you unique identifier (within given tmux server) of your current PANE. The value looks like %123, and is irrelevant for us, but needed for tmux.

Then, tmux has command send-keys that sends given input string to given pane as keyboard input. For example, I could:

=$ tmux send-keys -t some_pane "ls -l" <enter>

And it would send ls -l, with enter, to run the command, in some_pane pane/window. VERY useful.

Finally, we give it output of fzf command, which gets data from stdin, presents it, and outputs one, selected, line.

There is a problem, though, that tmux will cause some problems is last character of pasted string is ;. But we can escape it, and the problem is gone. Unfortunately, due to many levels of escaping, we need 6 backslashes. Ugly. But works.

Let's save this script as ~/.psql-fzf, and now we can see how that works:

Looks pretty cool. Of course my window in the above screenshot is small, but all of this is configurable.

One last thing is, how can I make it be called by doing normal ctrl-r?

For this, we can use the fact that psql is using readline library.

First, let's see if that's really the case. On some systems (like debian/ubuntu linux) psql that you run is small wrapper, so we have to find real psql. This can be done from inside of psql using \! commands.

The commands I'm about to show are working on linux, sorry, but I don't have any Mac or Windows computers around to see how it works there.

First, we need to get pid of the psql, this can be done, inside psql, with:

$ \! ps o ppid -p $$
   PPID
2006321

So, now I know that my psql should have pid 2006321. Sanity check:

$ \! ps -p 2006321
    PID TTY          TIME CMD
2006321 pts/14   00:00:00 psql

Great. It shows that the cmd is psql. All well, and now, I can use ldd program to see what libraries given program uses, and search (grep) in them for readline:

$ \! ldd /home/pgdba/work/bin/psql | grep -i readline
        libreadline.so.8 => /lib/x86_64-linux-gnu/libreadline.so.8 (0x00007f7ad0d8d000)

Sweet. We see that psql that I use uses readline. And as such, it is fully configurable with relatively uncommon file: ~/.inputrc.

Inside this file (created if necessary) I can add:

$if Psql
    "\C-r":"\\i ~/.psql-fzf\n"
$endif

This thing means that inside psql (hence the $if), if user will press ctrl-c, readline should treat it as if they typed \i ~/.psql-fzf and pressed enter. There are two backslashes because, as usually, escaping needs to be done.

With this in place, I can simply press ctrl-r inside psql, and it will work:

you can see \i ... but that's because readline is typing it. I just pressed ctrl-r.

There is one issue though. What will happen if I will do it in psql that isn't inside tmux? That will not work, in best case. Worst case - it can cause problems.

Unfortunately we can't (or I don't know how) how to make inputrc change its content depending on whether we're in tmux, or not. We could, of course, make a wrapper around psql, to start tmux always, or perhaps run with different inputrc depending on case. But instead, perhaps, instead of ctrl-r, use some other shortcut for fzf-powered history? For example: ctrl-f ?

Trivial change to ~/.inputrc:

$if Psql
    "\C-f":"\\i ~/.psql-fzf\n"
$endif

and now I can have both normal history search, and fzf based one:

Finally, I could get away with not using ~/.psql-fzf file, and instead put all the commands in there (not so many of them) to ~/.inputrc, but the escaping so many backslashes would be a nightmare. I think that even splitting psql-fzf into two files, one just calling fzf on input, doing the sed, and calling tmux send-keys would be beneficial in terms of readability.

In such case we would have:

  • ~/.psql-fzf : 
    \set prevpager `echo $PAGER`
\setenv PAGER '~/.psql-fzf-pager'
\s
\setenv PAGER :prevpager
  • ~/.psql-fzf-pager : 
    #!/usr/bin/env bash
 
# Call fzf to pick one line from history
fzf_out="$( fzf )"
 
# If in tmux put the line from history into terminal, as if typed
if [[ "${TMUX_PANE}" =~ ^%[0-9]+$ ]]
then
    # Escape trailing semicolon
    fzf_out="$( sed 's/;$/\\;/' <<< "${fzf_out}" )"
 
    tmux send-keys -t "${TMUX_PANE}" "${fzf_out}"
else
    # If not, just print it to screen
    printf '%s\n' "${fzf_out}"
fi
  • ~/.inputrc : 
    $if Psql
    "\C-f":"\\i ~/.psql-fzf\n"
$endif

This version of the pager code will also be smarter about not using tmux when not inside tmux. Not by a lot, but a bit smarter :)

Final word: I was planning to write this for quite some time now, but lately nickb from IRC prodded me, so if you end up using this, you have him to thank for making me finally write it :)