pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Keary Suska <hierophant@pcisys.net>
To: Postgres-PHP <pgsql-php@postgresql.org>
Subject: Re: using CURSOR with PHP
Date: Sat, 18 May 2002 12:32:35 -0600
Message-ID: <B90BFA62.E93F%hierophant@pcisys.net> (raw)
In-Reply-To: <1021723791.30041.241.camel@kant.mcmillan.net.nz>

on 5/18/02 6:09 AM, andrew@catalyst.net.nz purportedly said:

>> I used persistent connections, however, I got lots of <defunct>
>> processes. 
>> If the CURSOR is closed when the script has finished (pg_connect),
>> it makes no sense (for me) using it, because I can not move within
>> the results. 
> 
> Right, so your problem is you're trying to use CURSOR, which you are
> leaving open  on the off-chance your visitor will returns and wants to
> see the 'next record' or something.
> 
> I think that really you just should bite the bullet and use the better
> syntax on SELECT.  You may have a little more programming work to do,
> but it has little impact on performance as far as I can see.
> 
> PostgreSQL will optimise queries differently when they have LIMIT
> clauses too.

I'm afraid Andrew is correct here--with persistent connections, you have no
control, over when the connection is closed, and what connection you are
using at any one point. Thus you would still have to keep track of where the
cursor is, and furthermore you would have to check for the existence of the
cursor on every script invocation, which I don't believe there is a command
for, so you would have to try to use the cursor and then capture any error.
In the case of an error, you would need to re-define the cursor and move to
the current point according to the stored cursor location info.

Depending on your program logic, this could be more difficult than using
LIMIT. Using LIMIT has the added benefit of avoiding the overhead of many
unused cursors, which can build up quickly as you also have little or no
control over the cursors since cursors are tied to the connection.

The drawback to using LIMIT is that you are executing the query on every
call, and Postgres cannot optimize on a LIMIT as was mentioned, because the
entire query has to be collected before the LIMIT can be applied. However,
IIRC, Postgres does query result caching, so subsequent calls on the same
query will tend to be faster, providing there is enough memory allocated to
support it.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"




view thread (23+ messages)  latest in thread

Message-ID: <B90BFA62.E93F%hierophant@pcisys.net>
Permalink:  ../B90BFA62.E93F%25hierophant@pcisys.net/
Also on:    postgresql.org/message-id/B90BFA62.E93F%hierophant@pcisys.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: hierophant@pcisys.net
  Subject: Re: using CURSOR with PHP
  In-Reply-To: <B90BFA62.E93F%hierophant@pcisys.net>
  References: <1021723791.30041.241.camel@kant.mcmillan.net.nz> <B90BFA62.E93F%hierophant@pcisys.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox