Re: ROLLBACK automatically - Mailing list pgsql-general

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