hello all,
I would like to mention something in this regard.
I have executed all the commands given here in the same order, but what the
auther is saying that after insert fails whatever u have inserted rolls back,
this is not the case .
as all of us knows Postgre works in autocommit mode, so when user successfully
inserts a row in a table and then again tries to insert the same row then
already entered record will not get deleted from tha table. On top of
autocommit, we are executing COMMIT;
so it will further explicitely commits the first transaction and will NOT
ROLLBACK
the succefully entered row.
And that way also this should not happen in any condition, otherwise it will be
so much duplication of work.
if anything is incorrect pls rectify it , but I have just now executed all the
set of commands in the exactly same fashion as given in the reply mail by Alex.
thanks
Kshipra
Alex Bolenok wrote:
> > Hi you all!
> >
> > I nedd to figure out how to instruct PostgreSQL to ROLLBACK automatically
> when
> > something goes bad within a transaction (i.e. it fell in *ABORT STATE*).
> >
> > Do you think a trigger will do?
> >
> > Do you thing only a hack will do? (Like calling the rollback code after
> calling
> > the aborting code)
> >
> > Or is there a set I don't know about?
> >
> > Thanx!!
> >
> > Cheers,
> > Haroldo.
>
> If something bad happens inside the transaction, all previous changes made
> within the transaction are discarded, even if you COMMIT the changes. See
> output:
>
> peroon=# SELECT * FROM foo;
> id | name
> ----+------
> (0 rows)
>
> peroon=# BEGIN;
> BEGIN
> peroon=# INSERT INTO foo VALUES (1, 'Some value');
> INSERT 255330 1
> peroon=# SELECT * FROM foo;
> id | name
> ----+------------
> 1 | Some value
> (1 row)
>
> peroon=# INSERT INTO foo VALUES (1, 'The same value');
> ERROR: Cannot insert a duplicate key into unique index foo_pkey
> peroon=# COMMIT;
> COMMIT
> peroon=# SELECT * FROM foo;
> id | name
> ----+------
> (0 rows)
>
> So when your transaction is in ABORT STATE, you may use COMMIT, ROLLBACK or
> ABORT to close it, and you will get the same result.
>
> If exception happens inside a function or trigger, the whole stack is rolled
> back automatically (PostgreSQL doesn't support nested tranactions), so you
> don't have to worry about rolling it back manually.
>
> BTW, ABORT is just an alias for ROLLBACK in PostgreSQL, so 'rollback code'
> and 'aborting code' you wrote about do the same things :)
>
> Alex Bolenok.