Thread

1. ROLLBACK automatically

   Haroldo Stenger <hstenger@adinet.com.uy> — 2000-07-24T03:29:25Z

   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.
   -- 
   ----------------------+------------------------
    Haroldo Stenger      | hstenger@ieee.org
    Montevideo, Uruguay. | hstenger@adinet.com.uy
   ----------------------+------------------------
    Visit UYLUG Web Site: http://www.linux.org.uy
   -----------------------------------------------
   
   

2. Re: ROLLBACK automatically

   Alex Bolenok <abolen@chat.ru> — 2000-07-24T07:04:18Z

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

3. Re: ROLLBACK automatically

   Kshipra <kshipra@mahindrabt.com> — 2000-07-24T11:55:56Z

   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.
   
   
   

4. Re: ROLLBACK automatically

   Alex Bolenok <abolen@chat.ru> — 2000-07-24T12:57:23Z

   > 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
   
   Autocommit mode means that when you run a query _outside_ the transaction
   block, each statement of the query starts its own transaction block
   implicitly, executes itself and then commits the transaction. When you
   _explicitly_ start the transaction block with BEGIN statement, if will not
   be commited until you _explicitly_ commit it with COMMIT statement.
   
   Try to perform following statements (that means _all_ statements, including
   BEGIN and COMMIT):
   
   peroon=# CREATE TABLE foo (id INT4 PRIMARY KEY, name TEXT);
   NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for
   table 'foo'
   CREATE
   peroon=# BEGIN;
   BEGIN
   peroon=# INSERT INTO foo VALUES (1, 'Some value');
   INSERT 258925 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)
   
   What have we done?
   
   First we created table foo with id as PRIMARY KEY field. A unique index was
   created for this field, so if we try to insert a duplicate value into this
   field we will get an error.
   
   Then we started an explicit transaction block with BEGIN statement and
   inserted a value into the PRIMARY KEY field of the table foo. This operation
   completed successfully and when we SELECT'ed all values from this table we
   found the record we had just inserted.
   
   Then we inserted the duplicate value into id field. This action violated
   UNIQUE INDEX created by the PRIMARY KEY constraint and resulted in error.
   The transacion falled into ABORT STATE. All queries in this state are
   ignored until we ROLLBACK (or COMMIT, or ABORT) this transaction manually.
   
   Then we performed a commit statement. It commited nothing, but it finished
   the transaction block.
   
   And finally we SELECT'ed all values from the table foo. As it was expected,
   we found no values in it. That means that the first insert statement had
   been rolled back though we didn't perform ROLLBACK but COMMIT.
   
   Alex Bolenok.
   
   
   

5. Re: ROLLBACK automatically

   Sevo Stille <sevo@ip23.net> — 2000-07-24T14:44:29Z

   Kshipra wrote:
   > 
   > 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,
   
   Change that to "MAY work in autocommit mode".
   
   > 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.
   
   For different reasons. SQL has no notion of "inserting the same row". If
   you insert something twice, you will have two rows with the same values
   in your table - which may be perfectly valid in some cases.
   
   > On top of
   > autocommit, we are executing COMMIT;
   > so it will further explicitely commits the first transaction 
   
   You can't commit anything twice. Autocommit implicitly creates a
   transaction around each statement. Any extra explicit COMMIT outside a
   transaction block won't do anything but raise a warning.
   
   > and will NOT
   > ROLLBACK
   > the succefully entered row.
   
   Nor would it inside a transaction block.
   
   CREATE TABLE foo (t text);
   INSERT into foo values ('bar');
   INSERT into foo values ('bar');
   SELECT * from foo;
   DROP table foo;
   
   will give you two instances of bar inside table foo, just like the
   transactional version 
   
   CREATE TABLE foo (t text);
   BEGIN TRANSACTION;
   INSERT into foo values ('bar');
   INSERT into foo values ('bar');
   COMMIT;
   SELECT * from foo;
   DROP table foo;
   
   would. There is a difference between 
   
   CREATE TABLE foo (i int4);
   INSERT into foo values (1);
   INSERT into foo values ('bar');
   SELECT * from foo;
   DROP table foo;
   
   and
   
   CREATE TABLE foo (i int4);
   BEGIN TRANSACTION;
   INSERT into foo values (1);
   INSERT into foo values ('bar');
   COMMIT;
   SELECT * from foo;
   DROP table foo;
   
   in that the data type error on the second insert will make the entire
   transaction fail in the second example leaving you with an empty table,
   where the first statement in the first example makes it into the table,
   as its autocommit transaction has already successfully comitted before
   the faulty statement gets executed.
   
   > And that way also this should not happen in any condition, otherwise it will be
   > so much duplication of work.
   
   Sorry, you lost me there...
   
   Sevo
   
   
   -- 
   sevo@ip23.net
   
   

6. Re: ROLLBACK automatically

   Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> — 2000-07-24T23:47:44Z

   And what if I didn't want the commit to fail? What if I half expected
   the insert
   to fail and then want to do an update instead? That's a pretty common
   pattern - try
   to insert, if fail - do an update instead.
   
   Is this behaviour some kind of standard? Can it be changed?
   
   Alex Bolenok wrote:
   > Autocommit mode means that when you run a query _outside_ the transaction
   > block, each statement of the query starts its own transaction block
   > implicitly, executes itself and then commits the transaction. When you
   > _explicitly_ start the transaction block with BEGIN statement, if will not
   > be commited until you _explicitly_ commit it with COMMIT statement.
   > 
   > Try to perform following statements (that means _all_ statements, including
   > BEGIN and COMMIT):
   > 
   > peroon=# CREATE TABLE foo (id INT4 PRIMARY KEY, name TEXT);
   > NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for
   > table 'foo'
   > CREATE
   > peroon=# BEGIN;
   > BEGIN
   > peroon=# INSERT INTO foo VALUES (1, 'Some value');
   > INSERT 258925 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)
   > 
   > What have we done?
   > 
   > First we created table foo with id as PRIMARY KEY field. A unique index was
   > created for this field, so if we try to insert a duplicate value into this
   > field we will get an error.
   > 
   > Then we started an explicit transaction block with BEGIN statement and
   > inserted a value into the PRIMARY KEY field of the table foo. This operation
   > completed successfully and when we SELECT'ed all values from this table we
   > found the record we had just inserted.
   > 
   > Then we inserted the duplicate value into id field. This action violated
   > UNIQUE INDEX created by the PRIMARY KEY constraint and resulted in error.
   > The transacion falled into ABORT STATE. All queries in this state are
   > ignored until we ROLLBACK (or COMMIT, or ABORT) this transaction manually.
   > 
   > Then we performed a commit statement. It commited nothing, but it finished
   > the transaction block.
   > 
   > And finally we SELECT'ed all values from the table foo. As it was expected,
   > we found no values in it. That means that the first insert statement had
   > been rolled back though we didn't perform ROLLBACK but COMMIT.
   > 
   > Alex Bolenok.
   
   

7. Re: ROLLBACK automatically

   Alex Bolenok <abolen@chat.ru> — 2000-07-25T07:15:50Z

   > And what if I didn't want the commit to fail? What if I half expected
   > the insert
   > to fail and then want to do an update instead? That's a pretty common
   > pattern - try
   > to insert, if fail - do an update instead.
   >
   > Is this behaviour some kind of standard? Can it be changed?
   
   Not in this life :)
   
   PostgreSQL does not support nested transactions. If transaction fails, the
   transaction fails.
   
   You may write a trigger instead of (or combined with) UNIQUE CHECK, like
   this:
   
   CREATE FUNCTION fn_foo_insupd() RETURNS OPAQUE AS '
   DECLARE
       chk INT4;
   BEGIN
       SELECT id INTO chk FROM foo WHERE id = NEW.id;
       IF FOUND THEN
           PERFORM some_action(NEW.id);
           RETURN NULL;
       END IF;
       RETURN NEW;
   END;
   ' LANGUAGE 'plpgsql';
   
   CREATE TRIGGER trg_foo_insupd BEFORE INSERT OR UPDATE ON foo FOR EACH ROW
   EXECUTE PROCEDURE fn_foo_ins();
   
   Note that if you insert a row into a keyed table that has a trigger, you
   cannot delete this row or update the key in the same transaction.
   
   Alex Bolenok.
   
   
   

8. Re: ROLLBACK automatically

   Peter Eisentraut <peter_e@gmx.net> — 2000-07-25T20:40:17Z

   Chris Bitmead writes:
   
   > Is this behaviour some kind of standard? Can it be changed?
   
   In the past the answer to the question "Does a statement-level error
   warrant a transaction abort?" has usually hinged on the interpretation of
   the following clauses of the SQL92 standard.
   
       3.3.4.1  Exceptions
   
       The phrase "an exception condition is raised:", followed by the
       name of a condition, is used in General Rules and elsewhere to
       indicate that the execution of a statement is unsuccessful, ap-
       plication of General Rules, other than those of Subclause 12.3,
       "<procedure>", and Subclause 20.1, "<direct SQL statement>", may
       be terminated, diagnostic information is to be made available,
       and execution of the statement is to have no effect on SQL-data or
       schemas. 
   
   
       4.28 SQL-transactions
   
       [...]
   
       The execution of a <rollback statement> may be initiated implicitly
       by an implementation when it detects unrecoverable errors. When
       such an error occurs, an exception condition is raised: transaction
       rollback with an implementation-defined subclass code.
   
   
   So essentially we classify all errors as unrecoverable, which is certainly
   dumb, but legal. But then we still don't comply because we don't execute
   the rollback automatically but instead hang in some sort of "invalid"
   state.
   
   There is certainly big demand for getting rid of this restriction though,
   but the code changes could end up being very extensive.
   
   
   -- 
   Peter Eisentraut                  Sernanders väg 10:115
   peter_e@gmx.net                   75262 Uppsala
   http://yi.org/peter-e/            Sweden