Re: storing C binary array in bytea via libpq

Tom DalPozzo <t(dot)dalpozzo(at)gmail(dot)com> writes:
> I've a table in which a field is BYTEA, as I need to store around 200 raw
> bytes in this field.
> I need to perform many many INSERT starting from a common C array and, in
> order to get good performance, I want to do many of them in a single BEGIN
> COMMIT block.
> What is the best choice from libpq?
> PQexec needs to have the bytes encoded into a string. This expansion lowers
> the performance (I tried with \x format, not with the other option yet).
> With PQexecParams, can I send a raw array of bytes as a bytea parameter?

Sure. Specify binary format for that parameter.

> And also, as PQexecParams can't accept multi commands, if I enclose many
> PQexecParams calls between a PQexec("BEGIN;") and PQexec("COMMIT") would
> it work as I wish?

Well, it'll be faster than committing them separately, but have you
considered bundling this up into a single INSERT with multiple VALUES
rows? It'd be a bit tedious to manage by hand, but if the command is
being constructed by a program anyway, it shouldn't be much harder
than separate INSERTs. Much of the time in this is going to go into
parsing and network round-trip overhead, so one statement is going
to handily beat N statements whether they're in a transaction block
or not.

regards, tom lane