Re: int8 primary keys still not using index without manual

Tino Wildenhain wrote:
> Hi Craig,
>
> Craig O'Shannessy schrieb:
> > I'm using EJB CMP (Enterprise Java Beans, Container Managed Persistence),
> > so the SQL is generated. I would think this is a common usage of
> > PostgreSQL, as a database for a modern EJB container. There are options
> > for fixing this (not including fixing postgres itself), IMHO the best is
> > patching the JDBC PreparedStatement code so that setLong() adds '::int8'.
> > The advantage here is that you can use hand coded prepared statements, as
> > well as auto CMP ones, and both will get the proper cast.
> >
> > The real problem is that PostgreSQL out of the box is not really usable
> > for CMP! This really isn't good, and I'm always suprised that it's not
> > fixed. It was very luck we found the bug on the website when we were
> > evaluating PostgreSQL against Oracle, it wasn't easy to track down or fix,
> > and it causes truly horrible performance problems.
> >
> > I spose you'd call it my pet peeve.
> >
> I agree with you wholeheartly - it also bothers me why
> postgresql can cast [0-9]+ to int4, but only
> '[0-9]+' to int8 or int2, I really cannot see the
> difference.

OK, the issue for int8, I think, is that when the lexer/scanner is
looking for tokens, it doesn't know how the token is going to be used,
so it doesn't know if [0-9]+ should be considered an int4 or int8 so it
makes it an int4. When it is a string, there is no type-casting done in
the lexer because it is just a string.

What I don't understand is why we can't just upgrade an int4 to int8 if
the value is greater than an int4, and why we can't just convert it
inside to int8 as needed. I am sure there is a reason, but I can't
remember it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073