Re: Using libpq, floats and binary data

Hi,

Inserting many of rows is almost always IO bound. Converting ints and floats
to text is CPU bound and really fast anyway. To speed things up first look
at things like indexes, how often you need to COMMIT or using COPY. Only
then look at prepared statements and binary transfer modes. Else it's simply
not worth the headache.

Groeten, Arjen

On Wed, Sep 15, 2010 at 6:02 PM, Michael Hull <mikehulluk(at)googlemail(dot)com>wrote:

> Hi Guys,
> I have jsut started to use the libpq interface to postgre, and was
> wondering if someone with some experience could explain the 'best
> practices' :)
>
> I am preparing a query, then submitting it with binary data. 6 values
> are ints, and one is a float.
>
> For the ints, I understand that since I need network byte ordering, I
> have to pump them through htonl()
> The same is also true for the floats I assume, so I have dones some
> dirty,dirty casting, but this feels like a source for problems later
> on.
> It does the correct thing, but is there a better way of doing it??
>
>
> Mike
>
>
> char qString[] = "INSERT INTO raw_results (ca,kf,ks,lk,na,iinj,
> AP1height) VALUES( $1::int4, $2::int4, $3::int4, $4::int4, $5::int4,
> $6::int4, $7::float4)";
>
> PGresult* pPreparedQuery = PQprepare(pDB, "InsertQ",qString, nParams,
> NULL);
>
>
>
>
> float AP1Height = 3.141;
> /* TROUBLE AHEAD! */
> if( sizeof( float) != sizeof(int32_t) ) throw MyException();
> int32_t AP_int = * ( (int32_t*) &AP1Height );
> int32_t AP_intSwapped = htonl(AP_int);
> float AP1HeightSwapped = *( (float*) &AP_intSwapped);
> /* TROUBLE OVER */
>
>
> int32_t ca= htonl(100);
> int32_t kf= htonl(200);
> int32_t ks= htonl(300);
> int32_t lk= htonl(400);
> int32_t na= htonl(500);
> int32_t iinj= htonl(600);
>
> const char* values[nParams] = {
> (char*)&ca,
> (char*)&kf,
> (char*)&ks,
> (char*)&lk,
> (char*)&lk,
> (char*)&iinj,
> (char*)&AP1HeightSwapped,
> };
> int lengths[nParams] = {
> sizeof(ca),
> sizeof(kf),
> sizeof(ks),
> sizeof(lk),
> sizeof(na),
> sizeof(iinj),
> sizeof(AP1Height),
> };
>
> int binary[nParams]={1,1,1,1,1, 1,1};
>
> PGresult* res = PQexecPrepared( pDB, "InsertQ", nParams, values,
> lengths,binary,0);
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>