From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jan Peters <petersjan@gmx.at>
Cc: pgsql-sql@postgresql.org
Subject: Re: Rollback locks table - why?
Date: Thu, 20 Mar 2008 11:11:34 -0400
Message-ID: <9890.1206025894@sss.pgh.pa.us> (raw)
In-Reply-To: <20080320134208.42400@gmx.net>
References: <20080320134208.42400@gmx.net>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, petersjan@gmx.at
Subject: Re: Rollback locks table - why?
In-Reply-To: <9890.1206025894@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox