Thread: uncommited question

Hi.

What happens if I don't do any commits on a connection?

I am opening a connection and doing a few "selects" from the connection.

I am not comiting after each query, but instead useing teh same cursor
for another query.

What then happens if I update records and the commitn from another
connection?  What about if I do some udates and inserts from this
connection and commit them?

Thanks

--

*
*  Rob Brown-Bayliss
*

On Fri, Aug 09, 2002 at 01:58:29PM +1200, Rob Brown-Bayliss wrote:
>
> Hi.
>
> What happens if I don't do any commits on a connection?
>
> I am opening a connection and doing a few "selects" from the connection.

It depends entirely on whether you issued an explicit BEGIN (this
includes "turning autocommit off").  If you did, then certain kinds
of locks will be impossible until you COMMIT.

A
--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

On 9 Aug 2002, Rob Brown-Bayliss wrote:

>
> Hi.
>
> What happens if I don't do any commits on a connection?
>
> I am opening a connection and doing a few "selects" from the connection.
>
> I am not comiting after each query, but instead useing teh same cursor
> for another query.
>
> What then happens if I update records and the commitn from another
> connection?  What about if I do some udates and inserts from this
> connection and commit them?

It looks like you've got an Oracle thinking cap on.  You'll need to trade
that in on a Postgresql thinking cap.

In Oracle, when you connect, a transaction is begun for you.  Then you do
whatever you want, and when you like what you have you commit, or if you
don't like it you rollback.  Then another transaction begins silently for
you.

In Postgresql, there are no transactions running automatically for you
when you connect.  If you issue a SQL command, postgresql will initiate a
transaction, perform the SQL, then commit / rollback the transaction
automatically, based on whether it went through or not.

In Postgresql, if you want a transaction to begin, you have to issue the
begin command yourself.  Then, all the SQL you type will be in a
transaction, and you can either commit or rollback when you are done.

Hopefully that explains the difference well enough.

On Sat, 2002-08-10 at 05:03, scott.marlowe wrote:

>
> Hopefully that explains the difference well enough.

Yeah, I thought postgresql had by default the auto-commit thing?  Is it
a config setup somewhere?


--

*
*  Rob Brown-Bayliss
*

On Sat, 2002-08-10 at 08:11, Rob Brown-Bayliss wrote:

> Yeah, I thought postgresql had by default the auto-commit thing?  Is it
> a config setup somewhere?

For interested parties (from the docs to 7.1.3):

By default, Postgres executes transactions in unchained mode (also known
as "autocommit" in other database systems). In other words, each user
statement is executed in its own transaction and a commit is implicitly
performed at the end of the statement (if execution was successful,
otherwise a rollback is done). BEGIN initiates a user transaction in
chained mode, i.e., all user statements after BEGIN command will be
executed in a single transaction until an explicit COMMIT, ROLLBACK, or
execution abort.

--

*
*  Rob Brown-Bayliss
*

On 10 Aug 2002, Rob Brown-Bayliss wrote:

> On Sat, 2002-08-10 at 05:03, scott.marlowe wrote:
>
> >
> > Hopefully that explains the difference well enough.
>
> Yeah, I thought postgresql had by default the auto-commit thing?  Is it
> a config setup somewhere?

Postgresql is in autocommit mode until you initiate a transaction with a
begin statement.

I.e. when you enter the psql monitor, and type in:

update table set field1='something' where id=45;

Postgresql internally wraps the update in the equivalent of a begin;end;
pair.

There was some talk of making psql run in a kind of auto-transact mode,
where it would throw a begin; when you started it up, and another when you
did a commit or rollback.  I haven't seen any work done on it though.  I
kinda prefer the way postgresql does it, but can understand the
advantages to the way Oracle et. al. do it.

On Fri, 9 Aug 2002, scott.marlowe wrote:
>
> Postgresql is in autocommit mode until you initiate a transaction with a
> begin statement.
>
> I.e. when you enter the psql monitor, and type in:
>
> update table set field1='something' where id=45;
>
> Postgresql internally wraps the update in the equivalent of a begin;end;
> pair.
>
> There was some talk of making psql run in a kind of auto-transact mode,
> where it would throw a begin; when you started it up, and another when you
> did a commit or rollback.  I haven't seen any work done on it though.  I
> kinda prefer the way postgresql does it, but can understand the
> advantages to the way Oracle et. al. do it.


This caught me out when I started with PostgreSQL as well. It also took me a
while to work out from the documentation that autocommit was not some sort of
configuration parameter and was simply a BEGIN statement. If someone can bear
the boredom I would request that the documentation is changed to make this
clear. I might have a quick scan through the directory tree myself this weekend
to see what format the docs are in.


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

On Fri, 9 Aug 2002, Nigel J. Andrews wrote:

> On Fri, 9 Aug 2002, scott.marlowe wrote:
> >
> > Postgresql is in autocommit mode until you initiate a transaction with a
> > begin statement.
> >
> > I.e. when you enter the psql monitor, and type in:
> >
> > update table set field1='something' where id=45;
> >
> > Postgresql internally wraps the update in the equivalent of a begin;end;
> > pair.
> >
> > There was some talk of making psql run in a kind of auto-transact mode,
> > where it would throw a begin; when you started it up, and another when you
> > did a commit or rollback.  I haven't seen any work done on it though.  I
> > kinda prefer the way postgresql does it, but can understand the
> > advantages to the way Oracle et. al. do it.
>
>
> This caught me out when I started with PostgreSQL as well. It also took me a
> while to work out from the documentation that autocommit was not some sort of
> configuration parameter and was simply a BEGIN statement. If someone can bear
> the boredom I would request that the documentation is changed to make this
> clear. I might have a quick scan through the directory tree myself this weekend
> to see what format the docs are in.


Oops, I think I need another egg, I seem to have an as yet unegged spot on my
face.

Now I know when I started out I was confused and I was looking for an
autocommit configuration/settable type item but scanning the directory tree
I'll be blowed if I can find an occurrance of autocommit that doesn't mention
that this requires a BEGIN statement. Obviously I'm ignoring the ecpg, odbc and
jdbc interfaces that do seem to have an autocommit flag.


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants