Re: PATCH: psql tab completion for SELECT

On 15 January 2018 at 14:20, Andres Freund <andres(at)anarazel(dot)de> wrote:
> On January 14, 2018 5:12:37 PM PST, Edmund Horner <ejrh00(at)gmail(dot)com> wrote:
>>And here's a patch to add savepoint protection for tab completion.
>
> It'd be good to explain what that means, so people don't have to read the patch to be able to discuss whether this is a good idea.

Good idea.

In psql if you have readline support and press TAB, psql will often
run a DB query to get a list of possible completions to type on your
current command line.

It uses the current DB connection for this, which means that if the
tab completion query fails (e.g. because psql is querying catalog
objects that doesn't exist in your server), the current transaction
(if any) fails. An example of this happening is:

$ psql -h old_database_server
psql (10.1, server 9.2.24)
Type "help" for help.

postgres=# begin;
BEGIN
postgres=# create table foo (id int);
CREATE TABLE
postgres=# alter subscription <TAB>

(no tab completions because the pg_subscription table doesn't
exist on 9.2! User realises their mistake and types a different
command)

postgres=# select * from foo;
ERROR: current transaction is aborted, commands ignored until end
of transaction block

This patch:
- checks that the server supports savepoints (version 8.0 and later)
and that the user is currently idle in a transaction
- if so, creates a savepoint just before running a tab-completion query
- rolls back to that savepoint just after running the query

The result is that on an 8.0 or later server, the user's transaction
is still ok:

$ psql -h old_database_server
psql (11devel, server 9.2.24)
Type "help" for help.

postgres=# begin;
BEGIN
postgres=# create table foo (id int);
CREATE TABLE
postgres=# alter subscription <TAB>

(again, no tab completions)

postgres=# select * from p;
id
----
(0 rows)

postgres=# commit;
COMMIT

Note that only the automatic tab-completion query is protected; the
user can still fail their transaction by typing an invalid command
like ALTER SUBSCRIPTION ;.