473,729 Members | 2,092 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
33 14023
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/
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 23 '05 #2
John Sidney-Woollett <jo****@wardbro ok.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

Nov 23 '05 #3
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.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #4
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/
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #5
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.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #6
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/
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #7
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.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #8
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/
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #9
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 ma*******@postg resql.org

Nov 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2668
by: Christian Traber | last post by:
Hi, thanks for the new great version! Only one small problem, how can I disable autocommit in Postgres 7.4 in libpgtcl and psql? I found something about .psqlrc but how is the syntax and how can I set it for libpgtcl? BTW, SET AUTOCOMMIT TO ON still gives no error, only SET AUTOCOMMIT TO OFF
1
4383
by: Carmen Gloria Sepulveda Dedes | last post by:
Hola. He instalado postgres 7.4, y vi que ya no es posible hacer un "alter database ... set autocommit = off" Leyendo la documentacion de 7.4, encontre lo siguiente: Note: The autocommit-on mode is PostgreSQL's traditional behavior, but autocommit-off is closer to the SQL spec. If you prefer autocommit-off, you may wish to set it in your .psqlrc file.
0
1670
by: Carmen Gloria Sepulveda Dedes | last post by:
Hello. I have installed postgres 7.4, and I see that it's not possible to do "alter database ... set autocommit = off" In the documentation, I found: Note: The autocommit-on mode is PostgreSQL's traditional behavior, but autocommit-off is closer to the SQL spec. If you prefer autocommit-off, you may wish to set it in your .psqlrc file.
0
1135
by: Bricklen | last post by:
Hi all, I've scoured the net for a simple way to set AUTOCOMMIT off in psql, for the duration of a session (not simply a transaction). The only real reference that I can find says that "\set autocommit off" will do what I want. It doesn't though. For example, if session A inserts a row, then session B has access to that data immediately (from another terminal). I realize that you can fire off a begin/end pair for every statement, but...
0
8925
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8763
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9428
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9154
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8156
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6722
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6026
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4531
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3240
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.