Re: COMMIT in PostgreSQL

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.