Обсуждение: Practical Cursors

Hello,

We are just about to wrap up our book, Pratical PostgreSQL (the one that
used to be The Elephant never forgets) and we have a question.

There is an obvious benefit to the use of cursors within a persistent
environment. In other words, if my connection to the database is live, I
can increase my query and display efficiency through the use of a cursor.

However, this seems to be useless within a web based environment. If we
have a live connection through a C++ application, we can perform a
transaction and interact within the results.

However, using something like PHP will not allow this because HTTP is
stateless and PostgreSQL will not know from one transaction to the next
that the results of the connection are related.

Is this truly the case, or is there a way for PostgreSQL to remember the
connection identifier so that the next time a PHP connection is made with
the same identifier a transaction can be completed?

Sincerely,

Joshua Drake



--
Practical PostgreSQL:
 http://stage.linuxports.com/projects/postgres/book1.htm
by way of:
 pgsql-general@commandprompt.com

> However, using something like PHP will not allow this because HTTP is
> stateless and PostgreSQL will not know from one transaction to the next
> that the results of the connection are related.
>
> Is this truly the case, or is there a way for PostgreSQL to remember the
> connection identifier so that the next time a PHP connection is made with
> the same identifier a transaction can be completed?
>
> Sincerely,
>
> Joshua Drake
>

I have actually been thinking recently about just this question. I looked
through the source for the PHP pgsql extension and it is clear that no
cursor is used. There is, however, the ability to open a persistent
connection to PostgreSQL from PHP. I think it would be possible to create a
persistent resource identifier to a cursor in a similar manner, and "reuse"
the cursor across multiple HTTP requests.

The downside I can see is that PHP would have no way to know when it could
garbage-collect the allocated resource. So while it could be done, what
would happen when the user closes their browser with your cursor still open?

-- Joe

> The downside I can see is that PHP would have no way to know when it could
> garbage-collect the allocated resource. So while it could be done, what
> would happen when the user closes their browser with your cursor still open?

Well, I thought of two things. One PostgreSQL could have some identifier
that was set by PHP, similar to a session ID that would have a time out.
Like a cookie timeout. If PostgreSQL did not receive a CLOSE or COMMIT
within that time limit, it would drop the transaction.

On the other hand, you could have it track the transaction so that the
next time I come back, if my identifier it matched to my session you could
code an application that will inform you of the transactions that you that
are not completed. Alas, that would be cool but would take a lot of code
on the PHP end.

The second thought I had was a small java applet that kept a live
connection to PostgreSQL and proxied certain things but that seems
extreme.

J

>
> -- Joe
>

--
--
by way of pgsql-general@commandprompt.com

On Mon, 17 Sep 2001, Command Prompt, Inc. wrote:

> However, using something like PHP will not allow this because HTTP is
> stateless and PostgreSQL will not know from one transaction to the next
> that the results of the connection are related.
>
> Is this truly the case, or is there a way for PostgreSQL to remember the
> connection identifier so that the next time a PHP connection is made with
> the same identifier a transaction can be completed?
Not currently. Cursor right now is a per-backend thing. A lot of changes
are needed to make cursors global. (particularly transaction-awareness)

-alex

On Mon, 17 Sep 2001, Command Prompt, Inc. wrote:

> Is this truly the case, or is there a way for PostgreSQL to remember the
> connection identifier so that the next time a PHP connection is made with
> the same identifier a transaction can be completed?

Anything is possible - you could write a server process that associates
cursors with session IDs and then have your PHP app make requests through
the server.  Would it be a good idea?  Probably not.  Would it qualify as
"practical postgres" usage?  Definitely not!  I'd call it an impractical
idea that might be possible through a lot of hard work.

-sam

(sorry to reply to a week-old message.  need to keep up with this list more!)

On Monday 17 September 2001 17:04, you wrote:

> There is an obvious benefit to the use of cursors within a persistent
> environment. In other words, if my connection to the database is live, I
> can increase my query and display efficiency through the use of a cursor.
>
> However, this seems to be useless within a web based environment. If we
> have a live connection through a C++ application, we can perform a
> transaction and interact within the results.

Yep.  That seems to be a disadvantage with ALL database systems & HTTP based
apps.

I once wrote a MySQL app (I know, but that's what the company used) to do a
fairly complicated search on a huge database full of domain names.  The query
was time consuming (10-30 seconds) so it obviously could not be performed for
every prev/next page request.

My first approach was to have the PHP script write the entire data resultset
to a fixed-length file, which could be easily accessed for each request to
the point where the user was in the file.  Only problem there was when the
result set was large, initial query time was significantly longer.  And that
happened a lot.

I then wrote a daemon in C to do the work and store the results in RAM.  The
PHP script connected to the daemon via a socket, and passed a request ID and
the numbers of the records it wanted.  Sure, it was convoluted, but I
actually got the speed up to where I was fairly happy with it.

If there's a better solution than that, I'm not aware of it.

But like someone else mentioned, it's not quite "practical" database usage.

--
Like to travel?                        http://TravTalk.org
Micah Yoder Internet Development       http://yoderdev.com

>>>>> "Micah" == Micah Yoder <yodermk@home.com> writes:

Micah> I then wrote a daemon in C to do the work and store the results
Micah> in RAM.  The PHP script connected to the daemon via a socket,
Micah> and passed a request ID and the numbers of the records it
Micah> wanted.  Sure, it was convoluted, but I actually got the speed
Micah> up to where I was fairly happy with it.

Micah> If there's a better solution than that, I'm not aware of it.

What you need is a "middleware" (heh) solution... I've done a number
of magazine columns using a Perl-based mini-web-server (search google
for "site:stonehenge.com HTTP::Daemon").  You could set up your
frontware begin-search CGI script to fire off a daemon, and tell it to
do the hard work, using a session ID to keep track of subsequent hits,
and let the front CGI request proxy through using HTTP to talk to the
daemon (very easy).  Or, you could redirect the client to the
mini-daemon directly.  I've done the latter a couple of times, and
it's pretty practical up to light-commercial-volume hits.

And before you scream "Perl is slow", I have a working Perl webserver
based on HTTP::Daemon that pre-forks just like Apache (taking current
load into consideration), handles streaming proxy connections
(including SSL proxy) and local file delivery, and benchmarks at
*half* the speed of Apache in both proxy and local file mode.  That's
much better than I initially expected, and completely satisfactory for
most applications.  And it's only 300 lines of code. :)

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

Micah Yoder wrote:
> (sorry to reply to a week-old message.  need to keep up with this list more!)
>
> On Monday 17 September 2001 17:04, you wrote:
>
> > There is an obvious benefit to the use of cursors within a persistent
> > environment. In other words, if my connection to the database is live, I
> > can increase my query and display efficiency through the use of a cursor.
> >
> > However, this seems to be useless within a web based environment. If we
> > have a live connection through a C++ application, we can perform a
> > transaction and interact within the results.
>
> Yep.  That seems to be a disadvantage with ALL database systems & HTTP based
> apps.
>
> I once wrote a MySQL app (I know, but that's what the company used) to do a
> fairly complicated search on a huge database full of domain names.  The query
> was time consuming (10-30 seconds) so it obviously could not be performed for
> every prev/next page request.
>
> My first approach was to have the PHP script write the entire data resultset
> to a fixed-length file, which could be easily accessed for each request to
> the point where the user was in the file.  Only problem there was when the
> result set was large, initial query time was significantly longer.  And that
> happened a lot.
>
> I then wrote a daemon in C to do the work and store the results in RAM.  The
> PHP script connected to the daemon via a socket, and passed a request ID and
> the numbers of the records it wanted.  Sure, it was convoluted, but I
> actually got the speed up to where I was fairly happy with it.
>
> If there's a better solution than that, I'm not aware of it.
>
> But like someone else mentioned, it's not quite "practical" database usage.

    Since  search  engines and data warehousing tend to have huge
    databases with sometimes complicated,  long  running  queries
    that  produce  empty to huge result sets, it's a quite common
    problem. Thus, I would consider any solution  that  leads  to
    success at first "practical".

    PHP together with cursors might be an alternate solution. You
    open a cursor for the entire result set. You have a  function
    that  fetches  the  next n rows from the cursor and generates
    the resulting html output in a file. It returns true if  more
    rows  have  been  found.  You  call it once and if it returns
    false display "No match found" or so. If it returns true, you
    call  it  again  to create a cache file for the second result
    page, and know if there  will  be  one  (telling  you  if  to
    provide a NEXT button). You register a shutdown function that
    will call the cache file generator another m times.  Now  you
    display  the  first  cache file, leave the DB connection with
    the open transaction and cursor where they are and exit.

    The user will already see the first result  page  while  your
    server  is  still  working.  After  calling  the  cache  file
    generator function m times, the shutdown function closes  the
    cursor,   terminates   the  transaction  and  closes  the  DB
    connection.

    I think 95% of users will not hit NEXT  more  than  10  times
    before  refining  their  search, so that should be enough. If
    one really does, well, than you'd  have  to  run  the  entire
    query  again  and this time create either more cache files or
    all of them.

    Now you need some sort of vacuum cleaner for the cache  files
    and are done.

    The  drawback  for  this solution is, that you don't know how
    many pages there will be in total when you display the  first
    one.   But   the   benefits   are  that  it  fit's  into  the
    connectionless HTTP nature, has a small  resource  footprint,
    provides  first  results  early  and  does  not  require open
    transactions over user interaction.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

On Tue, Sep 25, 2001 at 12:06:48AM -0400, Micah Yoder wrote:
> (sorry to reply to a week-old message.  need to keep up with this list more!)

Ditto, but more so.

> I then wrote a daemon in C to do the work and store the results in RAM.  The
> PHP script connected to the daemon via a socket, and passed a request ID and
> the numbers of the records it wanted.  Sure, it was convoluted, but I
> actually got the speed up to where I was fairly happy with it.
>
> If there's a better solution than that, I'm not aware of it.

A technique I've used with some success is to select the primary keys
from the rows you're interested in, and only have to memorize a list of
integers.  Then for each page, select the rows "WHERE pkey IN (...)".
It's sort of a middle ground as far as tradeoffs go.  You don't have to
store a huge amount of data in RAM or temporary files, but you still
have to do the work up front.

The problem I have with persistent per-session connections is that you
end up having basically the same
(per-transaction-overhead * simultaneous-transactions), and you add
(per-connection-overhead * simultaneous-open-sessions) on top.
There are certainly situations where you can do better one way or the
other.. figuring out how to best tune the per-session case scares me.
--
Christopher Masto

CB461C61 8AFC E3A8 7CE5 9023 B35D  C26A D849 1F6E CB46 1C61