Thread: Rollback locks table - why?
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 runjust: 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" errormessage and after that I get the dreaded "current transaction is aborted" error message and the system reports an EXCLUSIVELOCK 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
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 tryingto 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
Hello Craig, > 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? Yes, you are correct: I just want to issue an insertion of a row at the very beginning of an application start. So, whenthe table "test" is empty, the row with "runde = 0" should be inserted. If this row is already present, it should beupdated. How do I do a "manual" ROLLBACK? Regards and thank you again in advance Jan -- GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen! Jetzt dabei sein: http://www.shortview.de/?mc=sv_ext_mf@gmx
Jan Peters wrote: > Yes, you are correct: I just want to issue an insertion of a row at the very beginning of an application start. So, whenthe table "test" is empty, the row with "runde = 0" should be inserted. If this row is already present, it should beupdated. > This is quite common - you might find past discussions about alternatives to the SQL:2003 MERGE statement for PostgreSQL informative. > How do I do a "manual" ROLLBACK? > To roll back to a particular savepoint: ROLLBACK TO SAVEPOINT spname; However, for what you're trying to do another option is to just issue a pair of statements that'll work anyway. You should probably test and see which works better, faster, etc. Assuming there's only one row in the table so I don't need any more specific WHERE clauses, I might try something like: UPDATE my_unique_table SET col = some_col_val; INSERT INTO my_unique_table ( col ) SELECT some_col_val WHERE NOT EXISTS (SELECT 1 FROM my_unique_table WHERE someotherconstraint = somevalue); because one of those two is guaranteed to work whether or not there are any rows in the table (assuming I got it all right, at least). Presumably you're doing something like limiting app instances to one per user, in which case you'd need additional constraints in the above (you wouldn't be keeping just one row anymore, but one per user) and some more indexes but the idea's basically the same. Maybe you should tell the readers of this list a little more about what you're trying to do and why? -- Craig Ringer
Hello Craig, > > Maybe you should tell the readers of this list a little more about what > you're trying to do and why? > -- ok, so I'll do that: I am programming a small economic Java simulation/serious game that has to calculate the GDP after 12rounds. For doing this, I need the capital of each company at the very beginning of the game (i.e. runde (round) "0").So when the user logs into the game, his/her client has to write a row in the the corresponding timeline table whichholds the initial state of the company. For convinience reasons (since this will be used in laboratory conditions, only)I would just not want to purge the tables each time the game is restarted but would like to be sure, that the initialvalues are there for sure. The timeline can be dealt with via a timestamp that's also present. So basically that's that. Regards Jan -- GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen! Jetzt dabei sein: http://www.shortview.de/?mc=sv_ext_mf@gmx
"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
Hello Tom, > 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. That is exactly what confuses me. I just put the above code in the SQL Query Editor of my Admin App (e.g. PGAdmin III) andclick the run button, nothing else. First time I get "duplicate unique key" second time " ERROR: current transaction is aborted, commands ignored until end of transaction block"... Regards Jan -- GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen! Jetzt dabei sein: http://www.shortview.de/?mc=sv_ext_mf@gmx
Hm, Tom, your're right, it works in the console, but not in the editor window, strange... -- Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger
Yes, Tom, it looks like this: Locked Transaction:458553 I misread that. Sorry for that, I am actually a Geographer and just "using" the tools at hand. So I was not clear enough,it is a lock, but it's just the one of the transaction, right. That still does not explain why the Query Editor locksup, hm. I just tried to send the transaction to my servlet and it worked on the server es well. Seems the JDBC driverinterprets the string differently. Well, if it's working on the server, I will not keep trying in the editor, althoughit might be nice, to have a solution. But as long as I use the console for testing purposes from now on, it's finefor me. Thanks again, Craig and Tom, if you still have an idea what might cause this behaviour, let me know. Otherwise: Happy Easter! Regards Jan -- GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen! Jetzt dabei sein: http://www.shortview.de/?mc=sv_ext_mf@gmx