Thread: psql + autocommit
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
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/
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
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. >
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/
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.
>
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/
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. >
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/
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. >
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 > > > >
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
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"
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.
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/
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
> 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.
> 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.
* 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
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'. >
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'. >
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/
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
* 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
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
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. >
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/
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. >
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
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/
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
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 >
-----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-----
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