> 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.