Thread: 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
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 >
You probably need to execute a "begin;" statement first if you want to start a transaction. Sam > -----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 >
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;
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: > 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 >
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
> 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;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
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/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
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:
>
>
> 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
>
>
> > 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;
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
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
Attachment
"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
> > 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)