"Jan Peters" <petersjan@gmx.at> writes:
> If I run the transaction block from above again I get first a unique
> key violation (that is ok, because that's what I trying to check for)
> but there is NO rollback to sp1, only the "Unique Key" error message
> and after that I get the dreaded "current transaction is aborted"
> error message and the system reports an EXCLUSIVE LOCK on the table
> (<IDLE>in transaction).
The example works when executed by hand:
regression=# create table test(id int primary key, runde int);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
regression=# INSERT INTO test(id,runde) VALUES(2, 0);
INSERT 0 1
regression=# BEGIN;
BEGIN
regression=# SAVEPOINT sp1;
SAVEPOINT
regression=# INSERT INTO test(id,runde) VALUES(2, 0);
ERROR: duplicate key value violates unique constraint "test_pkey"
regression=# ROLLBACK TO sp1;
ROLLBACK
regression=# UPDATE test SET id = 1000 WHERE runde = 0;
UPDATE 1
regression=# commit;
COMMIT
so the problem must be in something you didn't show us. What exactly
are you doing to decide that you need to roll back? Also, none of these
statements (except the CREATE) would take an exclusive lock on test, so
there must be something else going on that you didn't show us.
regards, tom lane