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
sequentialanymore. 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
containan actual order of records that is owned by 'owner' ordered by timestamp ).Please note that usage of plain
LIMIT/OFFSETis 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
thetable 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
>
>
>
