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.
