Обсуждение: psql CLI: warn about ongoing transaction on exit

Hello PostgreSQL developers,

we recently got a wishlist Debian bug [1] that asks for the psql
command line tool to "warn about an ongoing transaction on exit:

users=> begin;
BEGIN
[...]
users=> \q
$ ...

It would be really nice if psql prompted me whether I wanted to do
this. As it stands, it just rolls back the transaction."

Does this stand a chance to get implemented/accepted? I'm happy to
look into this and craft a patch if you would adopt it upstream.

Thanks,

Martin

[1] http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=439943

--
Martin Pitt        http://www.piware.de
Ubuntu Developer   http://www.ubuntu.com
Debian Developer   http://www.debian.org

On Sun, 2007-09-16 at 20:34 +0200, Martin Pitt wrote:
> users=> begin;
> BEGIN
> [...]
> users=> \q
> $ ...
>
> It would be really nice if psql prompted me whether I wanted to do
> this. As it stands, it just rolls back the transaction."

At a minimum, I think we could make the fact that the transaction
has been rolled back more obvious (e.g. echo a ROLLBACK command tag
to the output or a similar warning message). Asking for confirmation
might be too chatty, and it's certainly not possible in
non-interactive mode.

-Neil

Neil Conway <neilc@samurai.com> writes:
> At a minimum, I think we could make the fact that the transaction
> has been rolled back more obvious (e.g. echo a ROLLBACK command tag
> to the output or a similar warning message). Asking for confirmation
> might be too chatty, and it's certainly not possible in
> non-interactive mode.

+1 for some kind of notification that work was rolled back.  I concur
that demanding confirmation could have unpleasant consequences.

            regards, tom lane

Hi,

Neil Conway [2007-09-17 10:16 -0700]:
> At a minimum, I think we could make the fact that the transaction
> has been rolled back more obvious (e.g. echo a ROLLBACK command tag
> to the output or a similar warning message). Asking for confirmation
> might be too chatty, and it's certainly not possible in
> non-interactive mode.

Unless you do isatty(STDIN_FILENO). Nothing should change for
noninteractive mode. However, I agree that it might be annoying.

If you just output a rollback command on exit, then it is already too
late to rescue the pending transaction, so I'm not sure whether that
would help this use case so much.

What do you think about displaying a different prompt when a
transaction is pending, like showing the number of open transactions
in parentheses or so? '(2 transactions pending) mydb$' is way too
long, and '(T2) mydb$' is not intuitive. Hmm..

Thanks,

Martin
--
Martin Pitt        http://www.piware.de
Ubuntu Developer   http://www.ubuntu.com
Debian Developer   http://www.debian.org

Martin Pitt said:
> If you just output a rollback command on exit, then it is already too
> late to rescue the pending transaction, so I'm not sure whether that
> would help this use case so much.

Well, the DBA can always replay the transaction manually -- I think
notifying the DBA that their modifications have been discarded
is actually more important. Presumably this isn't a particularly
common case...

> What do you think about displaying a different prompt when a
> transaction is pending, like showing the number of open transactions
> in parentheses or so?

Well, there can be at most one active transaction associated with
a given database session. In any case, this feature already exists:
see the %x escape sequence for the PROMPT[1-3] psql variables.

-Neil

Martin Pitt <martin@piware.de> writes:
> What do you think about displaying a different prompt when a
> transaction is pending,

That's been possible for a long time, see the prompt-string variable
in psql.

            regards, tom lane