Hello *,

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

any ideas ?

best regards

-andreas




---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if you=
r
joining column's datatypes do not match

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.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo [at] postgresql.org so that your
message can get through to the mailing list cleanly

--=-SA/wsgPOaBxZd54wAnFm
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

=D0=92 =D0=A1=D1=80=D0=B4, 06.08.2003, =D0=B2 17:51, scott.marlowe =D0=BF=
=D0=B8=D1=88=D0=B5=D1=82:
> 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 a=
void
> > 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 [at] bluetwanger.de>

--=-SA/wsgPOaBxZd54wAnFm
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: =?koi8-r?Q?ü=D4=C1?= =?koi8-r?Q?_=DE=C1=D3=D4=D8?=
=?koi8-r?Q?_=D3=CF=CF=C2=DD=C5=CE=C9=D1?=
=?koi8-r?Q?_=D0=CF=C4=D0=C9=D3=C1=CE=C1?=
=?koi8-r?Q?_=C3=C9=C6=D2=CF=D7=CF=CA?=
=?koi8-r?Q?_=D0=CF=C4=D0=C9=D3=D8=C0?=

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQA/M1jkZ+yN9zLmHSMRAj0cAJ4xhJbi/WDGA2D55srv3Oy69H/PzwCe Nfdo
egG0RqegyAp3YoOBXij1c6o=
=RliO
-----END PGP SIGNATURE-----

--=-SA/wsgPOaBxZd54wAnFm--

On 8 Aug 2003, Markus Bertheau wrote:

> =D0=92 =D0=A1=D1=80=D0=B4, 06.08.2003, =D0=B2 17:51, scott.marlowe =D0=BF=
=D0=B8=D1=88=D0=B5=D1=82:
> > 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 on=
e
> > 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 know=
s
that better may have a more definitive answer.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--=-1n/BlE+rBPLScSqG8UUo
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

> 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 know=
s
> 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

--=-1n/BlE+rBPLScSqG8UUo
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/M6iy6DETLow6vwwRAoUQAJ9r6VM53cADjvZcFwf2+444Z+P49QCf T+KZ
BrAt3f6x4xl9b5oO95M/B74=
=bIP9
-----END PGP SIGNATURE-----

--=-1n/BlE+rBPLScSqG8UUo--

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!


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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 [at] 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo [at] postgresql.org