Search Postgresql Archives

Re: Is this a Postgres Bug?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 5.8.2012 07:14, Mike Christensen wrote:
> First off, I've posted this question on StackOverflow in case anyone
> wants to answer it:
> 
> http://stackoverflow.com/questions/11814132/postgresql-smallint-
> overflowing-when-creating-index-on-multiple-columns-is-th
> 
> The repro can be found here: http://sqlfiddle.com/#!1/734d7/1
> 
> I'm happy to log this as a bug, unless someone can explain to me why
> this behavior is by design.  Thanks!

Definitely not a bug. The problem is not the index, it's the expression.
The data type of the result of deduced from the parts, and as it's an
addition of two int2 columns, the result is expected to be int2 too. Try
this:

  test=# select 32767::int2 + 10::int2;
  ERROR:  smallint out of range

No index, same result.

This is intentional, or rather expected, because the reasoning behind
this was 'we're not aware of a better solution' than 'we do want it to
behave like this.' But it clearly is not a bug.

I see two possible solutions:

1) change the column data types, e.g. to int4 - The limits will be much
   higher so you won't hit them. And the additional storage cost is
   minimal, especially considering the tuple header and UUID space
   requirements.

2) Retype the expession so that it uses different datatype. Just retype
   one of the columns like this:

     select (32767::int2 + 10::int2::int4);

   and everything should work fine. But you'll have to do the same in
   the queries, probably.

Tomas

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux