Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id D213A2E0048
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 20 Mar 2008 10:57:35 -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 60199-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 20 Mar 2008 10:57:31 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from www.postnewspapers.com.au (www.postnewspapers.com.au
	[202.61.230.242])
	by postgresql.org (Postfix) with ESMTP id 627912E0045
	for <pgsql-sql@postgresql.org>; Thu, 20 Mar 2008 10:57:31 -0300 (ADT)
Received: from mail.postnewspapers.com.au
	(202-89-185-120.static.dsl.amnet.net.au [202.89.185.120])
	by www.postnewspapers.com.au (Postfix) with ESMTP id 6179A364;
	Thu, 20 Mar 2008 22:57:25 +0900 (WST)
Received: from localhost (access [127.0.0.1])
	by mail.postnewspapers.com.au (Postfix) with ESMTP id 0D9AB1E0435;
	Thu, 20 Mar 2008 22:57:26 +0900 (WST)
X-Virus-Scanned: Debian amavisd-new at postnewspapers.com.au
Received: from mail.postnewspapers.com.au ([127.0.0.1])
	by localhost (access.postnewspapers.com.au [127.0.0.1]) (amavisd-new,
	port 10024)
	with LMTP id u+WCL0GNM0AG; Thu, 20 Mar 2008 22:57:23 +0900 (WST)
Received: from [203.161.97.213] (203.161.97.213.static.amnet.net.au
	[203.161.97.213])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(Client CN "Craig Ringer",
	Issuer "POST Certificate Authority" (verified OK))
	by mail.postnewspapers.com.au (Postfix) with ESMTP id 0DEA51E0434;
	Thu, 20 Mar 2008 22:57:23 +0900 (WST)
Message-ID: <47E26D3B.4010102@postnewspapers.com.au>
Date: Thu, 20 Mar 2008 22:57:15 +0900
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: Jan Peters <petersjan@gmx.at>
Cc: pgsql-sql@postgresql.org
Subject: Re: Rollback locks table - why?
References: <20080320134208.42400@gmx.net>
In-Reply-To: <20080320134208.42400@gmx.net>
X-Enigmail-Version: 0.95.6
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/147
X-Sequence-Number: 30475

Jan Peters wrote:
> 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).
>   
Am I correct in saying that your issue is really that you want an error 
to trigger an automatic rollback to the last savepoint, but it does not 
do so ?

If you issue the ROLLBACK manually (after you get the constraint error 
or a "current transaction is aborted" message) does it work as you would 
expect?

--
Craig Ringer