Thread: postgres session termination
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?
Of course, if this is a really bad way to do this, I’m willing to
learn a better way!
Thanks!
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
On Sun, 2005-01-30 at 21:24 -0500, 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? in normal php context, the generation of each web page should be considered a separate database session. there is no garantee that the 'next page' request from the user gets the same database connection, even if you use connection pooling. you can use LIMIT and OFFSET to do what you want. if your rows are ordered by a unique key, and you only have to browse forward, you can do: SELECT * from table where key > ? where the '?' is last value retrieved. gnari
IF you use pg_pconnect(), never close your script, and this page always shows the same data to all users, then the temp table would work, although it is not necessarily quicker than selecting ALL the rows (i.e., don't bother with a temp table at all; just run the whole select every time.) The usual method for handling this is the LIMIT and OFFSET clauses in a SELECT. For example, this would get the results to put on the fifth page: SELECT * FROM products ORDER BY stock_number DESC LIMIT 10 OFFSET 40; Paul Tillotson 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? > > Of course, if this is a really bad way to do this, I’m willing to > > learn a better way! > > Thanks! >
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? > > Of course, if this is a really bad way to do this, I'm willing to > learn a better way! Unfortunately, you can't use a temporary table - each webpage request will generate a new connection. If you are using php's permanent connections that still doesn't help you since you'll have several connections and can't guarantee which one is reused. So - the simplest way is to use a permanent table search_results_cache and store your results in there with some sort of session_code. Then, you can step through that and delete the results when the session is over. You might store just the IDs required to reconstitute the results, depending on how long it takes to calculate these. Check the mailing list archives for details - there was a length discussion recently. -- Richard Huxton Archonet Ltd
On Jan 30, 2005, at 9:24 PM, Rick Schumeyer wrote: > 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? > > > > Of course, if this is a really bad way to do this, I’m willing to > > learn a better way! I think there are much better ways to do this. If the result set is large, the user could be waiting a very long time. Two possibilities are (1) use a cursor or (2) use limit and offset in your select statement grab only the rows you need to display. I think phpPgAdmin (http://phppgadmin.sourceforge.net/) uses option 2. You could download the source and see how they implement the table browser. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
John DeSoi wrote: > I think there are much better ways to do this. If the result set is > large, the user could be waiting a very long time. Two possibilities are > (1) use a cursor or (2) use limit and offset in your select statement > grab only the rows you need to display. Someone correct me if I'm wrong, but I don't think PHP supports cursors (Maybe PHP 5?). Otherwise, that would have been a neat solution indeed. -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: alban@magproductions.nl W: http://www.magproductions.nl
On Mon, 2005-01-31 at 09:28, Alban Hertroys wrote: > John DeSoi wrote: > > I think there are much better ways to do this. If the result set is > > large, the user could be waiting a very long time. Two possibilities are > > (1) use a cursor or (2) use limit and offset in your select statement > > grab only the rows you need to display. > > Someone correct me if I'm wrong, but I don't think PHP supports cursors > (Maybe PHP 5?). > > Otherwise, that would have been a neat solution indeed. PHP supports postgresql cursors, and has since php was able to connect to postgresql.
On Mon, 2005-01-31 at 15:38 -0600, Scott Marlowe wrote: > On Mon, 2005-01-31 at 09:28, Alban Hertroys wrote: > > John DeSoi wrote: > > > I think there are much better ways to do this. If the result set is > > > large, the user could be waiting a very long time. Two possibilities are > > > (1) use a cursor or (2) use limit and offset in your select statement > > > grab only the rows you need to display. > > > > Someone correct me if I'm wrong, but I don't think PHP supports cursors > > (Maybe PHP 5?). > > > > Otherwise, that would have been a neat solution indeed. > > PHP supports postgresql cursors, and has since php was able to connect > to postgresql. well, my impression was that the OP wanted to divide result sets between web pages, so cursors would not help anyways,as they do not survive their session. gnari
On Mon, 2005-01-31 at 16:08, Ragnar Hafstað wrote: > On Mon, 2005-01-31 at 15:38 -0600, Scott Marlowe wrote: > > On Mon, 2005-01-31 at 09:28, Alban Hertroys wrote: > > > John DeSoi wrote: > > > > I think there are much better ways to do this. If the result set is > > > > large, the user could be waiting a very long time. Two possibilities are > > > > (1) use a cursor or (2) use limit and offset in your select statement > > > > grab only the rows you need to display. > > > > > > Someone correct me if I'm wrong, but I don't think PHP supports cursors > > > (Maybe PHP 5?). > > > > > > Otherwise, that would have been a neat solution indeed. > > > > PHP supports postgresql cursors, and has since php was able to connect > > to postgresql. > > well, my impression was that the OP wanted to divide result sets > between web pages, so cursors would not help anyways,as they do > not survive their session. Correct. However, that isn't a limitation in PHP so much as in the stateless nature of http. But PHP can certainly instantiate and use a cursor within a single page quite well. Based on what little was in the message I replied to, that seemed to be the only point made. I'm sure there was more to the question than what was left in the post I answered.