Обсуждение: propose: detail binding error log

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.

java code:
pstmt = connection.prepareStatement("insert into test values (?)");
pstmt.setInt(1, 1);

I could not see that value at a server log, by changing any servier
configurations.

That case is debuged to client only.

So, I propose that error value of bind variable will be displayed at a
server log.
in parse_target.c:529,
input parameter value of that node not containded value of column at
client.

I want more detail error log.

Regards, Ioseph Kim

Ioseph Kim <pgsql-kr@postgresql.kr> writes:
> I want see that value of bind variable at a server log.

That's available if you turn on log_statements, IIRC.
        regards, tom lane

thanks for reply.

value of log_statement is already 'all'
I set log_min_messages = debug5, log_error_verbosity = verbose and
debug_print_parse = on too.
but I could not a value of client in server log.

this case is occured only at jdbc prepare statement and wrong type
binding.

reguards, Ioseph.

2016-03-14 (월), 23:06 -0400, Tom Lane:
> Ioseph Kim <pgsql-kr@postgresql.kr> writes:
> > I want see that value of bind variable at a server log.
> 
> That's available if you turn on log_statements, IIRC.
> 
>             regards, tom lane
> 
> 

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@postgresql.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
>