Re: Inefficient escape codes. - Mailing list pgsql-performance

From Merlin Moncure
Subject Re: Inefficient escape codes.
Date
Msg-id 6EE64EF3AB31D5448D0007DD34EEB3417DD61D@Herge.rcsinc.local
Whole thread Raw
In response to Inefficient escape codes.  (Rodrigo Madera <rodrigo.madera@gmail.com>)
List pgsql-performance
Rodrigo wrote:
$$
As I understand it, the client needs to put the data into the server
using a textual-based command. This makes the 5MB data grow up-to 5x,
making it 25MB in the worst case. (Example: 0x01 -> \\001).

My question is:

1) Is there any way for me to send the binary field directly without
needing escape codes?
2) Will this mean that the client actually wastes my network bandwidth
converting binary data to text? Or does the client transparently manage
this?
$$ [snip]

I think the fastest, most efficient binary transfer of data to
PostgreSQL via C++ is a STL wrapper to libpq.  Previously I would not
have recommended libqpxx for this purpose although this may have changed
with the later releases.  As others have commented you most certainly
want to do this with the ExecParams/ExecPrepared interface.  If you want
to exclusively use libqxx then you need to find out if it exposes/wraps
this function (IIRC libpqxx build on top of libpq).

You can of course 'roll your own' libpq wrapper via STL pretty easily.
For example, here is how I am making my SQL calls from my COBOL apps:

typedef vector<string>      stmt_param_strings;
typedef vector<int>         stmt_param_lengths;
typedef vector<const char*> stmt_param_values;
typedef vector<int>         stmt_param_formats;

[...]

res = PQexecPrepared(    _connection,
                stmt.c_str(),
                num_params,
                ¶m_values[0],
                ¶m_lengths[0],
                ¶m_formats[0],
                result_format);

Executing data this way is a direct data injection to/from the server,
no parsing/unparsing, no plan generating, etc.  Also STL vectors play
very nice with the libpq interface because it takes unterminated stings.


Merlin





pgsql-performance by date:

Previous
From: Peter Childs
Date:
Subject: Re: Inefficient escape codes.
Next
From: "Steinar H. Gunderson"
Date:
Subject: Materializing a sequential scan