Scott Marlowe wrote:
On Wed, Jun 18, 2008 at 2:58 PM, Steve Clark <sclark@xxxxxxxxxxxxx> wrote:
Hello List,
I have acquired the task of maintaining and existing application that uses
postgresql. I am only lightly versed
in sql and have the following problem I need to solve.
I have a table in which each row has a column - row_number. The row_numbers
need to be sequential.
I question this. Someone may have arbitrarily decided it needs to be
sequential, but it's quite likely that your business process does NOT
need it to be sequential, at least not in the database.
Any solution that involves actually renumbering the rows is a
guaranteed performance loser, and a good way to scramble your data in
wonderful, horrible, scary new ways.
It's generally better to use a separate table with a numbering schema
in it that points to the rows so you don't have to actually update the
rows themselves, only the numbering scheme. But even that is fraught
with horrible possibilities in terms of social engineering problems
that show up.
Update number 154929! I was working on it last month and it needs x
added to it!
Someone updates 154929 only to find out that they should have updated
154924 instead because of the renumbering.
Thanks Scott,
I realize this is certainly not the best design - but at this point in time it can't be changed. The table
is rarely updated and never concurrently and is very small, typically less than 100 rows so there really is
no performance impact.
Steve
