Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 1F7932E0153
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 20 Mar 2008 12:12:00 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 42054-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 20 Mar 2008 12:11:42 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 5D6342E020C
	for <pgsql-sql@postgresql.org>; Thu, 20 Mar 2008 12:11:37 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m2KFBYup009891;
	Thu, 20 Mar 2008 11:11:34 -0400 (EDT)
To: "Jan Peters" <petersjan@gmx.at>
cc: pgsql-sql@postgresql.org
Subject: Re: Rollback locks table - why? 
In-reply-to: <20080320134208.42400@gmx.net> 
References: <20080320134208.42400@gmx.net>
Comments: In-reply-to "Jan Peters" <petersjan@gmx.at>
	message dated "Thu, 20 Mar 2008 14:42:08 +0100"
Date: Thu, 20 Mar 2008 11:11:34 -0400
Message-ID: <9890.1206025894@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/154
X-Sequence-Number: 30482

"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