Thread

1. Difference between 7.2 and 7.3, possible bug?

   Barry Lind <barry@xythos.com> — 2002-10-13T04:19:52Z

   create table test (col_a bigint);
   update test set col_a = nullif('200', -1);
   
   The above works fine on 7.2 but the update fails on 7.3b2 with the 
   following error:
   
   ERROR:  column "col_a" is of type bigint but expression is of type text
   	You will need to rewrite or cast the expression
   
   Is this change in behavior intentional or is it a bug?
   
   
   This situation is occuring because of two changes.  The first being the 
   difference in how the server is handling the above update in 7.2 vs. 
   7.3.  The second is a change in the jdbc driver in 7.3.  The actual 
   update in jdbc looks like:
   update test set col_a = nullif(?, -1);
   and a "setLong(1, 200)" call is being done.  In 7.2 the jdbc driver 
   bound the long/bigint value as a plain number, but in 7.3 it binds it 
   with quotes making it type text and exposing the change in server 
   behavior.  This change was made in the jdbc driver to work around the 
   fact that indexes are not used for int2 or int8 columns unless the value 
   is enclosed in quotes (or an explicit cast is used).  I am not sure if 
   the recent changes for implicit casts fixes this index usage problem in 
   the server or not.
   
   So I have three options here:
   
   1) if this is a server bug wait for a fix for 7.3
   2) revert the jdbc driver back to not quoting int2 and int8 values
         - If the server now handles using indexes on int2/int8 columns then
           this should be done anyway
         - It the server still has problems with using indexes without the
           quotes then this removes an often requested bugfix/workaround
           for the index usage problem
   3) Just have people rework their sql to avoid the change in behavior
   
   Any suggestions?
   
   
   thanks,
   --Barry
   
   
   
   

2. Re: [HACKERS] Difference between 7.2 and 7.3, possible bug?

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-13T05:05:24Z

   Barry Lind <barry@xythos.com> writes:
   > create table test (col_a bigint);
   > update test set col_a = nullif('200', -1);
   > The above works fine on 7.2 but the update fails on 7.3b2 with the 
   > following error:
   > ERROR:  column "col_a" is of type bigint but expression is of type text
   > 	You will need to rewrite or cast the expression
   
   > Is this change in behavior intentional or is it a bug?
   
   This is an intentional tightening of implicit-cast behavior.
   
   > This situation is occuring because of two changes.  The first being the 
   > difference in how the server is handling the above update in 7.2 vs. 
   > 7.3.  The second is a change in the jdbc driver in 7.3.  The actual 
   > update in jdbc looks like:
   > update test set col_a = nullif(?, -1);
   > and a "setLong(1, 200)" call is being done.  In 7.2 the jdbc driver 
   > bound the long/bigint value as a plain number, but in 7.3 it binds it 
   > with quotes making it type text and exposing the change in server 
   > behavior.
   
   I would say that that is a very bad decision in the JDBC driver and
   should be reverted ... especially if the driver is not bright enough
   to notice the context in which the parameter is being used.  Consider
   for example
   
   regression=# select 12 + 34;
    ?column?
   ----------
          46
   (1 row)
   
   regression=# select '12' + '34';
    ?column?
   ----------
    d
   (1 row)
   
   Not exactly the expected result ...
   
   > 2) revert the jdbc driver back to not quoting int2 and int8 values
   >       - If the server now handles using indexes on int2/int8 columns then
   >         this should be done anyway
   >       - It the server still has problems with using indexes without the
   >         quotes then this removes an often requested bugfix/workaround
   >         for the index usage problem
   
   You are trying to mask a server problem in the driver.  This is not a
   good idea.  The server problem is short-term (yes, we've finally agreed
   how to fix it, and it will happen in 7.4), but a client-library hack to
   mask it will cause problems indefinitely.
   
   			regards, tom lane
   
   

3. Re: [HACKERS] Difference between 7.2 and 7.3, possible bug?

   Barry Lind <barry@xythos.com> — 2002-10-13T05:35:06Z

   
   Tom Lane wrote:
   > I would say that that is a very bad decision in the JDBC driver and
   > should be reverted ... especially if the driver is not bright enough
   > to notice the context in which the parameter is being used.  Consider
   > for example
   ...
   > 
   > You are trying to mask a server problem in the driver.  This is not a
   > good idea.  The server problem is short-term (yes, we've finally agreed
   > how to fix it, and it will happen in 7.4), but a client-library hack to
   > mask it will cause problems indefinitely.
   > 
   > 			regards, tom lane
   > 
   
   Tom,
   
   Thanks for the quick reply.  I will back out the jdbc change.  It was 
   one of those changes I did reluctantly.  I have been pushing back for a 
   couple of releases now saying that this is a server bug and needs to be 
   fixed there.  But it didn't seem like that was ever going to happen so I 
   finally gave in.  For some users this bug of not using indexes for 
   int2/int8 makes it impossible for them to use postgres.  This happens 
   for users who are using a database abstraction layer that doesn't allow 
   the user to actually touch the sql being sent to the server.  Therefore 
   they have no opportunity to work around the underlying bug and can't use 
   postgres as their database because of the performance problems.
   
   I am glad to here this is finally getting resolved for 7.4.
   
   thanks,
   --Barry