libreadline is not used and command line history is not available

[hammady]

Is there a particular reason why libreadline is not used so that all invaluable command line features (like history, reverse search, ...) are not available? psql supports this out of the box and it looks for libreadline in the path (while configuring), or can be configured to look into custom paths.

[jakob]

PostgresApp is compiled with libedit, which is a replacement for the GNU readline library.

Could you elaborate a bit? Which version of PostgresApp are you using? What OS are you on? What exactly doesn't work?

And most important, how do you launch psql? Are you sure you are launching psql included with PostgresApp instead of the system-provided psql (before Mavericks) or another version installed by eg. MacPorts?

[hammady]

I am using PostgresApp 9.3.0.0 on Mavericks.
I have already included PostgresApp in my PATH so that psql launches the correct binary.

$ which psql
/Applications/Postgres.app/Contents/MacOS/bin/psql

My problem is in Ctrl+r which should bring in reverse-search, but it doesn't work.

[jakob]

It seems that libedit lacks advanced features of libreadline.

I don't know why Postgresapp uses libedit instead of libreadline (@mattt might know). I can only assume it is because of licensing issues. I found a lengthy thread on the psql-hackers list related to this topic http://postgresql.1045698.n5.nabble.com/Debian-readline-libedit-breakage-td3380317.html

I assume we could just get rid of libedit and link against the system provided libreadline, but I'm not totally sure if that complies with the GPL.

[jakob]

In the latest version I tried to compile with readline, but for some reason I just couldn't get that to work. It always linked with libedit instead. If anybody finds out how to build postgresql with readline, please let me know.

[danp]

Having trouble managing history especially with large queries I've constructed with \e in my editor, seems likely it's related to using libedit instead of readline.

[abatko]

@dpiddy have you discovered a fix or workaround? Has the conversation moved elsewhere?

[jakob]

The conversation hasn't moved elsewhere. As I stated above, I tried to get PostgreSQL to compile with the version of readline that ships with OS X, but I couldn't get it to work. If someone finds out how to get it to work (perhaps someone on the PostgreSQL mailing lists might be able to help), I'll integrate this in Postgres.app, but right now I unfortunately don't have the time to handle this task myself.

[danp]

I use postgresql from homebrew now.

[fab1an]

Could you compile postgresql using homebrew?

[jakob]

Maybe. But then we'd always lag behind homebrew.

(Note: there's nothing stopping you from using the Postgres server from Postgres.app and psql from homebrew.)

Also, I think I found out why PostgreSQL won't compile with the OS X provided readline: /usr/lib/libreadline.dylib is just a symlink to libedit.3.dylib

[fab1an]

Libedit actually supports Ctrl+R.
Quickfix: open ~/.editrc and add:

bind "^R" em-inc-search-prev

[tsibley]

This is still pretty annoying, especially since @fab1an's workaround above doesn't actually work on my machine. :-/

Can Postgres.app not just statically link to readline at build time instead of using OS X's editline at runtime?

[fab1an]

Did you copy that exact line? And restarted the terminal?

[tsibley]

@fab1an I did copy the exact line. It changed the behaviour of ^R yesterday, but it wasn't actually completing correctly (stuff I could see in history using up/down wasn't visible to completion). In a newly launched iTerm, it now works as expected. Thanks for the prod to try it again!

[tsibley]

While further customizing .editrc, it took me a few minutes to realize that some lines weren't working because the file does not appear to support comment lines starting with # (this is unlike .inputrc). Parsing of the file is silently aborted when an unrecognized line is seen.

[fab1an]

Nice. If your further customization has to do with psql, can you share it?

Sent from my iPhone

On 30 Jun 2015, at 19:04, Thomas Sibley notifications@github.com wrote:

While further customizing .editrc, it took me a few minutes to realize that some lines weren't working because the file does not appear to support comment lines starting with # (this is unlike .inputrc). Parsing of the file is silently aborted when an unrecognized line is seen.

—
Reply to this email directly or view it on GitHub.

[tsibley]

Just some navigation basics that my fingers still use:

bind "^R" em-inc-search-prev
bind "\e[1~" ed-move-to-beg
bind "\e[4~" ed-move-to-end
bind "\e[3~" ed-delete-next-char
bind "\e[2~" ed-quoted-insert
bind "\e[1;5C" em-next-word
bind "\e[1;5D" ed-prev-word
bind "\e[5C" em-next-word
bind "\e[5D" ed-prev-word
bind "\e\e[C" em-next-word
bind "\e\e[D" ed-prev-word

That's

• history completion with Ctrl-R
• Home
• End
• Delete
• Insert
• Ctrl-Right (× 3 different escape codes depending on term)
• Ctrl-Left (× 3 different escape codes depending on term)

[wiseman]

Any progress or conclusion to this issue? I'm another person who had previously used homebrew's psql and switched to Postgres.App hoping it would make things easier, but found that its psql cleared my 100 KB .psql_history and no longer supported useful features like ctrl-R. Is it still the case that the goal is to switch to libreadline, but there's a potential licensing issue?

[jakob]

@wiseman I don't think there's a licensing issue. I assume that Apple doesn't ship libreadline because it is GPLv3, but that shouldn't be an issue for us.

Linking with libreadline would require us to build libreadline ourselves and include it with Postgres.app. It would make Postgres.app a bit bigger, but I'd assume that libreadline is small, so that hopefully wouldn't be an issue. However, building Postgres.app already takes an hour, so I'm not particularly thrilled about adding more stuff.

But as far as I understand, the issue is just that libedit has different keyboard shortcuts than libreadline? We could fix this by documenting this on the page about command line tools.

Or are there other features that libedit does not support?

In general, I prefer linking with Apple provided libraries if possible -- but if someone can make a sound argument why we should include libreadline, I'm happy to reconsider.

[tsibley]

@jakob Two of the obvious problems with libedit include:

• Standard history/line navigation keys don't work out of the box, requiring arcane configuration. Documenting this may help a little, but doesn't really fit into the "It just works" goal of the rest of Postgres.app.

• History entries aren't always recalled correctly by libedit, especially long queries composed with psql's \e. This commonly and frustratingly leaves me with truncated queries on a daily basis.

It seems like the arguments for not using libreadline are only predicated on deference to Apple? The build time of libreadline seems a) likely to be insignificant relative to building Pg and b) not something that effects people daily.

[wiseman]

One pretty annoying issue was that PostgresApp's libedit version of psql silently overwrote my existing libreadline-compatible .psql_history. A silent bug causing data loss is unfortunate.

[Splarv]

Eh, issue from 2013 still alive. I am not against the libedit, it must just works with multirows queries. Simple example:
/Applications/Postgres.app/Contents/Versions/latest/bin/psql
Type 2 rows (2 rows of text) query, launch. Return to it (for instance by arrow up) and try to delete by backspace. When the cursor come to the first row it will appear at a wrong position.
With /usr/local/bin/psql (from home-brew) all fine.
May be psql works somehow wrongly with libedit?

Workaround is to install simultaneously postgresql.app and posgresql from home-brew. And install postgresql server from GUI, but psql from terminal (in this case launched psql from home-brew in my case).

[mattdeluco]

I believe macOS (based on BSD-derived Darwin) ships with libedit because that's the BSD-licensed equivalent of libreadline (a GNU library.)

Respectively, the libraries use ~/.editrc and ~/.inputrc for configuration. I ended up here trying to figure out how to enable vi keymaps. It seems on macOS, ~/.editrc must contain bind -v rather than various set commands used in the readline equivalent.

Right now I have both a function history I can access with the up/down arrows that came at no cost or configuration, and vi-mode for line editing etc. (including history search.)

I only recently came to appreciate @tsibley's point on long queries being truncated, very frustrating. A brief search turns up a libedit header with #define EL_BUFSIZ 1024 defining the maximum line length. That can apparently be changed as desired, perhaps Postgres.app could bump it up a few multiples before building.

I believe this issue can be closed.

PostgreSQL mailing list archive, for reference: "Re: psql + libedit command history truncation (was: psql history vs. dearmor (pgcrypto))"

[naomiquinones]

Hello @jakob,

maybe it's possible to use readline instead of libedit by setting:
export LDFLAGS="-L/usr/local/opt/readline/lib -L/usr/local/opt/openssl/lib"
and
export CPPFLAGS="-I/usr/local/opt/readline/include -I/usr/local/opt/openssl/include"

Here are some similar issues that used these two commands to connect to readline:
psql_history escape sequences
postgresql 9.4.5 El Capitan bottle breaks ~/.psql_history

The problem that led me here is that when I'm using PostgresApp, the history gets messed up and ends up like this: select\040*\^Afrom\040messages;

I believe this causes the terminal prompt to wrap onto the same line, and when I browse the history with the up and down arrow keys, gaps appear and some text does not appear while other text does not get deleted.

When I use the brew installed postgresql, its history files look like this:
select * from sightings;
and browsing history with the up/down keys causes no strange wrapping/display issues.

[richardkmichael]

@jakob

I'd like to make a case for switching to GNU Readline.

I spent today investigating the various issues such as linking (I was hoping to rather rudely use install_name_tool to rename libedit and shoehorn in libreadline), but also licensing (GPL w BSD and OpenSSL in psql), etc. I can write it up, if you're still open to it. It's largely related to providing a less surprising and easier experience -- for those with shell history experience (e.g., bash, until zsh became the default), or those accustomed to postgres on a Linux server, who might be replicating a "production" environment on a mac for development. (I guess the latter is the primary motivation for Postgres.app.)

Otherwise, I can send a PR on the website documentation to mention the overwrite of .psql_history (perhaps in the remove documention) and history search and line editing differences (perhaps in the CLI documentation). I notice most of the install and remove instructions are very nicely written, and evidently for first time users. So, it's not so clear how verbose to be and where to put file-level back up details; the install instructions could become cluttered. That said, I think it would be helpful to more prominently mention the loss-of-history issue, especially for those who come from homebrew (me :).

In the meantime, I've dusted off my old .editrc file, which I had hoped was gone for good quite a few years ago. :) If it helps anyone: (see also up-thread)

#
#
# CAUTION: libedit will stop reading this file at a *blank* line!
#          To insert readability whitespace, minimally prefix it with a '#'-comment.
#
# Make bindings like Emacs.
bind -e
#
# Search history similar to readline-style.
bind "^r" em-inc-search-prev
bind "^s" em-inc-search-next

One unfortunate consideration: at this point, switching to GNU Readline will mean a loss-of-history for those users already using Postgres.app. psql does not appear to be written to compile against both libedit and GNU Readline and switch between them as necessary at runtime.

[richardkmichael]

@naomiquinones

I'm not sure if you're looking at the .psql_history file directly (e.g. less ~/.psql_history). If so, note that it is not intended to be human readable-- it's written in whatever format is used by the line editing library used by psql. With homebrew psql, that's very likely GNU Readline (which has more readable format), while with Postgres.app psql, it's libedit (which has a format containing the \040 escape codes you mentioned). It's a kind of "coincidence" the file is readable with less or cat.

One would need another program to read the file using the API of the line editing library, then print it out nicely. I was just wondering about this myself, and it turns out the psql \s meta-command can do it! :-)

If you want to verify if the history is messed up, or perhaps just print it to a readable format for copy/paste, etc., try: psql, then use the meta-command \s to display the history, or with an optional filename \s my-history.txt to write the history to that file in human-readable format with spaces, etc.. Unfortunately, \s doesn't seem to work with -c on the command line, so one can't do: psql -c '\s my-history.txt', but needs to start psql and then use \s.

[naomiquinones]

Thanks, @richardkmichael! I'll try that!