Re: Numbering a records

If you strongly require this data-behavior, you, I think, must create
function afterUpdateOrInsertOrDelete(owner), which locks owner's rows
and recalculate position and batch, if needed.
But, imho, experience says that keeping data similar to your position
(ordinal number _without_ holes) is inefficient because concurency
conflicts on paralel updates.

regards,
pajout

NTPT wrote:

>I have this table
>
>content (id int8,owner int8,position int8,timestamp int8,description text,batch int8)
>
>Table is inserted/deleted frequently, 'id' is almoust random.
>
>
>
>I insert to the table following set of rows :
>
> 12345, 1000,1,timestamp,blabla,0
> 12349, 1000,2,timestamp,blabla,0
> 12355, 1001,1,timestamp,blabla,0
> 12389, 1000,3,timestamp,blabla,0
> etc.. There is a many of these records.
>
>Now I need to od some select like this
>
>select * from content where owner='1000' order by timestamp with some limits, offsets etc. It is OK, no problem.
>
>Other select, like to need select a record of user 1000 WHERE position >5 AND position <150 is OK,
>
>But now, some records are inserted, some deleted, some have the timestamp column updated, so column 'position' is not sequential anymore. I need to create some UPDATE ..... where owner='id of the owner' ORDER by timestamp, that will
>recalculate column 'position' to contain actual position inside a timestamp ordered table ? (ie. colum position contain an actual order of records that is owned by 'owner' ordered by timestamp ).Please note that usage of plain LIMIT/OFFSET is not what I need.
>
>
>in close relation to this, I have another problem. I NEED to assign bath number to records from this example. ie in the table content, where owner='id of the owner' ordered by timestamp, set of first 500 record should have the same 'bath' number '1', set of 2nd 500 records should have its batch number '2' etc...
>
>Is it possible and how it can be done ?
>
>
>PS: Execuse my bad english.
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>