PostgreSQL – Handling Reckless Behavior of psql \e

postgresqlpsql

psql 13.2 executes the query buffer no matter what when exiting the editor, even after quitting it – in which case the buffer may hold the most recently executed SQL query now.

To reproduce:

  1. Start editor (vim in my case) with \e or \e filename from the psql console.
  2. Type something or not, doesn't make a difference.
  3. Cancel edit with :q (or :q! to force it)

Expected behavior: go back to the state before starting the editor. (I cancelled the edit!)
Observed behavior: re-execute the last executed SQL query.

What seems to happen with \e:

  1. it executes (the equivalent of) \p, so the current query buffer contains the previous command when it was empty. (Which is not visible to the user when editing a file!)
    The manual on \p :

    \p or \print

    Print the current query buffer to the standard output. If the current query buffer is empty, the most recently executed query is printed instead.

  2. Start up the editor. If a filename was provided, load the file, else, load the query buffer.

  3. If the editor ends with a write, copy the result to the query buffer.
    If the edit is cancelled (:q in vim), don't overwrite the query buffer. (It holds the previous command now!)

  4. Execute the query buffer. Or, to be precise, do this:

The behavior seems ok for \p (just copy the query buffer, no harm done), but not for \e, which can execute a command I didn't even get to see.

Is it a bug?
Either way, I would at least like to disable auto-execute when leaving the editor. But that doesn't seem to be possible. Or am I missing something?

Best Answer

This has been fixed in v14 as a reaction to this question. The rest of the answer refers to the pre-v14 behavior:

This is (sort of) documented:

If filename is specified, the file is edited; after the editor exits, the file's content is copied into the current query buffer. If no filename is given, the current query buffer is copied to a temporary file which is then edited in the same fashion. Or, if the current query buffer is empty, the most recently executed query is copied to a temporary file and edited in the same fashion.

The new contents of the query buffer are then re-parsed according to the normal rules of psql, treating the whole buffer as a single line.

If you quit the editor without saving, it is the current query buffer or the most recently executed query that are executed.

I agree with you that this is annoying, and you are not the first person I heard complain.

When editing a script, the behavior clearly violates the “principle of least astonishment”:

test=> \! cat q.sql
SELECT 99;

test=> SELECT 42;
 ?column? 
----------
       42
(1 row)

test=> \e q.sql
 ?column? 
----------
       42
(1 row)
Related Question