Thread: psql + autocommit

psql + autocommit

From
John Sidney-Woollett
Date:
With the advent of postgres v8, would it be possible to change the
default behaviour of psql from AUTOCOMMIT=ON to AUTOCOMMIT=OFF ?

Although this might break backward compatibility, it might be acceptable
on the basis that v8 is such a major release.

Also adding a new command line parameter to control the AUTOCOMMIT
setting for those users that will experience broken scripts executed
(especially using the -c command) might help ease the pain, since they
would only have to add a new switch to their existing scripts, or
explictly set the AUTOCOMMIT variable in their scripts. Otherwise they
could add a final COMMIT at the end of the script.

In Oracle's SQLPlus, AUTOCOMMIT=OFF is the default behaviour and is (in
my view) preferable to the current situation.

I know the AUTOCOMMIT can be set in an active session, but I sometimes
forget leading to an un-rollback-able data loss/damage. Using the
.psqlrc file can lead to inconsistancies between different accounts
where some have the setting defined and others don't.

The final reason for doing so would be to closer to the SQL spec.

John Sidney-Woollett

Re: psql + autocommit

From
Peter Eisentraut
Date:
John Sidney-Woollett wrote:
> With the advent of postgres v8, would it be possible to change the
> default behaviour of psql from AUTOCOMMIT=ON to AUTOCOMMIT=OFF ?

Absolutely not.  This will break every psql use in existence.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: psql + autocommit

From
Tom Lane
Date:
John Sidney-Woollett <johnsw@wardbrook.com> writes:
> With the advent of postgres v8, would it be possible to change the
> default behaviour of psql from AUTOCOMMIT=ON to AUTOCOMMIT=OFF ?

If that's what you want, set it in your ~/.psqlrc.

            regards, tom lane

Re: psql + autocommit

From
John Sidney-Woollett
Date:
No it won't!

It will break any newly compiled version of psql expecting to work to
the old behaviour. It won't affect anyone using an older version of
postgres or psql (ie pre version 8).

To ease any pain, what about a configuration setting for the build
script for postgres (and psql) which changes the default behaviour for
the AUTOCOMMIT setting.

I personally would want to build it with AUTOCOMMIT=OFF is I had the
setting to do so.

This change apart from being more standards compliant would help make
psql "safer" than it currently is.

John Sidney-Woollett

Peter Eisentraut wrote:

> John Sidney-Woollett wrote:
>
>>With the advent of postgres v8, would it be possible to change the
>>default behaviour of psql from AUTOCOMMIT=ON to AUTOCOMMIT=OFF ?
>
>
> Absolutely not.  This will break every psql use in existence.
>

Re: psql + autocommit

From
Peter Eisentraut
Date:
John Sidney-Woollett wrote:
> It will break any newly compiled version of psql expecting to work to
> the old behaviour. It won't affect anyone using an older version of
> postgres or psql (ie pre version 8).

Of course there are no backward compatibility issues when you keep using
the old version.  The problem is that people will use the new psql
expecting it to behave like the old one.  This isn't a small secondary
change; it fundamentally changes the interaction with the program.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: psql + autocommit

From
John Sidney-Woollett
Date:
I agree with you 100% about this - whoever it won't affect new users
starting with v8 (including many new Windows users), and those migrating
from other dbs (like Oracle).

If a config switch was available for the build process that could
preserve the "old" behavior - it wouldn't really pose a problem for
existing users migrating their systems to v8 provided they set the
switch appropriately.

A warning message on psql start might help:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Welcome to psql 8.0, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help on internal slash commands
        \g or terminate with semicolon to execute query
        \q to quit

AUTOCOMMIT is ON/OFF <-- depending on the way it is built

dbname=#
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

This is an opportunity here to make psql more standards compliant and it
can be done in such a way so as not to p*ss off the existing user base,
and break their applications.

John Sidney-Woollett

Peter Eisentraut wrote:

> John Sidney-Woollett wrote:
>
>>It will break any newly compiled version of psql expecting to work to
>>the old behaviour. It won't affect anyone using an older version of
>>postgres or psql (ie pre version 8).
>
>
> Of course there are no backward compatibility issues when you keep using
> the old version.  The problem is that people will use the new psql
> expecting it to behave like the old one.  This isn't a small secondary
> change; it fundamentally changes the interaction with the program.
>

Re: psql + autocommit

From
Peter Eisentraut
Date:
John Sidney-Woollett wrote:
> If a config switch was available for the build process that could
> preserve the "old" behavior - it wouldn't really pose a problem for
> existing users migrating their systems to v8 provided they set the
> switch appropriately.

Such a switch exists: you put \set AUTOCOMMIT in your psql configuration
file.  We don't put feature-altering switches in the build process if
we can help it.  Since most people use prebuilt binaries, such a switch
would be mostly useless at best.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: psql + autocommit

From
John Sidney-Woollett
Date:
I can see this is going nowhere fast! :)

I'd like to see a global setting that I could change, not one on a user
by user basis...

I'd also like a message in the Welcome banner telling me what the
current AUTOCOMMIT setting is...

John Sidney-Woollett

Peter Eisentraut wrote:

> John Sidney-Woollett wrote:
>
>>If a config switch was available for the build process that could
>>preserve the "old" behavior - it wouldn't really pose a problem for
>>existing users migrating their systems to v8 provided they set the
>>switch appropriately.
>
>
> Such a switch exists: you put \set AUTOCOMMIT in your psql configuration
> file.  We don't put feature-altering switches in the build process if
> we can help it.  Since most people use prebuilt binaries, such a switch
> would be mostly useless at best.
>

Re: psql + autocommit

From
Peter Eisentraut
Date:
John Sidney-Woollett wrote:
> I'd like to see a global setting that I could change, not one on a
> user by user basis...

Then I suggest that in addition to the per-user configuration file
~/.psqlrc you implement a global configuration file
/etc/postgresql/psqlrc.  That would be the place you could put such a
setting.

> I'd also like a message in the Welcome banner telling me what the
> current AUTOCOMMIT setting is...

Put

\echo 'AUTOCOMMIT is' :AUTOCOMMIT

in your configuration file and you're done.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: psql + autocommit

From
John Sidney-Woollett
Date:
OK that's exactly what I want - thanks! I wasn't aware of a global
psqlrc file.

BTW, I still think the default behaviour is incorrect...

But at least I can work around it now and have the change be global. :)

Thanks again

John Sidney-Woollett

Peter Eisentraut wrote:

> John Sidney-Woollett wrote:
>
>>I'd like to see a global setting that I could change, not one on a
>>user by user basis...
>
>
> Then I suggest that in addition to the per-user configuration file
> ~/.psqlrc you implement a global configuration file
> /etc/postgresql/psqlrc.  That would be the place you could put such a
> setting.
>
>
>>I'd also like a message in the Welcome banner telling me what the
>>current AUTOCOMMIT setting is...
>
>
> Put
>
> \echo 'AUTOCOMMIT is' :AUTOCOMMIT
>
> in your configuration file and you're done.
>

Re: psql + autocommit

From
daniel
Date:
daniel wrote:
put
\set AUTOCOMMIT 'off'
in your configuration file and you're done.
daniel

> John Sidney-Woollett wrote:
>
>> OK that's exactly what I want - thanks! I wasn't aware of a global
>> psqlrc file.
>>
>> BTW, I still think the default behaviour is incorrect...
>>
>> But at least I can work around it now and have the change be global. :)
>>
>> Thanks again
>>
>> John Sidney-Woollett
>>
>> Peter Eisentraut wrote:
>>
>>> John Sidney-Woollett wrote:
>>>
>>>> I'd like to see a global setting that I could change, not one on a
>>>> user by user basis...
>>>
>>>
>>>
>>>
>>> Then I suggest that in addition to the per-user configuration file
>>> ~/.psqlrc you implement a global configuration file
>>> /etc/postgresql/psqlrc.  That would be the place you could put such
>>> a setting.
>>>
>>>
>>>> I'd also like a message in the Welcome banner telling me what the
>>>> current AUTOCOMMIT setting is...
>>>
>>>
>>>
>>>
>>> Put
>>>
>>> \echo 'AUTOCOMMIT is' :AUTOCOMMIT
>>>
>>> in your configuration file and you're done.
>>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
>


Re: psql + autocommit

From
John Sidney-Woollett
Date:
Does psql v7.4 support a global psqlrc file? I tried adding it to
/etc/postgresql/psqlrc and /etc/psqlrc but in both cases it was ignored.

Adding it to $HOME/.psqlrc worked fine, but means that I need to install
it for each user.

Any ideas why the global file doesn't work?

John Sidney-Woollett

>> Then I suggest that in addition to the per-user configuration file
>> ~/.psqlrc you implement a global configuration file
>> /etc/postgresql/psqlrc.  That would be the place you could put such a
>> setting.
>>
>>
>>> I'd also like a message in the Welcome banner telling me what the
>>> current AUTOCOMMIT setting is...
>>
>>
>>
>> Put
>>
>> \echo 'AUTOCOMMIT is' :AUTOCOMMIT
>>
>> in your configuration file and you're done.
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: psql + autocommit

From
Alvaro Herrera
Date:
On Wed, Sep 15, 2004 at 10:32:20PM +0100, John Sidney-Woollett wrote:
> Does psql v7.4 support a global psqlrc file? I tried adding it to
> /etc/postgresql/psqlrc and /etc/psqlrc but in both cases it was ignored.

Try with $PGDATA/etc/psqlrc

Oh, wait, apparently that was added on 8.0.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Use it up, wear it out, make it do, or do without"


Re: psql + autocommit

From
William Yu
Date:
A script to do this should be trivial. This is just off the top of my head.

#!/usr/bin/perl
open(USER, "/etc/passwd");
while(<USER>) {
   @tmp = split(/:/, $_);
   $home_dir = $tmp[5];
   if ($home_dir) {
     open(PGCONF, "> $home_dir/.psqlrc");
     print PGCONF "\\set AUTOCOMMIT 'off'\n";
     close(PGCONF);
   }
}
close(USER);




John Sidney-Woollett wrote:

> Adding it to $HOME/.psqlrc worked fine, but means that I need to install
> it for each user.

Re: psql + autocommit

From
Peter Eisentraut
Date:
John Sidney-Woollett wrote:
> Any ideas why the global file doesn't work?

There is no support for a global configuration file at this time.  I
suggested that you *implement* it.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: psql + autocommit

From
Bruce Momjian
Date:
Peter Eisentraut wrote:
> John Sidney-Woollett wrote:
> > Any ideas why the global file doesn't work?
>
> There is no support for a global configuration file at this time.  I
> suggested that you *implement* it.

8.0 has a global psqlrc.

--
  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

Re: psql + autocommit

From
tmp
Date:
> Does psql v7.4 support a global psqlrc file? I tried adding it to
> /etc/postgresql/psqlrc and /etc/psqlrc but in both cases it was ignored.

I'm really interested in this global setting to. I cannot find any
documentation on where to place this config file either.


Re: psql + autocommit

From
tmp
Date:
> To ease any pain, what about a configuration setting for the build
> script for postgres (and psql) which changes the default behaviour for
> the AUTOCOMMIT setting.

I really agree on the need for a posibility to set autocommit = off
*regardless* of the client:
On a system using both psql, php and perl on a large bunch of clients it
is a pain - real pain - to set this behavior up individually.

It could be really nice if I had a choise of ensuring that any default
behavior was the more "secure" autommit = off. So please bring back this
setting in postgresql.conf. Please.


Re: psql + autocommit

From
Lars Haugseth
Date:
* Peter Eisentraut:
| John Sidney-Woollett wrote:
| > Any ideas why the global file doesn't work?
|
| There is no support for a global configuration file at this time.  I
| suggested that you *implement* it.

Version 8.0.0beta2 supports a global configuration file. It's should be
located in '~postgres/etc/pgsql'.

--
Lars Haugseth

Re: psql + autocommit

From
John Sidney-Woollett
Date:
Thanks - I'll live with modifying the ~/.psqlrc file until we move to
version 8.

John Sidney-Woollett

Lars Haugseth wrote:

> * Peter Eisentraut:
> | John Sidney-Woollett wrote:
> | > Any ideas why the global file doesn't work?
> |
> | There is no support for a global configuration file at this time.  I
> | suggested that you *implement* it.
>
> Version 8.0.0beta2 supports a global configuration file. It's should be
> located in '~postgres/etc/pgsql'.
>

Re: psql + autocommit

From
John Sidney-Woollett
Date:
Thanks to you and Bruce for the info.

I'll live with modifying the ~/.psqlrc file until we move to version 8.

John Sidney-Woollett

Lars Haugseth wrote:
> * Peter Eisentraut:
> | John Sidney-Woollett wrote:
> | > Any ideas why the global file doesn't work?
> |
> | There is no support for a global configuration file at this time.  I
> | suggested that you *implement* it.
>
> Version 8.0.0beta2 supports a global configuration file. It's should be
> located in '~postgres/etc/pgsql'.
>

Re: psql + autocommit

From
Peter Eisentraut
Date:
Lars Haugseth wrote:
> Version 8.0.0beta2 supports a global configuration file. It's should
> be located in '~postgres/etc/pgsql'.

That would be pretty useless, since normal users often don't have read
access to another user's home directory.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: psql + autocommit

From
"Michael Paesold"
Date:
Peter Eisentraut wrote:

> Lars Haugseth wrote:
> > Version 8.0.0beta2 supports a global configuration file. It's should
> > be located in '~postgres/etc/pgsql'.
>
> That would be pretty useless, since normal users often don't have read
> access to another user's home directory.

Further up in this thread I read $PGDATA/etc/psqlrc, which seems like
nonsense, too. First, $PGDATA is not known at compile time, second no user
should have access to that directory.

Perhaps it should be (and really is) $PREFIX/etc/psqlrc?
(where $PREFIX is of course the installation prefix set during configure)

I am not at work, so I can have a look at the code or test it myself right
now.

Best Regards,
Michael Paesold

--
Superg�nstige DSL-Tarife + WLAN-Router f�r 0,- EUR*
Jetzt zu GMX wechseln und sparen http://www.gmx.net/de/go/dsl


Re: psql + autocommit

From
Lars Haugseth
Date:
* Michael Paesold:
| Peter Eisentraut wrote:
| > Lars Haugseth wrote:
| > > Version 8.0.0beta2 supports a global configuration file. It's should
| > > be located in '~postgres/etc/pgsql'.
| >
| > That would be pretty useless, since normal users often don't have read
| > access to another user's home directory.
|
| Further up in this thread I read $PGDATA/etc/psqlrc, which seems like
| nonsense, too. First, $PGDATA is not known at compile time, second no user
| should have access to that directory.
|
| Perhaps it should be (and really is) $PREFIX/etc/psqlrc?
| (where $PREFIX is of course the installation prefix set during configure)
|
| I am not at work, so I can have a look at the code or test it myself right
| now.

There's a file $PREFIX/share/psqlrc.sample that reads:

--
--      psql configuration file
--
--  This file is read before the .psqlrc file in the user's home directory.
--
--  Copy this to your sysconf directory (typically /usr/local/pgsql/etc) and
--  rename it psqlrc.

So $PREFIX/etc/psqlrc is the right place. (In my setup, ~postgres equals
$PREFIX).

This directory is not created by "gmake install", though. Perhaps it should
be?

--
Lars Haugseth
Tinde ASA
mob 92087323

Re: psql + autocommit

From
Bruce Momjian
Date:
Lars Haugseth wrote:
> * Michael Paesold:
> | Peter Eisentraut wrote:
> | > Lars Haugseth wrote:
> | > > Version 8.0.0beta2 supports a global configuration file. It's should
> | > > be located in '~postgres/etc/pgsql'.
> | >
> | > That would be pretty useless, since normal users often don't have read
> | > access to another user's home directory.
> |
> | Further up in this thread I read $PGDATA/etc/psqlrc, which seems like
> | nonsense, too. First, $PGDATA is not known at compile time, second no user
> | should have access to that directory.
> |
> | Perhaps it should be (and really is) $PREFIX/etc/psqlrc?
> | (where $PREFIX is of course the installation prefix set during configure)
> |
> | I am not at work, so I can have a look at the code or test it myself right
> | now.
>
> There's a file $PREFIX/share/psqlrc.sample that reads:
>
> --
> --      psql configuration file
> --
> --  This file is read before the .psqlrc file in the user's home directory.
> --
> --  Copy this to your sysconf directory (typically /usr/local/pgsql/etc) and
> --  rename it psqlrc.
>
> So $PREFIX/etc/psqlrc is the right place. (In my setup, ~postgres equals
> $PREFIX).
>
> This directory is not created by "gmake install", though. Perhaps it should
> be?

The directory is not created because we don't know if they will be using
this feature.

--
  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

Re: psql + autocommit

From
John Sidney-Woollett
Date:
Why not create this file during the "make install", and explicitly set
the current/standard default options (for psql) in the
$PREFIX/share/psqlrc file?

If the file is deleted or not found then the current/standard default
options would still apply - but atleast the default are there for all to
see in the config file.

Presumably any user wanting to use their own settings with a ~/.psqlrc
file would have these setting override the settings in the global file.

John Sidney-Woollett

Bruce Momjian wrote:

> Lars Haugseth wrote:
>
>>* Michael Paesold:
>>| Peter Eisentraut wrote:
>>| > Lars Haugseth wrote:
>>| > > Version 8.0.0beta2 supports a global configuration file. It's should
>>| > > be located in '~postgres/etc/pgsql'.
>>| >
>>| > That would be pretty useless, since normal users often don't have read
>>| > access to another user's home directory.
>>|
>>| Further up in this thread I read $PGDATA/etc/psqlrc, which seems like
>>| nonsense, too. First, $PGDATA is not known at compile time, second no user
>>| should have access to that directory.
>>|
>>| Perhaps it should be (and really is) $PREFIX/etc/psqlrc?
>>| (where $PREFIX is of course the installation prefix set during configure)
>>|
>>| I am not at work, so I can have a look at the code or test it myself right
>>| now.
>>
>>There's a file $PREFIX/share/psqlrc.sample that reads:
>>
>>--
>>--      psql configuration file
>>--
>>--  This file is read before the .psqlrc file in the user's home directory.
>>--
>>--  Copy this to your sysconf directory (typically /usr/local/pgsql/etc) and
>>--  rename it psqlrc.
>>
>>So $PREFIX/etc/psqlrc is the right place. (In my setup, ~postgres equals
>>$PREFIX).
>>
>>This directory is not created by "gmake install", though. Perhaps it should
>>be?
>
>
> The directory is not created because we don't know if they will be using
> this feature.
>

Re: psql + autocommit

From
Peter Eisentraut
Date:
John Sidney-Woollett wrote:
> Why not create this file during the "make install", and explicitly
> set the current/standard default options (for psql) in the
> $PREFIX/share/psqlrc file?

Because it would take up space, computing power, and attention without
achieving anything.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: psql + autocommit

From
John Sidney-Woollett
Date:
Except transparency...

John Sidney-Woollett

Peter Eisentraut wrote:

> John Sidney-Woollett wrote:
>
>>Why not create this file during the "make install", and explicitly
>>set the current/standard default options (for psql) in the
>>$PREFIX/share/psqlrc file?
>
>
> Because it would take up space, computing power, and attention without
> achieving anything.
>

Re: psql + autocommit

From
Bruce Momjian
Date:
Peter Eisentraut wrote:
> John Sidney-Woollett wrote:
> > Why not create this file during the "make install", and explicitly
> > set the current/standard default options (for psql) in the
> > $PREFIX/share/psqlrc file?
>
> Because it would take up space, computing power, and attention without
> achieving anything.

You could saw the same about installing postgresql.conf in /data.  It is
not required and doesn't do anything.  It just makes changes easier.

--
  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

Re: psql + autocommit

From
Peter Eisentraut
Date:
Bruce Momjian wrote:
> You could saw the same about installing postgresql.conf in /data.  It
> is not required and doesn't do anything.  It just makes changes
> easier.

I used to say that, but I think now it is sort of required because of
the various settings inserted by initdb.

But one thing is a server program reading a configuration file once when
it starts.  Another things is a client program reading a configuration
file every time it starts.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: psql + autocommit

From
Bruce Momjian
Date:
Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > You could saw the same about installing postgresql.conf in /data.  It
> > is not required and doesn't do anything.  It just makes changes
> > easier.
>
> I used to say that, but I think now it is sort of required because of
> the various settings inserted by initdb.
>
> But one thing is a server program reading a configuration file once when
> it starts.  Another things is a client program reading a configuration
> file every time it starts.

Very true.  There is overhead in reading that file each time psql
starts.  Let's leave it alone for now and see if people get confused or
not.

--
  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

Re: psql + autocommit

From
Barry S
Date:
In article <3574.1095259341@sss.pgh.pa.us>, Tom Lane wrote:
> John Sidney-Woollett <johnsw@wardbrook.com> writes:
>> With the advent of postgres v8, would it be possible to change the
>> default behaviour of psql from AUTOCOMMIT=ON to AUTOCOMMIT=OFF ?
>
> If that's what you want, set it in your ~/.psqlrc.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

Re: psql + autocommit

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Here is another way to do it:

1. Rename your system psql to something else:

mv psql real_psql

2. Make a new "psql" that is really an excutable script:

real_psql --set AUTOCOMMIT=OFF $@

Now anyone calling "psql" gets autocommit off, no matter who
they are.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200409202307

-----BEGIN PGP SIGNATURE-----

iD8DBQFBT5sevJuQZxSWSsgRAkZvAKCU16jcZfy0TLPHiKUpUYehPTBfLgCdH44t
McArsrxV+NaP8h8Q1vMLpng=
=ZO2G
-----END PGP SIGNATURE-----



Re: psql + autocommit

From
"Michael Paesold"
Date:
Greg Sabino Mullane wrote:
> real_psql --set AUTOCOMMIT=OFF $@

I have stumbled over this myself: psql is case-sensitive here at the moment,
so it must be --set AUTOCOMMIT=off.

Best Regards,
Michael