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.
