AutoCommit mode in PostgreSQL (7.3 beta1 from CVS 05.09.2002)

snpe wrote:

Hello,
I am try 'autocommit' mode off in postgresql.conf

This is fine, but if I don't call commit and exit from plsql, PostgreSQL do
rollback.Can I change this behavior.

I assume this is 7.3beta. What does the SQL standard say about exit
from a sesion without commit? There is currently no way to change that
behavior.

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

That is 7.3beta.I don't know for standard, but Oracle do auto commit on exit

I think that that is fine if we have choice in postgresql.conf

thanks
haris peco

Bruce Momjian <pgman@candle.pha.pa.us> writes:

snpe wrote:

This is fine, but if I don't call commit and exit from plsql, PostgreSQL do
rollback.Can I change this behavior.

I assume this is 7.3beta. What does the SQL standard say about exit
from a sesion without commit? There is currently no way to change that
behavior.

I don't think there should be. Autocommit on exit is sheer folly: what
if it's an accidental disconnect? (network fails, you type \q when you
meant \w, or whatever.) You might as well have autocommit on as run
an "autocommit off" session that will commit when you did not explicitly
tell it to.

regards, tom lane

I could not find the exact clause that says that in either SQL'92 nor
SQL'99, but C.J.Date says (about SQL'92) says that a DISCONNECT would
"automatically execute either a ROLLBACK or a COMMIT (it is
implementation dependent which)".

I guess a GUC variable can be a good idea, for Oracle compatibility
purposes. I would make our default different from Oracle's though: if a
commit is not received something is wrong, either an user error, some
tool error, etc. It sees safer to ROLLBACK. Isn't that what we do if
a connection is lost due to a communication error anyway? How can
Oracle know that if it got the whole set of commands for the transaction
anyway? Isn't there a more specific situation where it does that (the
automatic COMMIT)?

Anyway, psql can be smarter and ask the user: "There is a transaction in
progress, do you want to commit?", what can be done

--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9

On Monday 09 September 2002 07:18 pm, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

snpe wrote:

This is fine, but if I don't call commit and exit from plsql, PostgreSQL
do rollback.Can I change this behavior.

I assume this is 7.3beta. What does the SQL standard say about exit
from a sesion without commit? There is currently no way to change that
behavior.

I don't think there should be. Autocommit on exit is sheer folly: what
if it's an accidental disconnect? (network fails, you type \q when you
meant \w, or whatever.) You might as well have autocommit on as run
an "autocommit off" session that will commit when you did not explicitly
tell it to.

Oracle work like next :
If program quit correct (with ctrl-d or exit) Oracle do commit.
If program lost (power lost, network problem, kill program etc) Oracle do
rollback.If postgresql do rollback that is fine, but autocommit have another
problem (my another post)

regards
haris peco

Fernando Nasser wrote:

I could not find the exact clause that says that in either SQL'92 nor
SQL'99, but C.J.Date says (about SQL'92) says that a DISCONNECT would
"automatically execute either a ROLLBACK or a COMMIT (it is
implementation dependent which)".

I guess a GUC variable can be a good idea, for Oracle compatibility
purposes. I would make our default different from Oracle's though: if a
commit is not received something is wrong, either an user error, some
tool error, etc. It sees safer to ROLLBACK. Isn't that what we do if
a connection is lost due to a communication error anyway? How can
Oracle know that if it got the whole set of commands for the transaction
anyway? Isn't there a more specific situation where it does that (the
automatic COMMIT)?

Anyway, psql can be smarter and ask the user: "There is a transaction in
progress, do you want to commit?", what can be done

I agree with Tom. If you are in a multi-statement transaction, then if
you exit, you exit. I can't understand the logic that would to a commit
on any type of disconnect.

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

On Tuesday 10 September 2002 03:53 am, Bruce Momjian wrote:

Fernando Nasser wrote:

I could not find the exact clause that says that in either SQL'92 nor
SQL'99, but C.J.Date says (about SQL'92) says that a DISCONNECT would
"automatically execute either a ROLLBACK or a COMMIT (it is
implementation dependent which)".

I guess a GUC variable can be a good idea, for Oracle compatibility
purposes. I would make our default different from Oracle's though: if a
commit is not received something is wrong, either an user error, some
tool error, etc. It sees safer to ROLLBACK. Isn't that what we do if
a connection is lost due to a communication error anyway? How can
Oracle know that if it got the whole set of commands for the transaction
anyway? Isn't there a more specific situation where it does that (the
automatic COMMIT)?

Anyway, psql can be smarter and ask the user: "There is a transaction in
progress, do you want to commit?", what can be done

I agree with Tom. If you are in a multi-statement transaction, then if
you exit, you exit. I can't understand the logic that would to a commit
on any type of disconnect.

It is only logic that I choose what's happen when I exit
Autocommit=false have another problem
regards
Haris Peco