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

Fri, 13 Aug 1999 06:11:48 +0200
Theo Kramer <theo@flame.co.za>
Single row fetch from backend
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

Fri, 13 Aug 1999 10:41:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Single row fetch from backend 
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

Fri, 13 Aug 1999 17:33:28 +0200
Theo Kramer <theo@flame.co.za>
Re: [HACKERS] Single row fetch from backend
> 
> 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, Pennsylvania 19026

Fri, 13 Aug 1999 13:03:43 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Single row fetch from backend
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): 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.

Regards
Theo

Sat, 14 Aug 1999 15:15:28 +0200
Theo Kramer <theo@flame.co.za>
Re: [HACKERS] Single row fetch from backend

> 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

Sat, 14 Aug 1999 13:52:43 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Single row fetch from backend