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

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

From
Alex Goncharov
Date:
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
canpass a certain number of rows  * (rows_to_pass_up) to the caller, and I repeat the following loop until  * many
rows_to_pass_upcover all the rows_in_result (pg_row_num_base keeps the track  * of where I am in the process. */  for
(introw_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@comcast.net --


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

From
Alex Goncharov
Date:
,--- 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@comcast.net --



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

From
Yeb Havinga
Date:
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




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

From
Abhijit Menon-Sen
Date:
At 2010-05-25 07:35:34 -0400, alex-goncharov@comcast.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


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

From
Alex Goncharov
Date:
,--- 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@comcast.net --


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

From
Andrew Chernow
Date:
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/


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

From
Andrew Dunstan
Date:

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


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

From
Giles Lean
Date:
Abhijit Menon-Sen <ams@toroid.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