 | |
Continuing a transaction after INSERT failure
comp.databases.postgresql comp.databases.postgresql
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Continuing a transaction after INSERT failure -
04-01-2009
, 11:12 AM
#2
| |||
| |||
|
|
I want to give people with the same name consecutive serial numbers. The way I'm trying to achieve this is to insert everyone with a serial number of 1 at first and catch exceptions that may result from a violated uniqueness constraint. Then I retry with an incremented serial number. I've used this strategy originally with sqlite3 where it works, now with PostgreSQL (8.3) the transaction is aborted. Is there a way to recover from the violated uniqueness constraint and continue the transaction? Or is there a better strategy entirely? |
the savepoint leaving the "overall" transaction valid.
But to me the whole concept does not sound that good.
Why not simply insert them with a generated key that might not be consecutive,
but generate the consecutive numbers during the display in the front end. Do you
have any real (e.g. legal) requirement that those numbers are consecutive?
Usually this is a very bad design and will eventually lead to a lot more
problems (e.g. what happens if you delete a row).
Thomas
#3
| |||
| |||
|
| Michael Schuerig wrote on 01.04.2009 18:12: I want to give people with the same name consecutive serial numbers. The way I'm trying to achieve this is to insert everyone with a serial number of 1 at first and catch exceptions that may result from a violated uniqueness constraint. Then I retry with an incremented serial number. I've used this strategy originally with sqlite3 where it works, now with PostgreSQL (8.3) the transaction is aborted. Is there a way to recover from the violated uniqueness constraint and continue the transaction? Or is there a better strategy entirely? You need to set a savepoint before you "try" to insert, then you can rollback to the savepoint leaving the "overall" transaction valid. |
Quote:
|
But to me the whole concept does not sound that good. Why not simply insert them with a generated key that might not be consecutive, but generate the consecutive numbers during the display in the front end. |
easy and very localized way to handle them. In particular, I don't want
to make display code any more complicated because of them.
Michael
--
Michael Schuerig
mailto:michael (AT) schuerig (DOT) de
http://www.schuerig.de/michael/
#4
| |||
| |||
|
|
I want to give people with the same name consecutive serial numbers. The way I'm trying to achieve this is to insert everyone with a serial number of 1 at first and catch exceptions that may result from a violated uniqueness constraint. Then I retry with an incremented serial number. But to me the whole concept does not sound that good. Why not simply insert them with a generated key that might not be consecutive, but generate the consecutive numbers during the display in the front end. In my case, duplicates are rare to begin with, thus I'd like to have an easy and very localized way to handle them. In particular, I don't want to make display code any more complicated because of them. |
if you ever delete rows.
But apart from that, your technique seems very expensive and slow:
If there are already n rows in the table, you will need n+1 INSERT
statements, each of which will result in index and table reads.
Why don't you either start with "SELECT max(id)+1 FROM tab"
instead of 1 or keep the current maximum in a separate one row
table which you use with
UPDATE counter SET maxid=maxid+1 RETURNING maxid
to get the next value.
That would be much cheaper.
Yours,
Laurenz Albe
#5
| |||
| |||
|
|
You will never get around the problem with "holes" in your sequence if you ever delete rows. But apart from that, your technique seems very expensive and slow: If there are already n rows in the table, you will need n+1 INSERT statements, each of which will result in index and table reads. |
on lastname, firstname, serial_number, i.e., it is only triggered when
there are duplicate names to begin with. But you're right, there's an
opportunity for optimization in determining the biggest relevant serial
number in a case a collision occurs.
Michael
--
Michael Schuerig
mailto:michael (AT) schuerig (DOT) de
http://www.schuerig.de/michael/
#6
| |||
| |||
|
|
But apart from that, your technique seems very expensive and slow: If there are already n rows in the table, you will need n+1 INSERT statements, each of which will result in index and table reads. I see, I think you're misunderstanding me. The uniqueness constraint is on lastname, firstname, serial_number, i.e., it is only triggered when there are duplicate names to begin with. |
If the danger of constraint violation upon INSERT is low,
your technique is actually good.
Yours,
Laurenz Albe
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.