Rick Schumeyer wrote:
> 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@magproductions.nl
W: http://www.magproductions.nl