Automatic transactions in psql

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@gmx.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@gmx.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@turnstep.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@candle.pha.pa.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@candle.pha.pa.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@gmx.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@candle.pha.pa.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@candle.pha.pa.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

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

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

Sounds good to me. If he is not, I'll volunteer if someone will point
me in the right direction.

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.

<devil's advocate>
Surely it's being done at least as reliably as the hack in large_obj.c? :)
</da>
I don't think it's that unreliable myself: as long as the backend outputs
the standard confirmation message (e.g. "BEGIN"), we should always be
able to keep track. And a dropped connection means we switch to "not in a
transaction"

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

iD8DBQE8dnuTvJuQZxSWSsgRAnnFAJ9vz2o1bYiDB1jfnBE2cWohDAw0IwCglMX5
LUgc2CcQAsdueSpFWH+mwxE=
=e0qZ
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg@turnstep.com> writes:

I don't think it's that unreliable myself: as long as the backend outputs
the standard confirmation message (e.g. "BEGIN"), we should always be
able to keep track.

But it wouldn't. At least not in the auto-transaction-start mode.
(We couldn't send "C BEGIN" in addition to a "C" for the command that
caused the transaction start; that'd be one too many "C", and would
at least potentially confuse clients.) Moreover, I don't think psql
can reliably know whether the backend is in auto-start mode or not;
nor should it be responsible for understanding which SQL constructs
can cause an auto transaction start. (Not all do.)

We could define an additional message that the backend would put out at
start and end of a transaction block, but ISTM that is a protocol
change.

<devil's advocate>
Surely it's being done at least as reliably as the hack in large_obj.c? :)
</da>

That crock should be eliminated, not emulated ;-)

Being able to clean up large_obj.c might actually be a sufficient reason
to change the protocol ...

regards, tom lane