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:
Show quoted text
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:
Show quoted text
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:
Show quoted text
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:
Show quoted text
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
Show quoted text
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
Import Notes
Reply to msg id not found: 4148B292.9050805@littlesaigon.homedns.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
Show quoted text
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.
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.
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:
Show quoted text
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
* 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:
Show quoted text
* 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'.
