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]> 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]
> <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');>
> .
> To post to this group, send email to [email protected]
> <javascript:_e(%7B%7D,'cvml','[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