Skip site navigation (1) Skip section navigation (2)

Re: Incremental results from libpq

From: "Goulet, Dick" <DGoulet(at)vicr(dot)com>
To: <gsstark(at)mit(dot)edu>,"Alvaro Herrera" <alvherre(at)commandprompt(dot)com>
Cc: "Frank van Vugt" <ftm(dot)van(dot)vugt(at)foxi(dot)nl>,<pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: Incremental results from libpq
Date: 2005-11-13 16:23:18
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-interfaces

	While you can bulk collect or array process records in Oracle,
the older less efficient method is also still around.  And yes you can
actually measure the inefficiency thereby created.  Also there are
places where that inefficiency is of value.  Take a case where your
applying a price change to a pricing table, assume that you want to
raise the price by one of several values based on another column in the
table.  Oracle allows you to open the cursor with the "for update of"
clause.  This has the effect or locking the affected rows in update mode
as they are included in the result set.  Now once you've figured out the
new price you can simply update the table "where current of
<cursor_name>".  Works very well, especially in a stored
procedure/package/function where sqlnet does not get in the way.

-----Original Message-----
From: gsstark(at)mit(dot)edu [mailto:gsstark(at)mit(dot)edu] 
Sent: Sunday, November 13, 2005 11:14 AM
To: Alvaro Herrera
Cc: Goulet, Dick; Frank van Vugt; Greg Stark;
Subject: Re: [INTERFACES] Incremental results from libpq

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:

> Postgres supports cursors too.  The Qt guys, and everyone else, could
> using it to get incremental results right now, no libpq mods

Not really since the way Postgres supports cursors is at the SQL level.
of Qt and every other driver could be using cursors if their drivers
them, but Qt can't really be reasonably expected to go into users' SQL
modify them to use cursors.

Moreover cursors aren't really that great a substitute. With cursors you
to manually fetch individual records. You're just trading off the
inefficiencies of batching up all the results for entirely different
inefficiencies. Now for every record you retrieve you need a network
trip as well as a round trip down through your driver, the kernel layers
both machines, and the backend as well.

The efficient approach as Oracle and other mature network layers
implement is
to issue the query once, then pipeline the results back to the
buffering a substantial amount in the driver. DBD::Oracle goes to some
to ensure the number of records buffered is a reasonable multiple of the
default TCP mss of 1500 bytes. 

So even though the application only retrieves one record at a time it's
pulling it out of an array that's already prefilled. When the array gets
the next block of records is retrieved from the server (where they're
already buffered as well). The result is a constant flow of network
that keeps the application and network as busy as possible.


pgsql-interfaces by date

Next:From: Peter EisentrautDate: 2005-11-16 09:34:58
Subject: Re: Incremental results from libpq
Previous:From: Goulet, DickDate: 2005-11-13 16:16:07
Subject: Re: Incremental results from libpq

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group