SELECT .... WHERE itemKey=:lastItemKey AND location>:lastLocation UNION SELECT ... WHERE itemKey>:lastItemKey
You could solve the OFFSET/LIMIT modification problem if you could keep the transaction open, but I don't know if it's applicable in your case.
Andras Kutrovics wrote:
Franco Bruno Borghesi wrote:
Hi!
Sorry for being late with the answer, I was busy at one of our customer;)
wouldn't it be easier using offset & limit?: you always select from the table with an itemkey,location order by clause. You save the current offset between requests, and for every request you re-execute the query with a different offset.
Sure, but sometimes I have to query by name, and dont want to create another query component.
If you still want to go with what you already have, you should keep the lastItemKey and lastLocaltion values between requests, and your where clause should be something like:
WHERE (itemKey=:lastItemKey AND location>:lastLocation) OR (itemKey>:lastItemKey)
It works perfectly, but I have to consider the performance issue, because if i use 'or' statement, postgres doesn't use index scan, and I also have tables with 3 or more keys and 500.000 records , where the performance of this method is poor. Maybe I will end up using limit and offset in case of incremental fetching,but if the table is modified between requests, it can behave weird. Is there a perfect solution to this?
Sorry for the english
Thank you again,
Andras Kutrovics
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
