Thread: (not) freeing cursors

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/

Alex Howansky <alex@wankwood.com> writes:
> 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?

No.  The actual query work will be the same, plus you have some (small)
bookkeeping overhead to define the cursor.  You cannot come out ahead.
In fact you could come out behind, for small N, since the straight
SELECT will get optimized with the knowledge that only a few tuples need
be retrieved, whereas the cursor will not be.

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

Nothing catastrophic, but it's best not to hold a transaction open
longer than you have to (long-running transactions cause various sorts
of inefficiencies).

I'm not clear on why this is a problem.  If you are able to use a cursor
across queries at all, then you must have *some* persistent state that
is holding the connection to the backend that you sent the cursor
declaration to.  It doesn't seem that hard to add a little more info to
keep track of the fact that you have an open transaction on that
connection.  Maybe keep track of the last time the cursor was used,
and drop it after N minutes of inactivity.  Worst case is that you have
to create the cursor again if you drop it too soon...

            regards, tom lane

At 02:28 PM 1/3/01 -0600, Alex Howansky wrote:
>
>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

So far I'm doing the whole select and throwing away the unneeded results.
If you use offset and limit the database is the one throwing away the
results. In my case I want to know the number of records in a select so I
let the application throw away the results.

I decided on this approach rather than caching the results somewhere or
leaving the transaction open because of the difficulties on deciding when
to free up resources. This is because I was trying to cater for a scenario
where there could be many users at once.

For example if the user starts using more than one window and looks at lots
of results what do you do? Worse, say your resource timeouts are 10
minutes, what happens if you get 1000 users over a 10 minute interval?

If you are certain there will only be a small controlled number of users at
once, the caching/"reuse relevant transaction" approach can work.

Unfortunately there is no way to "continue" a transaction in a different
connection (hard to ensure that the user always hits the correct webapp
instance and thus persistent db connection). And even if you could (e.g.
CONTINUE transaction 1452354), if the user opens up two browser windows
during a session things could get messy! Actually this could be feasible in
a select only environment (search engine?) but this is quite a specialised
case.

About your server being flogged:
If memory is an issue you could try turning off buffering. In my setup, the
database does buffer the results somewhat, then the Perl DBI-DBD also
stores the full results which my webapp then retrieves row by row. However
it is possible to configure things so that the web app retrieves things row
by row more or less straight from the database. However this means you will
not be able to do other queries in that db connection until you are
finished with that query (this is sometimes necessary if you are doing
recursive stuff).

If not that many rows are being returned in your queries then there may be
a chance that things could be better optimized - e.g. more indexing, query
rewrites, or table/database reshaping. If like 50,000 rows are being
returned then well uh I dunno how to make it faster :(. Coz 150,000 rows
isn't that big, unless that's your result set :). I can get about 40 to
100+ hits per second with simple selects from a 500,000 row table,
throughput is about 1-2MB/sec (slower than disk I/O even when cached in mem
dunno why :( ).

Maybe some experts here can help with your queries and joins. How about you
give an example of your db and the complex query? As a start maybe the
results from the "EXPLAIN" of your query could be helpful.

Good luck,

Cheerio,
Link.

Hi,

Say I have two applications A and B communicating via a postgresql
database. A sends data for B to work on.

How can I get B to _wait_ for a signal from A before proceeding?

Right now it looks like B has to keep polling regularly. So in order for
things to occur in a timely fashion the polling interval has to be
ridiculously short. This means using more CPU than I would like.

Is there a postgresql equivalent of a unix select/accept?

Basically LISTEN doesn't wait. It'll really be nice to have one which does
- lots of cool stuff can be done.

For example, I could have a webapp write trigger another app running as
root- the webapp writes high level stuff to the database, and a run-as-root
app does stuff based on it, this seems to be a more secure. Or I could have
a small app watch some logs for some event, send the data to a database.
Then another small app waits for a trigger and then uses some of that data
to do something else. Or we could even have a database level trigger, so
when a certain criteria is met, the waiting application is triggered.
Basically you now can have apps use the database for instant messaging :).

Is this sort of thing possible with postgresql or even other RDBMs?

Thanks,
Link.

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> Basically you now can have apps use the database for instant messaging :).

My former company was doing that since Postgres 6.4 or so.

> Right now it looks like B has to keep polling regularly.

No, it just has to use select() to wait on the postgres connection
(plus any other input files it wants to pay attention to).

> Basically LISTEN doesn't wait.

LISTEN has nothing to do with waiting.  It merely informs the backend
of your interest in subsequently receiving notices of a particular type.
Perhaps you should think of it as like signal(2).

See
http://www.postgresql.org/devel-corner/docs/postgres/libpq-notify.htm
but in brief the idea is:

1. The outer event loop of your application uses select() to wait on
the PQsocket() fd as well as any other interesting fds.

2. When you see input ready on the PQsocket() fd, call PQconsumeInput(),
then check PQnotifies().

3. Also check PQnotifies() after any PQexec(), to see if notify messages
came in during the query.

Keep in mind also that notifications are only delivered when you are
not within a transaction block (BEGIN/END).

            regards, tom lane

At 10:47 PM 04-01-2001 -0500, you wrote:
>Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
>> Basically LISTEN doesn't wait.
>
>LISTEN has nothing to do with waiting.  It merely informs the backend
>of your interest in subsequently receiving notices of a particular type.
>Perhaps you should think of it as like signal(2).

My fault - poor phrasing.

>See
>http://www.postgresql.org/devel-corner/docs/postgres/libpq-notify.htm
>but in brief the idea is:
>
>1. The outer event loop of your application uses select() to wait on
>the PQsocket() fd as well as any other interesting fds.
>
>2. When you see input ready on the PQsocket() fd, call PQconsumeInput(),
>then check PQnotifies().
>
>3. Also check PQnotifies() after any PQexec(), to see if notify messages
>came in during the query.
>
>Keep in mind also that notifications are only delivered when you are
>not within a transaction block (BEGIN/END).

Uhoh, since I'm using perl does that mean I have to patch DBI and Pg::DBD?
And worse - turn off transactions.

Would it be viable idea to add a WAIT command/statement for higher level
access (e.g. "SQL" level access to this feature)?

e.g.
WAIT "blahblahblah";
 where it will wait until a NOTIFY "blahblahblah".

That'll be real nice to have :). If I start messing about with PQstuff I'll
probably screw up somewhere.

Thanks anyway,
Link.

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> Uhoh, since I'm using perl does that mean I have to patch DBI and Pg::DBD?

Couldn't say.  I didn't have much trouble setting up a Tcl application
to work smoothly with NOTIFY events, but I've never tried such a thing
in Perl.  Any Perl gurus out there who can pontificate about how to
set up an event loop in Perl?

> Would it be viable idea to add a WAIT command/statement for higher level
> access (e.g. "SQL" level access to this feature)?

Strikes me as pretty useless.  If you were to PQexec("WAIT foo") then
your application would be totally locked up until and unless a foo
signal gets delivered.  You won't be satisfied with that for long.

            regards, tom lane

At 11:27 PM 04-01-2001 -0500, Tom Lane wrote:
>Lincoln Yeoh <lyeoh@pop.jaring.my> writes:

>> Would it be viable idea to add a WAIT command/statement for higher level
>> access (e.g. "SQL" level access to this feature)?
>
>Strikes me as pretty useless.  If you were to PQexec("WAIT foo") then
>your application would be totally locked up until and unless a foo
>signal gets delivered.  You won't be satisfied with that for long.

I'll actually be very happy, that's exactly what I want, and I can't see
why it would be a bad thing. I personally like the idea of programs doing
specific tasks, if there's nothing to do, they don't do anything else. That
way there is very little that can go wrong.

Cheerio,
Link.

On Thu, 4 Jan 2001, Tom Lane wrote:

> Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> > Uhoh, since I'm using perl does that mean I have to patch DBI and Pg::DBD?
>
> Couldn't say.  I didn't have much trouble setting up a Tcl application
> to work smoothly with NOTIFY events, but I've never tried such a thing
> in Perl.  Any Perl gurus out there who can pontificate about how to
> set up an event loop in Perl?
Very easy to do with old Pg interface (you just register a notify handler,
like in libpq), but currently DBD::Pg is not notify-aware. I wanted to fix
that (should be simple), but I think it'll have to wait a month or two
till I get a spare minute...

-alex