>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


Sun, 01 Jun 2003 17:50:19 +0200
"Rasmus Resen Amossen" <rresena@hotmail.com>
Re: Postgres config file: autocommit = off
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


Mon, 2 Jun 2003 18:54:21 +0100 (BST)
"Nigel J. Andrews" <nandrews@investsystems.co.uk>
Re: Postgres config file: autocommit = off

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

Mon, 2 Jun 2003 14:12:36 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Postgres config file: autocommit = off
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

Mon, 02 Jun 2003 14:25:43 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Postgres config file: autocommit = off 
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

Mon, 2 Jun 2003 14:29:06 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Postgres config file: autocommit = off
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

Mon, 2 Jun 2003 14:33:54 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Postgres config file: autocommit = off
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

Mon, 02 Jun 2003 14:52:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Postgres config file: autocommit = off 
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

Mon, 2 Jun 2003 14:57:49 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Postgres config file: autocommit = off
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

Mon, 02 Jun 2003 15:11:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Postgres config file: autocommit = off 

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

Mon, 2 Jun 2003 15:14:33 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Postgres config file: autocommit = off