Обсуждение: AutoCommit mode in PostgreSQL (7.3 beta1 from CVS 05.09.2002)
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. regards Haris Peco
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 On Friday 06 September 2002 04:40 pm, Bruce Momjian wrote: > 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 <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