postgres session termination

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

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:

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 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

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

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.