Thread: 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@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
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
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
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
-----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