Re: libpq, PQexecPrepared, data size sent to FE vs. FETCH_COUNT

I have some libpq-using application code, in which fetching the data
follows this logic (after a statement has been prepared):

----------------------------------------

PQexecPrepared(pg_result, pg_conn, pg_statement_name, input_param_cnt,
param_values, param_lengths, param_formats, result_format);

PQntuples(&rows_in_result, pg_result);

/* The application provides storage so that I can pass a certain number of rows
* (rows_to_pass_up) to the caller, and I repeat the following loop until
* many rows_to_pass_up cover all the rows_in_result (pg_row_num_base keeps the track
* of where I am in the process. */

for (int row_idx = 0; row_idx < rows_to_pass_up; ++row_idx) {
const int pg_row_number = row_idx + pg_row_num_base;

for (int pg_column_number = 0; pg_column_number < result_column_cnt_ ++pg_column_number) {
PQgetvalue(&value, pg_result, pg_row_number, pg_column_number);
PQgetlength(&length, pg_result, pg_row_number, pg_column_number);
}
}

----------------------------------------

My question is: am I doing the right thing from the "data size being
passed from BE to FE" perspective?

The code in `bin/psql' relies on the value of the FETCH_COUNT
parameter to build an appropriate

fetch forward FETCH_COUNT from _psql_cursor

command.

No equivalent of FETCH_COUNT is available at the libpq level, so I
assume that the interface I am using is smart enough not to send
gigabytes of data to FE.

Is that right? Is the logic I am using safe and good?

Where does the result set (GBs of data) reside after I call
PQexecPrepared? On BE, I hope?

Thanks,

-- Alex -- alex-goncharov(at)comcast(dot)net --

,--- I/Alex (Mon, 24 May 2010 12:25:18 -0400) ----*
| No equivalent of FETCH_COUNT is available at the libpq level, so I
| assume that the interface I am using is smart enough not to send
| gigabytes of data to FE.
|
| Where does the result set (GBs of data) reside after I call
| PQexecPrepared? On BE, I hope?

Sorry for asking again...

No sarcasm meant: is there no straightforward answer here? Or nobody
is certain? Or a wrong list?

Thanks,

-- Alex -- alex-goncharov(at)comcast(dot)net --

At 2010-05-25 07:35:34 -0400, alex-goncharov(at)comcast(dot)net wrote:
>
> | Where does the result set (GBs of data) reside after I call
> | PQexecPrepared? On BE, I hope?

Unless you explicitly declare and fetch from an SQL-level cursor, your
many GBs of data are going to be transmitted to libpq, which will eat
lots of memory. (The wire protocol does have something like cursors,
but libpq does not use them, it retrieves the entire result set.)

-- ams

Alex Goncharov wrote:
> ,--- I/Alex (Mon, 24 May 2010 12:25:18 -0400) ----*
> | No equivalent of FETCH_COUNT is available at the libpq level, so I
> | assume that the interface I am using is smart enough not to send
> | gigabytes of data to FE.
> |
> | Where does the result set (GBs of data) reside after I call
> | PQexecPrepared? On BE, I hope?
>
> Sorry for asking again...
>
> No sarcasm meant: is there no straightforward answer here? Or nobody
> is certain? Or a wrong list?
>
The straighforward answer is that the libpq frontend c-library does not
support something like the JDBC client's setFetchSize.

The GBs of data are gathered at the site of the libpq client (pgresult
object gathered/allocated while consuming result input from backend).

regards,
Yeb Havinga

,--- Abhijit Menon-Sen (Tue, 25 May 2010 17:26:18 +0530) ----*
| Unless you explicitly declare and fetch from an SQL-level cursor, your
| many GBs of data are going to be transmitted to libpq, which will eat
| lots of memory. (The wire protocol does have something like cursors,
| but libpq does not use them, it retrieves the entire result set.)
,--- Yeb Havinga (Tue, 25 May 2010 14:08:51 +0200) ----*
| The GBs of data are gathered at the site of the libpq client (pgresult
| object gathered/allocated while consuming result input from backend).
`------------------------------------------------------*

Thank you very much!

-- Alex -- alex-goncharov(at)comcast(dot)net --

On 05/25/2010 07:35 AM, Alex Goncharov wrote:
> ,--- I/Alex (Mon, 24 May 2010 12:25:18 -0400) ----*
> | No equivalent of FETCH_COUNT is available at the libpq level, so I
> | assume that the interface I am using is smart enough not to send
> | gigabytes of data to FE.
> |
> | Where does the result set (GBs of data) reside after I call
> | PQexecPrepared? On BE, I hope?
>
> Sorry for asking again...
>
> No sarcasm meant: is there no straightforward answer here? Or nobody
> is certain? Or a wrong list?
>

Issue multiple queries and make use of LIMIT/OFFSET. You'll have to go
manual on this one.

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

Alex Goncharov wrote:
> ,--- I/Alex (Mon, 24 May 2010 12:25:18 -0400) ----*
> | No equivalent of FETCH_COUNT is available at the libpq level, so I
> | assume that the interface I am using is smart enough not to send
> | gigabytes of data to FE.
> |
> | Where does the result set (GBs of data) reside after I call
> | PQexecPrepared? On BE, I hope?
>
> Sorry for asking again...
>
> No sarcasm meant: is there no straightforward answer here? Or nobody
> is certain? Or a wrong list?
>
>
>

You have been given the answer. Please re-read the replies, e.g. the one
from Abhijit Menon-Sen.

The data is saved on the client side before the call returns. If that
uses too much memory, use a cursor.

cheers

andrew

Abhijit Menon-Sen <ams(at)toroid(dot)org> wrote:

> Unless you explicitly declare and fetch from an SQL-level cursor, your
> many GBs of data are going to be transmitted to libpq, which will eat
> lots of memory. (The wire protocol does have something like cursors,
> but libpq does not use them, it retrieves the entire result set.)

Sounds like a project. Anyone got any suggestions about
semantics and function names? (Assuming that this can be done
without causing more problems on the backend; I'd rather one
frontend client get messed up than mess up the server if
someone makes a query like that.)

I'm not exactly volunteering to work on something like this
(my TODO list is a trifle long) but I'm working on a native Go
language interface for PostgreSQL presently (influced by but
not an exact clone of libpq) so it's perhaps something I could
do if I get free time in future.

Giles