Hi folks,
While working on tab completion for SELECT I found a few existing
problems with how psql's tab completion queries interact with
transactions.
- If a tab completion query fails, it will abort the user's
transaction. For example, typing, "ALTER PUBLICATION <tab>" when
connected to an older server version that doesn't have a
pg_publication table.
- If the user's transaction is already failed, psql tab completions
that depend on queries won't work.
I made a patch to wrap tab completion queries in SAVEPOINT/ROLLBACK TO
SAVEPOINT. This addresses the first problem, but not the second
(which is less critical IMHO). Unfortunately I published it in the
same thread as SELECT tab completion, which may have conflated the two
pieces of work:
https://www.postgresql.org/message-id/CAMyN-kCr6aUFaE%3Dov%2B_r%3Db4nS_ihtmJ8-MLMoomZGWdNFgMaVw%40mail.gmail.com
In the meantime, another problem has appeared:
BEGIN;
SET TRANS<tab> (User is hoping to avoid typing the
following long command)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query
This happens because tab completion for SET does a query on
pg_settings to get a list of variables to suggest.
I don't believe a savepoint will help here, as one cannot issue SET
TRANSACTION ISOLATION LEVEL after a SAVEPOINT. (The savepoint
mechanism might still be useful for the other cases, though.)
Perhaps it could be fixed by some special case code to suppress the
pg_settings query if and only if:
1. We're in a transaction.
2. No queries have been run yet (psql would need to track this
itself, I think, as libpq doesn't provide it -- am I right?).
3. The user is trying to tab complete on a SET.
If these are true, the code could jump straight to SET TRANSACTION
ISOLATION LEVEL.
Of course, this is a pain for users who don't want to do that but *do*
want to set a variable when beginning a transaction.
Ideas?
Edmund