Re: propose: detail binding error log

thanks for reply.

Craig wrote:
>> 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.

I know already that, so I wrote how can see error value at server log.

case 1: in psql simple query

ERROR: 42804: column "a" is of type integer but expression is of type
text at character 45
HINT: You will need to rewrite or cast the expression.
LOCATION: transformAssignedExpr, parse_target.c:529
STATEMENT: prepare aaa (text) as insert into b values ($1);

when this case, server error log is right that does not know value
because not yet be assigned.

but.
case 2: in jdbc program.
ERROR: 42804: column "a" is of type boolean but expression is of type
integer at character 25
HINT: You will need to rewrite or cast the expression.
LOCATION: transformAssignedExpr, parse_target.c:529
STATEMENT: insert into test values ($1)

when this case, statement is 'insert', I think binding values already
sent to server, then server can display these.

I want see that
"ERROR: 42804: column "a" is of type boolean but expression is of type
integer(input value = 24) at character 25"

Best regards, Ioseph.

2016-03-15 (화), 13:54 +0800, Craig Ringer:
> 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
>