Thread

1. propose: detail binding error log

   Ioseph Kim <pgsql-kr@postgresql.kr> — 2016-03-15T02:52:53Z

   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
   
   
   
   

2. Re: propose: detail binding error log

   Tom Lane <tgl@sss.pgh.pa.us> — 2016-03-15T03:06:34Z

   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
   
   
   

3. Re: propose: detail binding error log

   Ioseph Kim <pgsql-kr@postgresql.kr> — 2016-03-15T05:36:45Z

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

4. Re: propose: detail binding error log

   Craig Ringer <craig@2ndquadrant.com> — 2016-03-15T05:54:20Z

   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
   

5. Re: propose: detail binding error log

   Ioseph Kim <pgsql-kr@postgresql.kr> — 2016-03-15T07:06:19Z

   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
   > 
   
   
   
   
   

6. Re: propose: detail binding error log

   Craig Ringer <craig@2ndquadrant.com> — 2016-03-15T07:13:17Z

   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.
   
   -- 
    Craig Ringer                   http://www.2ndQuadrant.com/
    PostgreSQL Development, 24x7 Support, Training & Services