Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id DDCAC2E0048
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 20 Mar 2008 10:49:01 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 58324-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 20 Mar 2008 10:48:54 -0300 (ADT)
X-Greylist: delayed 00:06:41.406685 by SQLgrey-1.7.5
Received: from mail.gmx.net (mail.gmx.net [213.165.64.20])
	by postgresql.org (Postfix) with SMTP id 5C0812E0045
	for <pgsql-sql@postgresql.org>; Thu, 20 Mar 2008 10:48:50 -0300 (ADT)
Received: (qmail 2695 invoked by uid 0); 20 Mar 2008 13:42:08 -0000
Received: from 62.218.164.126 by www166.gmx.net with HTTP;
	Thu, 20 Mar 2008 14:42:07 +0100 (CET)
Content-Type: text/plain; charset="iso-8859-1"
Date: Thu, 20 Mar 2008 14:42:08 +0100
From: "Jan Peters" <petersjan@gmx.at>
Message-ID: <20080320134208.42400@gmx.net>
MIME-Version: 1.0
Subject: Rollback locks table - why?
To: pgsql-sql@postgresql.org
X-Authenticated: #16900582
X-Flags: 0001
X-Mailer: WWW-Mail 6100 (Global Message Exchange)
X-Priority: 3
X-Provags-ID: V01U2FsdGVkX18oKkg/mmQo2pjyR8Zy/8jJpyG/FiOYygHVzoacwD
	ER1NaVMejhifP4kSqPmNwh9nRcq0oN6vJKTw==
Content-Transfer-Encoding: 8bit
X-GMX-UID: iFvbcqtdeWUoVY6AcnRzSlIxU3U4N49W
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200803/145
X-Sequence-Number: 30473

Hello list,
I am a bit confused. See the code below:

BEGIN;
SAVEPOINT sp1;
INSERT INTO test(id,runde) VALUES(2, 0);
--if there is a unique key violstion:
ROLLBACK TO sp1;
UPDATE test  SET id = 1000 WHERE runde = 0;
COMMIT;

When I first run this statement, I do not get any error message, but also there's also no INSERT on table test. If I run just:

INSERT INTO test(id,runde) VALUES(2, 0);

on its own, it works. (i.e. I get a new row). 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).

Any ideas what I am doing wrong?

Using PostGreSQL 8.2.7 on Windows XP.

Regards and many thanks in advance
Jan
-- 
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger