Re: JBoss w/int8 primary keys in postgres ...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

This is an old discussion. :)
At the office we resolved this by making the driver to quote every
setLong or SetBigDecimal (the problem exist here too.)
Its really easy to do but it's not the correct way: this a backend's
problem and have to be fixed there and not at the driver.
If you already have an application in production state this "hack" can
resolve for moment but this need to be resolved correctly in the future.

I paste the old mails about this subject as it's look like Barry is busy
or away.

Regards.
João Paulo Ribeiro

- -------- Original Message --------
Subject: Re: [JDBC] One little tip for int8 and decimal :)
Date: Thu, 28 Mar 2002 09:28:56 -0800
From: Barry Lind <barry(at)xythos(dot)com>
To: João Paulo Ribeiro <jp(at)mobicomp(dot)com>
CC: dave(at)fastcrypt(dot)com
References: <3CA200D9(dot)10100(at)mobicomp(dot)com> <3CA27CB5(dot)1080002(at)xythos(dot)com>
<3CA309BD(dot)8050405(at)mobicomp(dot)com>

João,

There are two reasons that I don't what to make this change:

1) With this change in place it is less likely that the real problem
will get fixed. Putting workarounds in place often have the effect of
ensuring that the real problem never gets fixed. If the amount of
resources that it has taken the various people to discuss and build
workarounds to this problem where dedicated to fixing the real problem,
the real problem whould have been fixed by now.

2) I am concerned that this change will break existing code. I am
concerned that there exists in the postgresql parser instances where a
quoted number is not allowed. This change would then break existing
code. (Although I am less concerned about your approach than what has
been proposed in the past, which was to have the driver produce explicit
casts so 1234 would become 1234::int8 . I think just your approach
of changing 1234 to '1234' is less likely to break existing code).

Finally, most people who have run into this problem have been able to
work around the problem either by explicitly casting in their sql
statements (i.e. adding ::int8) or by calling
setString(Long.toString(var)) in their code.

Having said all of that I do understand the problem you are facing
because you are working with a tool set that doesn't allow you to use
the workarounds that others can take advantage of. So I will agree to
add the workaround you suggest to the jdbc driver at the end of the 7.3
development cycle if the real problem hasn't been fixed in the backend.

I would suggest that you send an email note to the pgsql-general and/or
pgsql-hackers list to explain the importance of getting this problem
fixed in the backend becuase you can't work around the problem since you
don't have control over the sql that is being generated in the tool set
you are using. I think many developers who are in a position to fix the
real problem don't think the problem is as bad as it is because they
assume you can work around the problem easily by changing your code.

thanks,
- --Barry

João Paulo Ribeiro wrote:
> Barry,
>
> I understand that the problem is in the backend and this is why i called
> it a little tip.
> Maybe you are suggesting that the fix that not appears in versions <
> 7.2 will apear someday, but what we do in the while?
> You are trying to tell me that its better to use another database?
> Because if you use preparedstatement to acess big tables with int8 or
> decimal in postgres its better to forget it.
> Postgres without this will not be usefull for business.
>
> I perfectly understand that the problem is not in the JDBC driver but
> can someone tell me why we cant fix it here?
>
> Advantages:
> - its very easy to fix
> - it dont have implication with backward compatibilies
> - it will fix the problem with some older versions of postgres (7.0 and
> 7.1 and dont know about the others)
> - it wil not make problem compatibilities in the futures
>
> Disadvantages:
> - the setString(...) method is slower than the set(...)
>
> I talk for the point view of someone that use postgres for work with
> medium databases (>400MB) and that can wait for the fix that can will
> appear to late.
> If we didnt made the fix we swapped out to Oracle.
> This is creating a very bad image of java with postgres.
>
> But as i said it was a suggestion.
>
> Best regards.
> João Paulo Ribeiro
>
> Barry Lind wrote:
>
>> João,
>>
>> This has been discussed before on the mailing list and rejected as the
>> wrong way to fix the problem. The correct way to fix this problem is
>> to fix the problem in the backend, not to work around the problem in
>> each of the front ends.
>>
>> --Barry
>>
>>
>> João Paulo Ribeiro wrote:
>>
>>> Hi!
>>>
>>> We are working with java and postgresql for a while.
>>>
>>> In our experiences we have seen the problem with int8 and decimal:
>>> postgres dont convert this types easyli and because of this the
>>> result sometimes wil not be the expected.
>>>
>>> A simple example:
>>> We have this table:
>>>
>>> create table test(
>>>
>>> data int8 NOT NULL PRIMARY KEY
>>>
>>> );
>>>
>>>
>>> we put n lines (n> 70000) :)
>>>
>>> if we try to make query withou explicit cast the postgres will not
>>> use the index.
>>> Example:
>>>
>>> pvs=# explain select * from test where data=12345;
>>>
>>> NOTICE: QUERY PLAN:
>>>
>>> Seq Scan on test (cost=0.00..22.50 rows=1 width=8)
>>>
>>> EXPLAIN
>>>
>>> pvs=#
>>>
>>>
>>> but with a explicit cast:
>>>
>>> pvs=# explain select * from test where data=12345::int8;
>>>
>>> NOTICE: QUERY PLAN:
>>>
>>> Index Scan using test_pkey on test (cost=0.00..4.82 rows=1 width=8)
>>>
>>> EXPLAIN
>>>
>>> pvs=#
>>>
>>> another aproach is to force the postgresql to evaluate and transform
>>> the value to the desired datatype using quotes '
>>>
>>> pvs=# explain select * from test where data='12345';
>>>
>>> NOTICE: QUERY PLAN:
>>>
>>> Index Scan using test_pkey on test (cost=0.00..4.82 rows=1 width=8)
>>>
>>> EXPLAIN
>>>
>>> pvs=#
>>>
>>>
>>> This problem is well known for the postgres user.
>>> But the problem go further when you use JDBC to access the postgresql.
>>> Using the same table.
>>> We have a little program that make a simple query:
>>> ...
>>>
>>> DBConnection con = someKindOfDbPool.allocateConnection();
>>>
>>> PreparedStatement ps = con.prepareStatement("Select * from
>>> user2 where obid=?");
>>>
>>> ps.setlong(1,123456);
>>>
>>> ps.executeQuery();
>>>
>>> ...
>>>
>>> This query will never use the index because of the problem explained
>>> above.
>>> We can use setBigDecimal and problem will persist.
>>>
>>> I use DODs with Enhydra and the data layer generated by the DODs
>>> have this problem.
>>>
>>> What we propose is to change the prepared statment to force postgres
>>> to correctly use the index and the result will be the expected. :)
>>> For example, at the office we made a little change to the setLong
>>> and setBigDecimal from PreparedStatement class.
>>>
>>> The orginal look like:
>>>
>>> public void setBigDecimal(int parameterIndex, BigDecimal x) throws
>>> SQLException
>>> {
>>> if (x == null)
>>> setNull(parameterIndex, Types.OTHER);
>>> else
>>> set(parameterIndex, x.toString());
>>> }
>>>
>>>
>>> public void setLong(int parameterIndex, long x) throws SQLException {
>>> set(parameterIndex, (new Long(x)).toString());
>>> }
>>>
>>>
>>> and we changed de set(...) to setString(..) and its look like:
>>>
>>>
>>> public void setBigDecimal(int parameterIndex, BigDecimal x) throws
>>> SQLException {
>>> if (x == null)
>>> setNull(parameterIndex, Types.OTHER);
>>> else
>>> setString(parameterIndex, x.toString());
>>> }
>>>
>>>
>>> public void setLong(int parameterIndex, long x) throws SQLException {
>>> setString(parameterIndex, (new Long(x)).toString());
>>>
>>> }
>>>
>>> With this change when we use the setBigdecimal or the setLong in a
>>> query and we expect that a index will be used, it will really be
>>> used. :)
>>>
>>> This has been working in a production database for couple of month
>>> and is really working fine.
>>>
>>>
>>> Regards.
>>>
>>> João Paulo Ribeiro & Marco Leal
>>>
>>>
>>
>>
>>
>
>

- --
- ----------------------------------------------------------------------------
MobiComp - Mobile Computing & Wireless Solutions
phone: +351 253 305 250 fax: +351 253 305 251
web: http://www.mobicomp.com
- ----------------------------------------------------------------------------

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/XfXjbwM7R+C+9B0RAlepAKDF11Yldz95snv58Ac7vj6bu15xYQCgzLWB
ia3iLpA+jwiP3FOaIHuDSd8=
=XsMs
-----END PGP SIGNATURE-----