Hi,

Yes if there is an update the row is moved at the end, like the sample I 
post. This is why some database gui frontend force the sort of a column. 
Maybe the implementation of H2 make such feature request nearly impossible 
to accomplish ?

-Nicolas
Le lundi 13 octobre 2014 20:04:59 UTC+2, Thomas Mueller a écrit :
>
> Hi,
>
> Does PostgreSQL makes a guarantee that two separate queries will use the 
> same row number for the same logical row? I think that's an implementation 
> detail. If there is a update between those calls (for example by another 
> connection), the row number might be different.
>
> Regards,
> Thomas
>
>
>
> On Thursday, October 9, 2014, Nicolas Fortin (OrbisGIS) <
> [email protected] <javascript:>> wrote:
>
>> Hi Thomas,
>>
>> Here a complete example of the difference between Postgre and H2:
>>
>> [raw]
>> drop table if exists streets;
>> create table streets(pk int primary key, length real);
>> insert into streets values (5, 55.2), (7, 33.2), (9, 151.1), (15, 48.2), 
>> (20, 14.6);
>> update streets set length = 60 where pk = 5;
>>
>> H2:
>> Execute request 5/6: select rowcpt, pk, length from (select ROW_NUMBER() 
>> OVER () rowcpt, pk ,length from streets) streetscount where length > 40
>>
>> ROWCPT(INTEGER)                PK(INTEGER)                    
>> LENGTH(REAL)                   
>> 1                              5                              
>> 60                             
>> 2                              9                              151,1
>> 3                              15                             48,2
>>
>> Execute request 6/6: select * from streets
>>
>> PK(INTEGER)                    LENGTH(REAL)                   
>> 5                              60                             
>> 7                              33,2
>> 9                              151,1
>> 15                             48,2
>> 20                             14,6
>>
>> Postgre:
>>
>> Execute request 5/6: select rowcpt, pk, length from (select ROW_NUMBER() 
>> OVER () rowcpt, pk ,length from streets) streetscount where length > 40
>>
>> rowcpt(int8)                   pk(int4)                       
>> length(float4)                 
>> 2                              9                              151,1
>> 3                              15                             48,2
>> 5                              5                              
>> 60                             
>>
>> Execute request 6/6: select * from streets
>>
>> pk(int4)                       length(float4)                 
>> 7                              33,2
>> 9                              151,1
>> 15                             48,2
>> 20                             14,6
>> 5                              60   
>> [/raw]
>>
>> If a show a table of the full content of streets, thanks to Postgre 
>> without caching primary key I could highlight the first, third and fourth 
>> lines.
>>
>> Following the result of "select * from streets", I expect h2 returning 
>> rowcpt 1,3,4
>>
>> We have decided to cache primary key (take time and memory for big table) 
>> in order to bypass this problem.
>>
>> Thanks for support.
>>
>> - Nicolas
>> IRSTV FR CNRS 2488
>>
>> Le jeudi 9 octobre 2014 07:52:38 UTC+2, Thomas Mueller a écrit :
>>>
>>> Hi,
>>>
>>> I'm not sure if I understand. You basically need a row id, which 
>>> uniquely identifies a row within a table? H2 supports the _rowid_ column 
>>> (like SQLite). Wouldn't that be enough for you? Is the problem that the 
>>> syntax (when using _rowid_) would be different to PostgreSQL?
>>>
>>> Maybe it's easier if you could provide a complete example (as small as 
>>> possible, but including "create table" and "insert" statements), that shows 
>>> what you need.
>>>
>>> Regards,
>>> Thomas
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Wed, Oct 8, 2014 at 4:50 PM, Nicolas Fortin (OrbisGIS) <
>>> [email protected]> wrote:
>>>
>>>> To go further I have to find a way to guess if TopTableFilter belongs 
>>>> to another preparedStatement. The only difference that I found is the 
>>>> Select.command is null in sub query.
>>>>
>>>> -- 
>>>> You received this message because you are subscribed to the Google 
>>>> Groups "H2 Database" group.
>>>> To unsubscribe from this group and stop receiving emails from it, send 
>>>> an email to [email protected].
>>>> To post to this group, send email to [email protected].
>>>> Visit this group at http://groups.google.com/group/h2-database.
>>>> For more options, visit https://groups.google.com/d/optout.
>>>>
>>>
>>> 

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Reply via email to