Re: COMMIT in PostgreSQL

You probably need to execute a "begin;" statement first if you want to start
a transaction.

Sam

On Sat, Jul 20, 2002 at 02:50:34PM +0300,
stefan@extum.com <stefan@extum.com> wrote
a message of 19 lines which said:

I was running a script which does some INSERTS and UPDATE some
table. I found that there is no need for COMMIT; After each
statement the TABLE is immediately commited.

There is probably no BEGIN; in your script. Try:

BEGIN;

INSERT INTO Domains (name, tech_contact, admin_contact, billing_contact, owner)
VALUES ('pasteur.eu', 1, 1, 1, 1);

INSERT INTO Domains (name, tech_contact, admin_contact, billing_contact, owner)
VALUES ('postgresql.eu', 1, 1, 1, 1);

COMMIT;

On Sat, Jul 20, 2002 at 03:03:02PM +0300, stefan@extum.com wrote:

thanks a lot all for comments. So in PostgreSQL each query is a
transaction ?

If you want them that way. If you want to combine them into one transaction,
you use BEGIN.

In PostgreSQL the changes are visible as long as you run the query.

If they're not in a transaction

On Fri, 19 Jul 2002, Steve Brett wrote:

AFAIAA each query sent to the backend is a transaction and is treated as
such unless you explicitly send a BEGIN.

If you send a BEGIN then you can send multiple INSERTS etc and then do a
manual COMMIT.

I've never used Oracle (as the comapny I work for can't afford it !) so I
wouldn't be able to supply you with a comparison.

Steve

-----Original Message-----
From: stefan@extum.com [mailto:stefan@extum.com]
Sent: 20 July 2002 12:51
To: pgsql-general@postgresql.org
Subject: [GENERAL] COMMIT in PostgreSQL

Hey,

I was running a script which does some INSERTS and UPDATE
some table. I
found that there is no need for COMMIT; After each statement
the TABLE is
immediately commited. Other session via psql can sees ASAP
the changes to
the table. So it seems a bit different than Oracle's COMMIT .
Can somebody
explain me why this is so in PostgreSQL ?

Are the modifications done without commit statement ?

PostgreSQL looks really interesting and seems to be good SQL
compliant.
stefan

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout <kleptog@svana.org> writes:

On Sat, Jul 20, 2002 at 03:03:02PM +0300, stefan@extum.com wrote:

thanks a lot all for comments. So in PostgreSQL each query is a
transaction ?

If you want them that way. If you want to combine them into one transaction,
you use BEGIN.

Right; otherwise you get the sort of behavior that some other databases
call auto-commit.

While we can't change this without breaking huge amounts of client code,
there has been talk of offering a parameter setting that could be
changed to support the SQL-standard behavior (which could be thought of
as auto-BEGIN in Postgres terms: any statement implicitly causes a
BEGIN, and then you stay in that transaction until you explicitly say
COMMIT).

regards, tom lane

On Fri, 2002-07-19 at 15:48, Tom Lane wrote:

Martijn van Oosterhout <kleptog@svana.org> writes:

On Sat, Jul 20, 2002 at 03:03:02PM +0300, stefan@extum.com wrote:

thanks a lot all for comments. So in PostgreSQL each query is a
transaction ?

If you want them that way. If you want to combine them into one transaction,
you use BEGIN.

Right; otherwise you get the sort of behavior that some other databases
call auto-commit.

While we can't change this without breaking huge amounts of client code,
there has been talk of offering a parameter setting that could be
changed to support the SQL-standard behavior (which could be thought of
as auto-BEGIN in Postgres terms: any statement implicitly causes a
BEGIN, and then you stay in that transaction until you explicitly say
COMMIT).

How much client code would break if pg would start-up in 'autocommit',
and revert to standard SQL after receiving the first 'COMMIT'?
(Supposing - I don't know that - that standard SQL but an implicit BEGIN
at statements issued after a COMMIT).

Benefits: adapting SQL code that excepts standard behaviour would be
easy to fix by just requiring to enter a BEGIN upon opening the
connection.

Just a thought.

cheers
-- vbi

--
secure email with gpg http://fortytwo.ch/gpg

"Adrian 'Dagurashibanipal' von Bidder" <avbidder@fortytwo.ch> writes:

How much client code would break if pg would start-up in 'autocommit',
and revert to standard SQL after receiving the first 'COMMIT'?

As near as I can tell, that would break *both* extant PG clients and
SQL-spec-compliant clients, just at different times in their runs.

regards, tom lane

AFAIAA each query sent to the backend is a transaction and is treated

as

such unless you explicitly send a BEGIN.

If you send a BEGIN then you can send multiple INSERTS etc and then do

a

manual COMMIT.

I have noticed this difference between Postgres and Oracle as well. I
wonder if there is a rollback mechanism as there is in Oracle. I mean if
you use a BEGIN, can you rollback in Postgres?

--
Susan Lane
DPN, Incorporated
4631 Spring Mountain Road
Las Vegas, NV 89102
Email suel@dpn.com
Ph. (702) 873-3282
Fax (702) 873-3913
http://www.dpn.com

On Fri, 2002-07-19 at 17:06, Susan Lane wrote:

AFAIAA each query sent to the backend is a transaction and is treated

as

such unless you explicitly send a BEGIN.

If you send a BEGIN then you can send multiple INSERTS etc and then do

a

manual COMMIT.

I have noticed this difference between Postgres and Oracle as well. I
wonder if there is a rollback mechanism as there is in Oracle. I mean if
you use a BEGIN, can you rollback in Postgres?

Yes

--

Tom Jenkins
Development InfoStructure
http://www.devis.com

On Fri, 19 Jul 2002, Susan Lane wrote:

I have noticed this difference between Postgres and Oracle as well. I
wonder if there is a rollback mechanism as there is in Oracle. I mean if
you use a BEGIN, can you rollback in Postgres?

Yes. If you do the following:

begin;
select * from table1;
update table2 set field1='yada' where id=1234;
delete from table3 where id=1243;
insert into table4 (fielda, fieldb) values ('hello',45);
rollback;

Then all the changes (except for sequence counters being incremented) will
be rolled back.

Note that if you do:

begin;
select * from ;
insert into table (name) values('me');
commit;

The insert will fail because you had a failure in your select query.
Unlike many other databases where data change failures are the only ones
to automatically rollback a transaction, in postgresql, almost any error
in a transaction will cause it to rollback.

Note that postgresql does NOT support nested transactions either.

Susan Lane wrote:

I have noticed this difference between Postgres and Oracle as well. I
wonder if there is a rollback mechanism as there is in Oracle. I mean if
you use a BEGIN, can you rollback in Postgres?

--
Susan Lane

How about ABORT? ROLLBACK?

Alex

Thanks!

""scott.marlowe"" <scott.marlowe@ihs.com> wrote in message
news:Pine.LNX.4.44.0207191529000.4638-100000@css120.ihs.com...

On Fri, 19 Jul 2002, Susan Lane wrote:

I have noticed this difference between Postgres and Oracle as well. I
wonder if there is a rollback mechanism as there is in Oracle. I mean

if

thanks a lot all for comments. So in PostgreSQL each query is a
transaction ?

Well the point was not to have a comparation with Oracle but to try
understanding the model what PostgreSQL does. It is interesting.

If you are under Oracle and say:

INSERT ...
UPDATE ...

and that's all another user will not see those updates if you don't COMMIT
the changes. After COMMIT all other users can see the changes.

In PostgreSQL the changes are visible as long as you run the query.

stefan

On Fri, 19 Jul 2002, Steve Brett wrote:

That's right you define a block transaction in this way.
I was a bit confused because each query performed the results are
commited in table without no need of COMMIT.

thank you,
stefan

Thanks all for help.

I really like the way how postgresql lists are working and, answers,
feedback.
Stefan

On Sat, 20 Jul 2002 stefan@extum.com wrote: