(not) freeing cursors - Mailing list pgsql-general

From Alex Howansky
Subject (not) freeing cursors
Date
Msg-id Pine.LNX.4.30.0101031324200.32196-100000@net-srv-0001.bvrd.com
Whole thread Raw
Responses Re: (not) freeing cursors
List pgsql-general
I've got a table with about 150,000 rows, and I'm typically going to be viewing
it 4 rows at a time, based on a fairly complex multi-table join. The front end
application is web (PHP) based, and allows the user to do the "View Next 4" and
"View Previous 4" thing. Currently, the PHP code maintains the offset and
issues queries like this:

select * from table where ... offset N limit 4

My server is getting totally flogged though, and I'd like to experiment with
cursors to try and alleviate some of the load. However, since my front end is
web based, I have no control over when a user just closes the browser window
and goes away. As a result, I have no way of determining when to close the
cursor and commit the transaction. I.e., I can't declare the cursor when the
user browses the first page, and then close it when the user browses the last
page -- because I never know which page is the last one they're going to view.
It seems that I'd need to do this:

begin
declare thing cursor for select ...
move N from thing
fetch 4 from thing
close thing
commit

for every single page that gets viewed. That seems pretty silly though. So,
three questions:

1) Would this method be better than doing the "select ... offset N limit 4" for
each page?

2) What happens if a cursor is never closed / transaction is never commited?

3) Am I missing something obvious? Is there a better way to do this?

Thanks!

--
Alex Howansky
Wankwood Associates
http://www.wankwood.com/


pgsql-general by date:

Previous
From: "Eric Mueller"
Date:
Subject: RE: RE: RE: Re: MySQL and PostgreSQL speed compare
Next
From: "Thomas T. Thai"
Date:
Subject: user privileges for particular table