Thread: 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 > >
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.
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
>
On 15 March 2016 at 15:06, Ioseph Kim <pgsql-kr@postgresql.kr> 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.