I think this is a common task, but I’m not sure how to do it.
I want to run a query that can return many records, display them 10 at a time, and be able to go forward/backward in the list. I’m not concerned about the list changing after the initial query.
I’m accessing this via a php web page. I’m thinking that maybe the best way to do this, other than re-running the query each time, is to put the results into a temporary table. I think this will work if I never call “disconnect” from the php script. My question is, when does my Postgres session end? Is there a timeout?
PHP will cause you trouble there, as it closes database connections at the end of scripts. As database transactions depend on connections, you would loose your temporary table then...
I know of two ways this sort of thing is done in general:
1) Using LIMIT and OFFSET (and ORDER BY!) and keeping track of the offset in a POST, GET or session variable. This has a few drawbacks:
It requires to do a COUNT first (sequential scan), and you're basically doing the same query each time (though with a limited result set, but the database needs to look up results until it's at the right offset anyway). I'm not sure how the database cache picks this up.
It would probably help to use prepared queries (see the PREPARE statement).
2) Selecting all the id's first and keep them in a POST, GET or session variable. In each group of results you can do a (fast) select on those indices using something like SELECT * FROM table WHERE table_id IN (...). This also has a drawback when you have a lot of results; the data passed between page loads can get large, but it will probably be lighter on the database.
I'm not sure which is the best way either, and there may be other ways. I'm sure some people here will have their ideas about this ;)
Regards,
-- Alban Hertroys MAG Productions
T: +31(0)53 4346874 F: +31(0)53 4346876 E: alban@xxxxxxxxxxxxxxxxx W: http://www.magproductions.nl
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
