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

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, Pennsylvania 19026

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

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