Thread: 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
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 --
,--- 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 --
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
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
,--- 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 --
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@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