Обсуждение: Single row fetch from backend

Hi

Does postgres support the notion of single row fetch without having to use 
cursors with libpq.

What I want to do is something like
 myResult = PQexec(myConnection, "select * from mytable where field >= ''")
 for (int i = 0; i < PQntuples(myResult); i++) {   PQfetchRow(myResult); }

Ie. rows are retrieved from the backend only on request. I can then control
when I want to stop retreiving rows.

--------
Regards
Theo

Theo Kramer <theo@flame.co.za> writes:
> Does postgres support the notion of single row fetch without having to use 
> cursors with libpq.

Not unless you can precalculate the number of rows you want and use
LIMIT.  I recommend a cursor ;-).

There has been some talk of modifying libpq so that rows could be handed
back to the application a few at a time, rather than accumulating the
whole result before PQgetResult lets you have any of it.  That wouldn't
allow you to abort the SELECT early, mind you, but when you're dealing
with a really big result it would avoid waste of memory space inside the
client app.  I'm not sure if that would address your problem or not.

If you really want the ability to stop the fetch from the backend at
any random point, a cursor is the only way to do it.  I suppose libpq
might try to offer some syntactic sugar that would make a cursor
slightly easier to use, but it'd still be a cursor as far as the backend
and the FE/BE protocol were concerned.  ecpg is probably a better answer
if you want syntactic sugar...
        regards, tom lane

Tom Lane wrote:
> Not unless you can precalculate the number of rows you want and use
> LIMIT.  I recommend a cursor ;-).
> 
> There has been some talk of modifying libpq so that rows could be handed
> back to the application a few at a time, rather than accumulating the
> whole result before PQgetResult lets you have any of it.  That wouldn't
> allow you to abort the SELECT early, mind you, but when you're dealing
> with a really big result it would avoid waste of memory space inside the
> client app.  I'm not sure if that would address your problem or not.
> 
> If you really want the ability to stop the fetch from the backend at
> any random point, a cursor is the only way to do it.  I suppose libpq
> might try to offer some syntactic sugar that would make a cursor
> slightly easier to use, but it'd still be a cursor as far as the backend
> and the FE/BE protocol were concerned.  ecpg is probably a better answer
> if you want syntactic sugar...

Hmmm, I've had pretty bad experiences with cursors on Informix Online. When
many clients use cursors on large result sets the system (even on big iron)
grinds to a halt. Luckily you can fetch a single row at a time on a normal
select with Informix so that solved that. It does appear, however, that
Postgres does not create huge overheads for cursors, but I would still like
to see what happens when many clients do a cursor select...
--------
Regards
Theo

> 
> Hmmm, I've had pretty bad experiences with cursors on Informix Online. When
> many clients use cursors on large result sets the system (even on big iron)
> grinds to a halt. Luckily you can fetch a single row at a time on a normal
> select with Informix so that solved that. It does appear, however, that
> Postgres does not create huge overheads for cursors, but I would still like
> to see what happens when many clients do a cursor select...

I believe later Informix ODBC version cache the query reqults in the sql
server in case they are needed later.  Terrible for performance.  I have
clients downgrade to older isql clients.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian wrote:
> I believe later Informix ODBC version cache the query reqults in the sql
> server in case they are needed later.  Terrible for performance.  I have
> clients downgrade to older isql clients.

I had a look at the ODBC interface for Postgres, yet could not get it to work
on my Linux RH5.0 machine. When linking with libpsqlodbc.a I get the following

cc -I $PGHOME/include/iodbc testpgodbc.c $PGHOME/lib/libpsqlodbc.a -lm
 libpsqlodbc.a(psqlodbc.o): In function `_init': psqlodbc.o(.text+0x0): multiple definition of `_init'
/usr/lib/crti.o(.init+0x0):first defined here libpsqlodbc.a(psqlodbc.o): In function `_fini': psqlodbc.o(.text+0x30):
multipledefinition of `_fini' /usr/lib/crti.o(.fini+0x0): first defined here
 

Looks like I am not doing the correct thing, yet don't know what else to do.

Regards
Theo

> I had a look at the ODBC interface for Postgres, yet could not get it to work
> on my Linux RH5.0 machine. When linking with libpsqlodbc.a I get the following
> cc -I $PGHOME/include/iodbc testpgodbc.c $PGHOME/lib/libpsqlodbc.a -lm
>   libpsqlodbc.a(psqlodbc.o): In function `_init':
>   psqlodbc.o(.text+0x0): multiple definition of `_init'
>   /usr/lib/crti.o(.init+0x0): first defined here
>   libpsqlodbc.a(psqlodbc.o): In function `_fini':
>   psqlodbc.o(.text+0x30): multiple definition of `_fini'
>   /usr/lib/crti.o(.fini+0x0): first defined here
> Looks like I am not doing the correct thing, yet don't know what else to do.

Are you building in the Postgres tree using the make system? If you
aren't, try either:

1) configure --with-odbc  cd interfaces/odbc  make install

or

2) Unpack the "standalone" odbc file from ftp://postgresql.org/pub/
and configure then make it in a separate directory.

I've left out a few steps; read the html or postscript docs in the
chapter on ODBC for complete details, and let us know what didn't
work.

I've built the ODBC interface on RH5.2, and probably had used an
earlier version of RH when I was working out the port with the other
developers.

Good luck.
                       - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California