Re: [HACKERS] propose: detail binding error log

[Craig Ringer]

On 15 March 2016 at 15:06, Ioseph Kim  wrote:


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

They aren't yet sent to the server, so it cannot display them.

Sure, your code says "setInteger(1)". But that doesn't send anything to the
server, it just stores it in the PreparedStatement object in Java.

PgJDBC does a Parse/Bind/Execute when you actually execute your prepared
statement after setting parameters. The parse phase, which comes first,
does NOT yet send the parameters your program supplied to the server.

So PgJDBC has the parameters, but the server, which is what is generating
the error, does not.

 I want see that

> "ERROR:  42804: column "a" is of type boolean but expression is of type
> integer(input value = 24) at character 25"
>
>
You can't, and I don't see any realistic way to make that happen except for
switching to client-side parameter binding (interpolation). Which is a bad
idea for performance and all sorts of other reasons. If you really must do
so, force the version 2 protocol in PgJDBC.

Really, the problem is your code: you should setBool not setInteger here.
That's really all there is too it.

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

Re: [HACKERS] propose: detail binding error log

[Ioseph Kim]

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  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
> 




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] propose: detail binding error log

[Craig Ringer]

On 15 March 2016 at 10:52, Ioseph Kim  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

Re: [HACKERS] propose: detail binding error log

[Ioseph Kim]

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  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
> 
> 




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] propose: detail binding error log

[Tom Lane]

Ioseph Kim  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers