Thread: Postgres config file: autocommit = off
I'm not sure that this is the right list to ask - but after having googled a while it seems that the only ones that might be able to answer this question is the developers. Therefor, here we go: As far as I have understood, postgres is autocommiting each typed statement UNLESS the user remembers to write "BEGIN" which then disables the autocommit behavior for this single transaction. -Unfortunately it's easy to forget the BEGIN and it might be quite troublesome if one has to do it a lot. Therefor: Are there any plans to give the administrator an OPTION to turn the behavior off through a parameter "autocommit = <boolean>" in the config file? Eventually the default behavior could be the autoccomit = on, as it is now. Eventually an option per database could override the config file setting, so that the default value was to disable the autocommit behavior but a subset of the databases had autocommit enabled. _________________________________________________________________ F� MSN Hotmail p� mobilen http://www.msn.dk/mobile
"Rasmus Resen Amossen" <rresena@hotmail.com> writes:
> Therefor: Are there any plans to give the administrator an OPTION to turn
> the behavior off through a parameter "autocommit = <boolean>" in the config
> file?
We have been there, done that, and decided it was a bad idea. I suggest
you do a little reading in the mail list archives.
regards, tom lane
>We have been there, done that, and decided it was a bad idea. I suggest >you do a little reading in the mail list archives. I have searched the lists archives for the words "commit", "autocommit" and "transaction" but couldn't find any discussion on wheter to give a database administrator the option to turn automatic commit off was is a good idea or not (not requirering the user to enter BEGIN; to start a transaction). Do you know the title of the thread or some more details on where to find the arguments? I look forward to read that discussion. :-) Personally I am managing a database for approx. 500 people which are all VERY dependant on the correctnes of the database. Sometimes, when we do manually fixes in the database, it takes a series of statements before our data mangeling leaves the databases in a consistent state again. Therefor it is a quite serious problem for us if we forget the BEGIN-word (which happens quite often :-( ), so we could really use the config file option mentioned. Regards, Rasmus _________________________________________________________________ Tag MSN Hotmail med dig p� mobilen http://www.msn.dk/mobile
On Sun, 1 Jun 2003, Rasmus Resen Amossen wrote: > >We have been there, done that, and decided it was a bad idea. I suggest > >you do a little reading in the mail list archives. > > I have searched the lists archives for the words "commit", "autocommit" and > "transaction" but couldn't find any discussion on wheter to give a database > administrator the option to turn automatic commit off was is a good idea or > not (not requirering the user to enter BEGIN; to start a transaction). > > Do you know the title of the thread or some more details on where to find > the arguments? I look forward to read that discussion. :-) > > Personally I am managing a database for approx. 500 people which are all > VERY dependant on the correctnes of the database. Sometimes, when we do > manually fixes in the database, it takes a series of statements before our > data mangeling leaves the databases in a consistent state again. Therefor it > is a quite serious problem for us if we forget the BEGIN-word (which happens > quite often :-( ), so we could really use the config file option mentioned. > I can't remember the discussion very clearly but I seem to recall that it was some sort of issue with some, but not all, of the interfaces. However, ignoring that you can set autocommit to off by altering the user. For example if the user you do the fixing as as described above is called dba_1 then if you do: alter user dba_1 set autocommit to off; whenever you log in as the dba_1 user you will find that the autocommit is turned off. -- Nigel J. Andrews
SET autocommit to 'off' is going away in 7.4 so the SET USER command will not work anymore in that release, and I can't think of a workaround. There will be interface-specific settings, I assume, but I am not sure how that would be controlled per-user. --------------------------------------------------------------------------- Nigel J. Andrews wrote: > On Sun, 1 Jun 2003, Rasmus Resen Amossen wrote: > > > >We have been there, done that, and decided it was a bad idea. I suggest > > >you do a little reading in the mail list archives. > > > > I have searched the lists archives for the words "commit", "autocommit" and > > "transaction" but couldn't find any discussion on wheter to give a database > > administrator the option to turn automatic commit off was is a good idea or > > not (not requirering the user to enter BEGIN; to start a transaction). > > > > Do you know the title of the thread or some more details on where to find > > the arguments? I look forward to read that discussion. :-) > > > > Personally I am managing a database for approx. 500 people which are all > > VERY dependant on the correctnes of the database. Sometimes, when we do > > manually fixes in the database, it takes a series of statements before our > > data mangeling leaves the databases in a consistent state again. Therefor it > > is a quite serious problem for us if we forget the BEGIN-word (which happens > > quite often :-( ), so we could really use the config file option mentioned. > > > > I can't remember the discussion very clearly but I seem to recall that it was > some sort of issue with some, but not all, of the interfaces. > > However, ignoring that you can set autocommit to off by altering the user. For > example if the user you do the fixing as as described above is called dba_1 > then if you do: > > alter user dba_1 set autocommit to off; > > whenever you log in as the dba_1 user you will find that the autocommit is > turned off. > > > -- > Nigel J. Andrews > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> SET autocommit to 'off' is going away in 7.4 so the SET USER command
> will not work anymore in that release, and I can't think of a
> workaround. There will be interface-specific settings, I assume, but I
> am not sure how that would be controlled per-user.
As I was just telling some Red Hat cohorts, I intend to provide
autocommit support in psql for 7.4 --- probably controlled by a psql
variable, though I've not gotten round to designing the details yet.
In any case it will be possible to turn autocommit off in your ~/.psqlrc
file if you have the desire to do so. I would also say that the setting
will not apply to psql -c invocations, thus avoiding the worst effects
for scripts ...
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > SET autocommit to 'off' is going away in 7.4 so the SET USER command > > will not work anymore in that release, and I can't think of a > > workaround. There will be interface-specific settings, I assume, but I > > am not sure how that would be controlled per-user. > > As I was just telling some Red Hat cohorts, I intend to provide > autocommit support in psql for 7.4 --- probably controlled by a psql > variable, though I've not gotten round to designing the details yet. > In any case it will be possible to turn autocommit off in your ~/.psqlrc > file if you have the desire to do so. I would also say that the setting > will not apply to psql -c invocations, thus avoiding the worst effects > for scripts ... Youch --- a .psqlrc that doesn't effect psql -c. First we had SET doesn't get rolled back if it is the first command of a transaction, now, with that gone, we have psql -c not reading autocommit settings in .psqlrc. The warts seem to follow autocommit where ever it goes! :-) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > SET autocommit to 'off' is going away in 7.4 so the SET USER command > > will not work anymore in that release, and I can't think of a > > workaround. There will be interface-specific settings, I assume, but I > > am not sure how that would be controlled per-user. > > As I was just telling some Red Hat cohorts, I intend to provide > autocommit support in psql for 7.4 --- probably controlled by a psql > variable, though I've not gotten round to designing the details yet. > In any case it will be possible to turn autocommit off in your ~/.psqlrc > file if you have the desire to do so. I would also say that the setting > will not apply to psql -c invocations, thus avoiding the worst effects > for scripts ... In thinking about it, I don't see how we can ignore .psqlrc if it has autocommit set to off. Imagine I am in psql and I do '\i file', and it works, so I code up psql -c in a script, and it doesn't work --- would be quite strange. I think the only thing we can do is have a psql flag that turns autocommit on and overrides .psqlrc --- maybe that's what you had in mind. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > In thinking about it, I don't see how we can ignore .psqlrc if it has > > autocommit set to off. Imagine I am in psql and I do '\i file', and it > > works, so I code up psql -c in a script, and it doesn't work --- would > > be quite strange. > > What's your point? "psql -c '\i file'" doesn't work either. > > Surely you don't want to have to put back all those SET AUTOCOMMITs again. No. > Yeah, it's a bit warty, but psql -c is inherently different from a psql > script. IMHO it's not reasonable to make (shell) scripts using psql -c How is it different, except for having no prompt? I never assumed it would behave differently. > have to explicitly state the obvious, which is that they'd like their > command committed. They can't do that from in the file anymore (command-line arg?), or will we have backslash commands for autocommit off? (Yikes, a new backslash command, or two?) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Yeah, it's a bit warty, but psql -c is inherently different from a psql
>> script. IMHO it's not reasonable to make (shell) scripts using psql -c
> How is it different, except for having no prompt?
It's different because it's explicitly designed for convenient execution
of a single command. Thus, autocommit off would be useless and
counterproductive. Also, since the -c string is fed to PQexec as a
single query (again different from the psql stdin behavior), if you do
put multiple commands into -c then you get them executed as a single
transaction anyway. So you do not need or want .psqlrc modifying the
behavior in either case.
One of the reasons for taking autocommit control out of the backend and
pushing it up to the client level is exactly to make it feasible to take
these sorts of application-level considerations into account when
choosing the behavior.
regards, tom lane
Oh, yes, sorry, I was confusing psql -c and -f. -c is clearly different. --------------------------------------------------------------------------- Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> Yeah, it's a bit warty, but psql -c is inherently different from a psql > >> script. IMHO it's not reasonable to make (shell) scripts using psql -c > > > How is it different, except for having no prompt? > > It's different because it's explicitly designed for convenient execution > of a single command. Thus, autocommit off would be useless and > counterproductive. Also, since the -c string is fed to PQexec as a > single query (again different from the psql stdin behavior), if you do > put multiple commands into -c then you get them executed as a single > transaction anyway. So you do not need or want .psqlrc modifying the > behavior in either case. > > One of the reasons for taking autocommit control out of the backend and > pushing it up to the client level is exactly to make it feasible to take > these sorts of application-level considerations into account when > choosing the behavior. > > regards, tom lane > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> In thinking about it, I don't see how we can ignore .psqlrc if it has
> autocommit set to off. Imagine I am in psql and I do '\i file', and it
> works, so I code up psql -c in a script, and it doesn't work --- would
> be quite strange.
What's your point? "psql -c '\i file'" doesn't work either.
Surely you don't want to have to put back all those SET AUTOCOMMITs again.
Yeah, it's a bit warty, but psql -c is inherently different from a psql
script. IMHO it's not reasonable to make (shell) scripts using psql -c
have to explicitly state the obvious, which is that they'd like their
command committed.
regards, tom lane
>One of the reasons for taking autocommit control out of the backend and >pushing it up to the client level is exactly to make it feasible to take >these sorts of application-level considerations into account when >choosing the behavior. Ok, I can see some sense in that: Make the autocommit-behavior client dependent instead of system dependent. But that requires that all clients the user uses can handle this (is able to store a default behavior). I aggree, that clients should, as you write, overrule the system default behavior. But I (still) can't find an argument for, why the administrator should not have the oppotunity to set a default behavior for the whole system (not even in the archives). In this way postgres would be able to deal with clients that did not have support for setting the default behavior. Eventually a per user or per database default behavior could be usefull for the same resons. Bennefits: - Project managers can easier force programmers to use a specific database coding style. Fx.: I guess that if the PHP-interface should have a default value it should be given at the connect time. Programmers could easily forget to set "autocommit = off" here, thus allowing them self an eventually unwanted coding style. - Clinents which do not support setting an autocommit default behavior, can be used by setting the wanted behavior for the database system. Drawbacks: - ? (Enlighten me) _________________________________________________________________ Send s�de postkort til s�de mennesker http://www.msn.dk/postkort
I agree with you, but as outvoted by the group --- can someone give the the client-side argument for him. I know it, but my heart isn't in it. :-) --------------------------------------------------------------------------- Rasmus Resen Amossen wrote: > >One of the reasons for taking autocommit control out of the backend and > >pushing it up to the client level is exactly to make it feasible to take > >these sorts of application-level considerations into account when > >choosing the behavior. > > Ok, I can see some sense in that: Make the autocommit-behavior client > dependent instead of system dependent. But that requires that all clients > the user uses can handle this (is able to store a default behavior). > I aggree, that clients should, as you write, overrule the system default > behavior. But I (still) can't find an argument for, why the administrator > should not have the oppotunity to set a default behavior for the whole > system (not even in the archives). In this way postgres would be able to > deal with clients that did not have support for setting the default > behavior. Eventually a per user or per database default behavior could be > usefull for the same resons. > > Bennefits: > - Project managers can easier force programmers to use a specific > database coding style. Fx.: I guess that if the PHP-interface should have a > default value it should be given at the connect time. Programmers could > easily forget to set "autocommit = off" here, thus allowing them self an > eventually unwanted coding style. > - Clinents which do not support setting an autocommit default behavior, > can be used by setting the wanted behavior for the database system. > > Drawbacks: > - ? (Enlighten me) > > _________________________________________________________________ > Send s�de postkort til s�de mennesker http://www.msn.dk/postkort > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Mon, Jun 02, 2003 at 03:14:33PM -0400, Bruce Momjian wrote: > > Oh, yes, sorry, I was confusing psql -c and -f. -c is clearly different. But if -f follows the ~/.psqlrc variable, then you'll definitively will have to put back the "SET AUTOCOMMIT to off" to scripts... (vacuumdb and clusterdb seem to be the only ones left, plus the ones in contrib) -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "La persona que no quería pecar / estaba obligada a sentarse en duras y empinadas sillas / desprovistas, por cierto de blandos atenuantes" (Patricio Vogel)