Re: Postgresql network transmission overhead

On Wed, Feb 24, 2021 at 09:14:19AM +0100, Michael J. Baars wrote:
> I've written this function to insert several rows at once, and noticed a certain postgresql overhead as you can see from the log file. A lot more data than the
> user data is actually sent over the net. This has a certain noticeable impact on the user transmission speed.
>
> I noticed that a libpq query always has a number of arguments of the following form:
>
> Oid paramt[cols] = { 1082, 701, 701, 701, 701, 701, 20, 701 };
> int paraml[cols] = { 4, 8, 8, 8, 8, 8, 8, 8 };
> int paramf[cols] = { 1, 1, 1, 1, 1, 1, 1, 1 };
>
> result = PQexecParams(psql_cnn, (char* ) &statement, 1, paramt, (const char** ) paramv, paraml, paramf, 1);
>
> I think the 'paramf' is completely redundant. The data mode, text or binary, is already specified in the last argument to 'PQexecParams' and does not have to be
> repeated for every value. Am I correct?

The last argument is the *result* format.
The array is for the format of the *input* bind parameters.

Regarding the redundancy:

https://www.postgresql.org/docs/current/libpq-exec.html
|nParams
| The number of parameters supplied; it is the length of the arrays paramTypes[], paramValues[], paramLengths[], and paramFormats[]. (The array pointers can be NULL when nParams is zero.)
|paramTypes[]
| Specifies, by OID, the data types to be assigned to the parameter symbols. If paramTypes is NULL, or any particular element in the array is zero, the server infers a data type for the parameter symbol in the same way it would do for an untyped literal string.
|paramValues[]
| ...
|paramLengths[]
| Specifies the actual data lengths of binary-format parameters. It is ignored for null parameters and text-format parameters. The array pointer can be null when there are no binary parameters.
|paramFormats[]
| Specifies whether parameters are text (put a zero in the array entry for the corresponding parameter) or binary (put a one in the array entry for the corresponding parameter). If the array pointer is null then all parameters are presumed to be text strings.

nParams specifies the length of the arrays: if you pass an array of length
greater than nParams, then the rest of the array is being ignored.

You don't *have* to specify Types, and Lengths and Formats can be specified as
NULL for text format params.

> semi-prepared

What does semi-prepared mean ?

--
Justin