PostgreSQL - Smallint overflowing when creating index on multiple columns. Is this a bug?

Question

I was having an issue inserting the value 32767 into a smallint column in Postgres, which would yield the error smallint out of range. This was odd because I could do:

SELECT 32767::int2;

Which would work just fine. After a little hair pulling, I finally tracked this down to an index on the column in question. First, here's the schema (Well, not really but I've simplified this down to a repro case):

CREATE TABLE Test
(
  id uuid NOT NULL,
  cooktime smallint,
  preptime smallint,
  CONSTRAINT test_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);

I now create the following index:

CREATE INDEX idx_test_totaltime
  ON Test
  USING btree
  ((cooktime + preptime) );

Next, I try to create the following row:

INSERT INTO Test (CookTime, PrepTime, Id)
VALUES (
  (32767)::int2,
  (10)::int2,
  (E'fd47dc1e-c3c6-42c1-b058-689e926a72a4')::uuid
);

I get the error:

ERROR: smallint out of range SQL state: 22003

It seems that idx_test_totaltime is expecting a maximum value of int2, even though the index is applied on the sum of two smallints.

Is this a Postgres bug, or am I missing something simple? Is there a way to work around this limitation, or would I need to make these columns int4 and use a CHECK constraint to limit each value to 32767? I'm using Postgres 9.0.0 (Yes, I need to upgrade!) but I've created a SQL Fiddle which demonstrates this error on 9.1.4.

score 5 · Accepted Answer · answered Aug 05 '12 at 05:37

5

Your problem is that int2 + int2 is another int2 so the expression in your index, (cooktime + preptime), overflows for (32767, 10). You can get around this problem with a bit of casting in the index expression:

CREATE INDEX idx_test_totaltime
  ON Test
  USING btree
  ((cooktime::int4 + preptime::int4));

You only need one of the casts but using both doesn't hurt.

answered Aug 05 '12 at 05:37

mu is too short

426,620
70
833
800

That works. However, I've noticed if you do `SELECT * FROM Test WHERE CookTime + PrepTime > 100` then it will not use the index. You have to specify `WHERE CookTime::Int4 + PrepTime::Int4 > 100` instead. Now I have to update my search code :) – Mike Christensen Aug 05 '12 at 05:49
1

@MikeChristensen: That's a good spot on the index not getting used. I'd recommend just forget about all the `int2` business and use `int` for the column with appropriate CHECK constraints. – mu is too short Aug 05 '12 at 06:35
1

Cast the result of the calculation to an INT2, not the columns in the index. – Frank Heikens Aug 05 '12 at 10:29
@FrankHeikens: You can't cast the result in the index, the overflow will happen before the `(cooktime + preptime)::int4` cast. Or am I misunderstanding what you're trying to say? – mu is too short Aug 05 '12 at 21:14
2

Ok, if the overflow happens before, it doesn't work. But the real problem is the usage of the wrong data type, an INT2 had nothing to do with time. What does 100 mean? 100 seconds? 100 minutes? 100 hours? Nobody knows, a recipe for bugs. – Frank Heikens Aug 06 '12 at 06:53

score 3 · Answer 2 · answered Aug 05 '12 at 10:27

3

Why don't you use an INTERVAL for a time interval? It's a perfect solution for your problem.

answered Aug 05 '12 at 10:27

Frank Heikens

117,544
24
142
135

That's probably a great solution as well. Making this change now might be somewhat messy though, but I might do some experimenting to see what would be involved. However, Mu's solution works perfectly fine as well and required me to update minimal code. – Mike Christensen Aug 05 '12 at 15:49

PostgreSQL - Smallint overflowing when creating index on multiple columns. Is this a bug?

2 Answers2