Re: Automatic transactions in psql

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The attached patch actually does two related things. First,
it keeps track of whether or not you are in a trnasaction
and modifies the prompt slightly when you are by putting
an asterick at the very front of it.

Secondly, it adds a "begin transaction" option that, when
enabled, ensures that you are always inside a transaction
while in psql, so you can always rollback. It does this
by issuing a BEGIN at the appropriate times. This patch
(if ever accepted) conflicts a bit with LO_RTANSACTION:
psql now *does* have a way to know if it is in a
transaction or not, so that part may need to get rewritten.

Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200202061602

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iQA/AwUBPGGZ37ybkGcUlkrIEQJhJQCgr2TEKcvPakEIC8Exn09pInLLOywAoL4I
uGv3TL6hUm/O1oSPrDVdmdc4
=rmRt
-----END PGP SIGNATURE-----

Greg Sabino Mullane writes:

> The attached patch actually does two related things. First,
> it keeps track of whether or not you are in a trnasaction
> and modifies the prompt slightly when you are by putting
> an asterick at the very front of it.

This is an interesting idea, although you may want to give the user the
option to customize his prompt. Add an escape, maybe %* or %t, with the
meaning "resolves to * if in a transaction block and to the empty string
if not". (The existing escapes were all stolen from tcsh, so look there
if you need an idea.)

> Secondly, it adds a "begin transaction" option that, when
> enabled, ensures that you are always inside a transaction
> while in psql, so you can always rollback.

This should be done in the backend.

--
Peter Eisentraut peter_e(at)gmx(dot)net

> Secondly, it adds a "begin transaction" option that, when
> enabled, ensures that you are always inside a transaction
> while in psql, so you can always rollback. It does this
> by issuing a BEGIN at the appropriate times. This patch
> (if ever accepted) conflicts a bit with LO_RTANSACTION:
> psql now *does* have a way to know if it is in a
> transaction or not, so that part may need to get rewritten.

Sweeeet. I've gone mad trying to get people with access to our production
databases to do _everything_ within a transaction when they start fiddling
around!

Chris

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> This is an interesting idea, although you may want to give the user the
> option to customize his prompt.

Seems cool. I am a bit worried about whether the transaction-block
detection mechanism is reliable, though. We might need to add something
to the FE/BE protocol to make this work correctly.

>> Secondly, it adds a "begin transaction" option that, when
>> enabled, ensures that you are always inside a transaction
>> while in psql, so you can always rollback.

> This should be done in the backend.

Agreed. If I recall recent discussions correctly, the spec says that
certain SQL commands should open a transaction and others should not.
It's not reasonable to have that logic in psql rather than the backend.

regards, tom lane

Thread added.

This has been saved for the 7.3 release:

http://candle.pha.pa.us/cgi-bin/pgpatches2

---------------------------------------------------------------------------

Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> The attached patch actually does two related things. First,
> it keeps track of whether or not you are in a trnasaction
> and modifies the prompt slightly when you are by putting
> an asterick at the very front of it.
>
> Secondly, it adds a "begin transaction" option that, when
> enabled, ensures that you are always inside a transaction
> while in psql, so you can always rollback. It does this
> by issuing a BEGIN at the appropriate times. This patch
> (if ever accepted) conflicts a bit with LO_RTANSACTION:
> psql now *does* have a way to know if it is in a
> transaction or not, so that part may need to get rewritten.
>
> Greg Sabino Mullane greg(at)turnstep(dot)com
> PGP Key: 0x14964AC8 200202061602
>
> -----BEGIN PGP SIGNATURE-----
> Comment: http://www.turnstep.com/pgp.html
>
> iQA/AwUBPGGZ37ybkGcUlkrIEQJhJQCgr2TEKcvPakEIC8Exn09pInLLOywAoL4I
> uGv3TL6hUm/O1oSPrDVdmdc4
> =rmRt
> -----END PGP SIGNATURE-----
>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

...
> > Secondly, it adds a "begin transaction" option that, when
> > enabled, ensures that you are always inside a transaction
> > while in psql, so you can always rollback. It does this
> > by issuing a BEGIN at the appropriate times. This patch
> > (if ever accepted) conflicts a bit with LO_RTANSACTION:
> > psql now *does* have a way to know if it is in a
> > transaction or not, so that part may need to get rewritten.

This part of the feature (corresponding to the Ingres "autocommit = off"
feature) should be implemented in the backend rather than in psql. I've
had a moderate interest in doing this but haven't gotten to it; if
someone wants to pick it up I'm sure it would be well received...

- Thomas

Thomas Lockhart wrote:
> ...
> > > Secondly, it adds a "begin transaction" option that, when
> > > enabled, ensures that you are always inside a transaction
> > > while in psql, so you can always rollback. It does this
> > > by issuing a BEGIN at the appropriate times. This patch
> > > (if ever accepted) conflicts a bit with LO_RTANSACTION:
> > > psql now *does* have a way to know if it is in a
> > > transaction or not, so that part may need to get rewritten.
>
> This part of the feature (corresponding to the Ingres "autocommit = off"
> feature) should be implemented in the backend rather than in psql. I've
> had a moderate interest in doing this but haven't gotten to it; if
> someone wants to pick it up I'm sure it would be well received...

Agreed. I wondered whether we could use the psql status part of this
patch. We currently cound parens and quotes, and show that in the psql
prompt. Could we do that for transaction status? Considering we
already track the parens/quotes, another level of status on the psql
display seems a bit much, even if we could do it reliably. Comments?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > This is an interesting idea, although you may want to give the user the
> > option to customize his prompt.
>
> Seems cool. I am a bit worried about whether the transaction-block
> detection mechanism is reliable, though. We might need to add something
> to the FE/BE protocol to make this work correctly.

OK, status on this? Seems we can't apply the patch as-is because of
reliability of the status display. Do people wnat a TODO item? I don't
think I want to make an incompatible protocol change for this feature.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> OK, status on this? Seems we can't apply the patch as-is because of
> reliability of the status display. Do people wnat a TODO item? I don't
> think I want to make an incompatible protocol change for this feature.

I believe Fernando Nasser at Red Hat is currently working on backend
changes to do this properly; so I recommend we not apply the psql hack.

The notion of customizing the psql prompt based on
in-an-xact-block-or-not seems cool; but I do not see how to do it
reliably without a protocol change, and it's not worth that.

regards, tom lane