Re: renumber table

Scott Marlowe wrote:
> On Wed, Jun 18, 2008 at 2:58 PM, Steve Clark <sclark(at)netwolves(dot)com> 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