Postgres config file: autocommit = off

"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, Pennsylvania 19073

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, Pennsylvania 19073

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, Pennsylvania 19073

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

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, Pennsylvania 19073

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, Pennsylvania 19073

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, Pennsylvania 19073

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)