pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Using Cursors in PHP
7+ messages / 5 participants
[nested] [flat]

* Using Cursors in PHP
@ 2003-08-06 12:59 Andreas Schmitz <a.schmitz@cityweb.de>
  2003-08-06 15:51 ` Re: Using Cursors in PHP scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Andreas Schmitz @ 2003-08-06 12:59 UTC (permalink / raw)
  To: pgsql-php


Hello *,

I am just wondering if it is possible to use cursors within PHP. I need to 
page query result from a big table and I am looking for a solution to avoid 
scanning the complete table using limit and offset.

any ideas ?

best regards

-andreas






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Using Cursors in PHP
  2003-08-06 12:59 Using Cursors in PHP Andreas Schmitz <a.schmitz@cityweb.de>
@ 2003-08-06 15:51 ` scott.marlowe <scott.marlowe@ihs.com>
  2003-08-08 08:01   ` Re: Using Cursors in PHP Markus Bertheau <twanger@bluetwanger.de>
  0 siblings, 1 reply; 7+ messages in thread

From: scott.marlowe @ 2003-08-06 15:51 UTC (permalink / raw)
  To: Andreas Schmitz <a.schmitz@cityweb.de>; +Cc: pgsql-php

On Wed, 6 Aug 2003, Andreas Schmitz wrote:

> 
> Hello *,
> 
> I am just wondering if it is possible to use cursors within PHP. I need to 
> page query result from a big table and I am looking for a solution to avoid 
> scanning the complete table using limit and offset.

Yes, you can use cursors, no you can't expect them to survive from one 
page to the next.

Luckily, Postgresql is smart enough to use indexes when you use offset 
limit, but, of course, it must internally create the result set up to and 
including the last row returned by the limit / offset.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Using Cursors in PHP
  2003-08-06 12:59 Using Cursors in PHP Andreas Schmitz <a.schmitz@cityweb.de>
  2003-08-06 15:51 ` Re: Using Cursors in PHP scott.marlowe <scott.marlowe@ihs.com>
@ 2003-08-08 08:01   ` Markus Bertheau <twanger@bluetwanger.de>
  2003-08-08 13:22     ` Re: Using Cursors in PHP scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Markus Bertheau @ 2003-08-08 08:01 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Andreas Schmitz <a.schmitz@cityweb.de>; pgsql-php

В Срд, 06.08.2003, в 17:51, scott.marlowe пишет:
> On Wed, 6 Aug 2003, Andreas Schmitz wrote:
> 
> > 
> > Hello *,
> > 
> > I am just wondering if it is possible to use cursors within PHP. I need to 
> > page query result from a big table and I am looking for a solution to avoid 
> > scanning the complete table using limit and offset.
> 
> Yes, you can use cursors, no you can't expect them to survive from one 
> page to the next.

What about the new feature in cursors "WITH HOLD"? Don't they survive?

-- 
Markus Bertheau <twanger@bluetwanger.de>

Attachments:

  signature.asc (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Using Cursors in PHP
  2003-08-06 12:59 Using Cursors in PHP Andreas Schmitz <a.schmitz@cityweb.de>
  2003-08-06 15:51 ` Re: Using Cursors in PHP scott.marlowe <scott.marlowe@ihs.com>
  2003-08-08 08:01   ` Re: Using Cursors in PHP Markus Bertheau <twanger@bluetwanger.de>
@ 2003-08-08 13:22     ` scott.marlowe <scott.marlowe@ihs.com>
  2003-08-08 13:42       ` Re: Using Cursors in PHP Rod Taylor <rbt@rbt.ca>
  2003-08-08 17:34       ` Re: Using Cursors in PHP Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 2 replies; 7+ messages in thread

From: scott.marlowe @ 2003-08-08 13:22 UTC (permalink / raw)
  To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: Andreas Schmitz <a.schmitz@cityweb.de>; pgsql-php

On 8 Aug 2003, Markus Bertheau wrote:

> В Срд, 06.08.2003, в 17:51, scott.marlowe пишет:
> > On Wed, 6 Aug 2003, Andreas Schmitz wrote:
> > 
> > > 
> > > Hello *,
> > > 
> > > I am just wondering if it is possible to use cursors within PHP. I need to 
> > > page query result from a big table and I am looking for a solution to avoid 
> > > scanning the complete table using limit and offset.
> > 
> > Yes, you can use cursors, no you can't expect them to survive from one 
> > page to the next.
> 
> What about the new feature in cursors "WITH HOLD"? Don't they survive?

I don't think so.  Basically, PHP is supposed to reset the connection if 
it's persistant, and if it isn't persistant, then it's always a new 
connection, which means a cursor shouldn't survive. 

But I'm NOT familiar with "WITH HOLD" right now, so someone else who knows 
that better may have a more definitive answer.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Using Cursors in PHP
  2003-08-06 12:59 Using Cursors in PHP Andreas Schmitz <a.schmitz@cityweb.de>
  2003-08-06 15:51 ` Re: Using Cursors in PHP scott.marlowe <scott.marlowe@ihs.com>
  2003-08-08 08:01   ` Re: Using Cursors in PHP Markus Bertheau <twanger@bluetwanger.de>
  2003-08-08 13:22     ` Re: Using Cursors in PHP scott.marlowe <scott.marlowe@ihs.com>
@ 2003-08-08 13:42       ` Rod Taylor <rbt@rbt.ca>
  2003-08-08 13:33         ` Re: Using Cursors in PHP scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Rod Taylor @ 2003-08-08 13:42 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; Andreas Schmitz <a.schmitz@cityweb.de>; pgsql-php

> I don't think so.  Basically, PHP is supposed to reset the connection if 
> it's persistant, and if it isn't persistant, then it's always a new 
> connection, which means a cursor shouldn't survive. 
> 
> But I'm NOT familiar with "WITH HOLD" right now, so someone else who knows 
> that better may have a more definitive answer.

Yup.. This is where you setup a little Java to deal with persistent
database elements like this and hook into it via PHP.  It's a little
nasty at the moment (though functional) but is supposedly greatly
improved in PHP 5.

http://ca2.php.net/manual/en/ref.java.php

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Using Cursors in PHP
  2003-08-06 12:59 Using Cursors in PHP Andreas Schmitz <a.schmitz@cityweb.de>
  2003-08-06 15:51 ` Re: Using Cursors in PHP scott.marlowe <scott.marlowe@ihs.com>
  2003-08-08 08:01   ` Re: Using Cursors in PHP Markus Bertheau <twanger@bluetwanger.de>
  2003-08-08 13:22     ` Re: Using Cursors in PHP scott.marlowe <scott.marlowe@ihs.com>
  2003-08-08 13:42       ` Re: Using Cursors in PHP Rod Taylor <rbt@rbt.ca>
@ 2003-08-08 13:33         ` scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 0 replies; 7+ messages in thread

From: scott.marlowe @ 2003-08-08 13:33 UTC (permalink / raw)
  To: Rod Taylor <rbt@rbt.ca>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; Andreas Schmitz <a.schmitz@cityweb.de>; pgsql-php

On Fri, 8 Aug 2003, Rod Taylor wrote:

> > I don't think so.  Basically, PHP is supposed to reset the connection if 
> > it's persistant, and if it isn't persistant, then it's always a new 
> > connection, which means a cursor shouldn't survive. 
> > 
> > But I'm NOT familiar with "WITH HOLD" right now, so someone else who knows 
> > that better may have a more definitive answer.
> 
> Yup.. This is where you setup a little Java to deal with persistent
> database elements like this and hook into it via PHP.  It's a little
> nasty at the moment (though functional) but is supposedly greatly
> improved in PHP 5.
> 
> http://ca2.php.net/manual/en/ref.java.php

Java providing connection pooling for PHP.  Sweet!




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Using Cursors in PHP
  2003-08-06 12:59 Using Cursors in PHP Andreas Schmitz <a.schmitz@cityweb.de>
  2003-08-06 15:51 ` Re: Using Cursors in PHP scott.marlowe <scott.marlowe@ihs.com>
  2003-08-08 08:01   ` Re: Using Cursors in PHP Markus Bertheau <twanger@bluetwanger.de>
  2003-08-08 13:22     ` Re: Using Cursors in PHP scott.marlowe <scott.marlowe@ihs.com>
@ 2003-08-08 17:34       ` Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 0 replies; 7+ messages in thread

From: Bruce Momjian @ 2003-08-08 17:34 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; Andreas Schmitz <a.schmitz@cityweb.de>; pgsql-php

scott.marlowe wrote:
> On 8 Aug 2003, Markus Bertheau wrote:
> 
> > ?? ??????, 06.08.2003, ?? 17:51, scott.marlowe ??????????:
> > > On Wed, 6 Aug 2003, Andreas Schmitz wrote:
> > > 
> > > > 
> > > > Hello *,
> > > > 
> > > > I am just wondering if it is possible to use cursors within PHP. I need to 
> > > > page query result from a big table and I am looking for a solution to avoid 
> > > > scanning the complete table using limit and offset.
> > > 
> > > Yes, you can use cursors, no you can't expect them to survive from one 
> > > page to the next.
> > 
> > What about the new feature in cursors "WITH HOLD"? Don't they survive?
> 
> I don't think so.  Basically, PHP is supposed to reset the connection if 
> it's persistant, and if it isn't persistant, then it's always a new 
> connection, which means a cursor shouldn't survive. 

Actually, that brings up a problem --- our RESET ALL doesn't close
cursors that are kept open using WITH HOLD.

However, you are right that you can't be sure you will get the same
backend when you connect via a new page, so WITH HOLD doesn't help here.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2003-08-08 17:34 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-06 12:59 Using Cursors in PHP Andreas Schmitz <a.schmitz@cityweb.de>
2003-08-06 15:51 ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-08 08:01   ` Markus Bertheau <twanger@bluetwanger.de>
2003-08-08 13:22     ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-08 13:42       ` Rod Taylor <rbt@rbt.ca>
2003-08-08 13:33         ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-08 17:34       ` Bruce Momjian <pgman@candle.pha.pa.us>

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