Thread: Questions about rollback and commit

Hello,

I tried Rollback after update or insert, but it doesn't work!
How can I rollback the update, insert or delete  changes I have made in
pgsql?
Do I need to do commit after each update or insert?
Do update, insert or delete consider as transactions?

Thanks in advance,
Fariba

From: "Fariba Noorbakhsh" <fNoorbakhsh@tecways.com>

> I tried Rollback after update or insert, but it doesn't work!
> How can I rollback the update, insert or delete  changes I have made in
> pgsql?
> Do I need to do commit after each update or insert?
> Do update, insert or delete consider as transactions?

By default, every SQL statement is wrapped in its own transaction. You'll
need to issue a BEGIN if you want to be able to ROLLBACK.

HTH

- Richard Huxton

Fariba Noorbakhsh wrote:
>
> Hello,
>
> I tried Rollback after update or insert, but it doesn't work!
> How can I rollback the update, insert or delete  changes I have made in
> pgsql?
> Do I need to do commit after each update or insert?
> Do update, insert or delete consider as transactions?
>

Every update and insert is wrapped in an implicit transaction. You can
however use transactions explicitly with 'begin;', you can then do
whatever sequence of updates and inserts until you give the 'commit;' or
'rollback;' command. When a SQL statement returns an error a rollback
will be issued by PostgreSQL and you would have to do the sequence
again. Next some examples from a psql session:

test=# insert into bar (foo) values ('foobar');
INSERT 15386527 1
test=# select * from bar;
  foo
--------
 foobar
(1 row)

test=# begin;
BEGIN
test=# insert into bar (foo) values ('barfoo');
INSERT 15386528 1
test=# select * from bar;
  foo
--------
 foobar
 barfoo
(2 rows)

test=# rollback;
ROLLBACK
test=# select * from bar;
  foo
--------
 foobar
(1 row)

test=#

test=# begin;
BEGIN
test=# insert into bar (foo) values ('barfoo');
INSERT 15386529 1
test=# commit;
COMMIT
test=# select * from bar;
  foo
--------
 foobar
 barfoo
(2 rows)

test=#



Regards,

Nils
--
Alles van waarde is weerloos
Lucebert