Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to log bind values for statements that produce errors

How to log bind values for statements that produce errors

From
Raul Kaubi
Date:
H
Centos7, postgres 13

We have a problem with certain select statement, which produces error and we would like to know the bind value that is given as parameter.

At the moment, the column is bigint type, and probably the bind that goes there is text, in postgresql logfile, the error is following.

operator does not exist: bigint = text

And it does log this select statement as well, where the bind value is $1.
“.. col1 = $1…”

I have tried parameters:
log_min_duration_statement = 0
log_parameter_max_length_on_error = -1

But still nothing. The first parameter logs binds only for succeeded statements. While the second one logs only this select statement and this error, but no binds are logged.

Is there any possibilities to log this bind value to postgresql.log file..?

Regards
Raul

Sent from my iPhone

Re: How to log bind values for statements that produce errors

From
Tom Lane
Date:
Raul Kaubi <raulkaubi@gmail.com> writes:
> We have a problem with certain select statement, which produces error and we would like to know the bind value that
isgiven as parameter. 
> I have tried parameters:
> log_min_duration_statement = 0
> log_parameter_max_length_on_error = -1

The other constraint on reporting parameters during error is that they
have to be sent by the client in text not binary mode.  I venture that
your client is sending them in binary.

(The reason for this restriction is to avoid the overhead of converting
binary to text for every query, which we'd have to do in advance of
knowing whether the query will throw an error.)

            regards, tom lane

Re: How to log bind values for statements that produce errors

From
Raul Kaubi
Date:
Hmm, actually, this same client executes several queries for that same session. For the first queries, I can clearly see the binds in postgresql.log. Only this third query that is being executed, it produces error and not binds are logged.
Also, there may the case, where the third query gets its bind parameter from the second query (as I understood the developer).

Regards
Raul

Kontakt Tom Lane (<tgl@sss.pgh.pa.us>) kirjutas kuupäeval T, 7. september 2021 kell 16:48:
Raul Kaubi <raulkaubi@gmail.com> writes:
> We have a problem with certain select statement, which produces error and we would like to know the bind value that is given as parameter.
> I have tried parameters:
> log_min_duration_statement = 0
> log_parameter_max_length_on_error = -1

The other constraint on reporting parameters during error is that they
have to be sent by the client in text not binary mode.  I venture that
your client is sending them in binary.

(The reason for this restriction is to avoid the overhead of converting
binary to text for every query, which we'd have to do in advance of
knowing whether the query will throw an error.)

                        regards, tom lane