Re: propose: detail binding error log

On 15 March 2016 at 10:52, Ioseph Kim <pgsql-kr(at)postgresql(dot)kr> wrote:

> Hi, hackers.
>
> I had a error message while using PostgreSQL.
>
> "ERROR: 42804: column "a" is of type boolean but expression is of type
> integer at character 25
> LOCATION: transformAssignedExpr, parse_target.c:529"
>
> This error is a java jdbc binding error.
> column type is boolean but bind variable is integer.
>
> I want see that value of bind variable at a server log.
>

log_statement = 'all' will log bind var values, but only when the statement
actually gets executed.

This is an error in parsing or parameter binding, before we execute the
statement. It's a type error and not related to the actual value of the
bind variable - you could put anything in the variable and you would get
the same error.

PostgreSQL is complaining that you bound an integer variable and tried to
insert it into a boolean column. There is no implicit cast from integer to
boolean, so that's an error. It doesn't care if the integer is 1, 42, or
null, since this is a type error. There's no need to log the value since
it's irrelevant.

Observe:

postgres=# create table demo(col boolean);
CREATE TABLE

postgres=# prepare my_insert(boolean) AS insert into demo(col) values ($1);
PREPARE

postgres=# prepare my_insertint(integer) AS insert into demo(col) values
($1);
ERROR: column "col" is of type boolean but expression is of type integer
LINE 1: ... my_insertint(integer) AS insert into demo(col) values ($1);
^
HINT: You will need to rewrite or cast the expression.

As you see, the error is at PREPARE time, when we parse and validate the
statement, before we bind parameters to it. You can get the same effect
without prepared statements by specifying the type of a literal explicitly:

postgres=# insert into demo(col) values ('1'::integer);
ERROR: column "col" is of type boolean but expression is of type integer
LINE 1: insert into demo(col) values ('1'::integer);
^
HINT: You will need to rewrite or cast the expression.

At the time PostgreSQL parses the statement it doesn't know the parameter
values yet, because PgJDBC hasn't sent them to it. It cannot log them even
if they mattered, which they don't.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services