Thread: numeric type

On Sun, 20 Jul 2008, Peter wrote:

> For numeric types (with no explicit scale and precision) JDBC returns 0 for
> both precision and scale (ResultSetMetaData.getPrecision and getScale
> methods). This is breaking my app and IMO does not reflect true state of
> things since Postgres docs state: "NUMERIC without any precision or scale
> creates a column in which numeric values of any precision and scale can be
> stored, up to the implementation limit on precision".
>
> Shouldn't PG JDBC driver return maximum possible values for precision and
> scale in such cases?
>

There are a couple of issues here:

1) While PG supports larger numeric values, the maximum precision you can
constrain it to is 1000.

jurka=# create temp table tt (a numeric (1001, 1));
ERROR:  NUMERIC precision 1001 must be between 1 and 1000

So do you return 1000 as the maximum precision because that's the maximum
that they can pass into create table even if they might actually receive a
value back that has a higher precision?  Or do you return something larger
(the internal pg limit which I don't know offhand) even though they can't
create a table column with that limit?

2) Once you've set the maximum precision, how do you set the maximum
scale as the scale is a dependent on the precision.  Is it (1000,500) ?
(1000, 0)? (1000, 1000)?

What we do now certainly isn't all that great, but I haven't heard a
concrete suggestion on what's better.

Kris Jurka

>> For numeric types (with no explicit scale and precision) JDBC returns 0
for
>> both precision and scale (ResultSetMetaData.getPrecision and getScale
>> methods). This is breaking my app and IMO does not reflect true state of
>> things since Postgres docs state: "NUMERIC without any precision or scale
>> creates a column in which numeric values of any precision and scale can
be
>> stored, up to the implementation limit on precision".
>>
>> Shouldn't PG JDBC driver return maximum possible values for precision and
>> scale in such cases?
>>
>
> There are a couple of issues here:
>
> 1) While PG supports larger numeric values, the maximum precision you can
> constrain it to is 1000.
>
> jurka=# create temp table tt (a numeric (1001, 1));
> ERROR:  NUMERIC precision 1001 must be between 1 and 1000
>
> So do you return 1000 as the maximum precision because that's the maximum
> that they can pass into create table even if they might actually receive a

> value back that has a higher precision?  Or do you return something larger

> (the internal pg limit which I don't know offhand) even though they can't
> create a table column with that limit?


> What we do now certainly isn't all that great, but I haven't heard a
> concrete suggestion on what's better.

OK, I hear ya. How about allowing user to set the default precision and
scale values for "numeric" type? Not sure how this would be done in JDBC
world but should be doable.

IMO a reasonable default maxing out both precision and scale (1000,500)
should work for most applications. Numeric type is not all that common
anyway.

Peter

On Tue, 29 Jul 2008, Peter wrote:

> OK, I hear ya. How about allowing user to set the default precision and
> scale values for "numeric" type? Not sure how this would be done in JDBC
> world but should be doable.

You could add URL parameters like unspecifiedNumericPrecision=1000 and
unspecificedNumericScale=500, but the tradeoff is how much work is it to
add these options to the URL parsing code and then pass them around, add
it to the datasource implementation, add it to the documentation, and ...

If there was some big important app that everyone wanted to use that blew
up on this I'd bet we'd do it, but I haven't heard a whole lot of
complaints about this problem.

Is this an important issue for other people?

Kris Jurka

> > OK, I hear ya. How about allowing user to set the default precision and
> > scale values for "numeric" type? Not sure how this would be done in JDBC
> > world but should be doable.
>
> You could add URL parameters like unspecifiedNumericPrecision=1000 and
> unspecificedNumericScale=500, but the tradeoff is how much work is it to
> add these options to the URL parsing code and then pass them around, add
> it to the datasource implementation, add it to the documentation, and ...
>
> If there was some big important app that everyone wanted to use that blew
> up on this I'd bet we'd do it, but I haven't heard a whole lot of
> complaints about this problem.

I would assume this is not a major issue for most. Personally I ran into
this problem when writing automated PG->MS Access converter (Jackcess is
the only lib capable of creating MDB files on Linux/BSD) and it bailed
on numeric fields.

Peter

Hello Peter,<br /><br /> Peter Zeltins schrieb: <blockquote cite="mid:1217366613.5193.11.camel@peter-laptop"
type="cite"><prewrap="">
 
I would assume this is not a major issue for most. Personally I ran into
this problem when writing automated PG->MS Access converter (Jackcess is
the only lib capable of creating MDB files on Linux/BSD) and it bailed
on numeric fields.  </pre></blockquote> Great gods, why would anyone do this in the first place? :) I once wrote one
forthe opposite direction, but please tell us, which requirements needs you to convert a postgres database to MS
Access?<br/><br /> With best regards,<br /> Daniel Migowski<br /><div class="moz-signature">-- <br /><pre> |¯¯|¯¯|
<b>IKOfficeGmbH             Daniel Migowski</b>|  |  |/|                            Mail: <a
href="mailto:dmigowski@ikoffice.de">dmigowski@ikoffice.de</a>| | // |  Nordstr. 10               Tel.: 0441 21 98 89
52| | \\ |  26135 Oldenburg           Fax.: 0441 21 98 89 55|__|__|\|  <a
href="http://www.ikoffice.de">http://www.ikoffice.de</a>   Mob.: 0176 22 31 20 76</pre></div> 

Hello Peter,

Peter schrieb:
>
>
>
> Long story. We’re migrating old Access app to Postgres but still need
> to be able to exchange datasets with the old app users (app supports
> it’s own import/export in MDB format). Since migration is expected to
> last several years we need some sort of automated PG->MDB thing…
>
Why don't you just make you converter configurable on how it handles
decimal without specs?

>> Long story. We're migrating old Access app to Postgres but still need
>> to be able to exchange datasets with the old app users (app supports
>> it's own import/export in MDB format). Since migration is expected to
>> last several years we need some sort of automated PG->MDB thing.
>>
>Why don't you just make you converter configurable on how it handles
>decimal without specs?

I would need to hack Jackcess library in order to do that... besides it does
not seem the proper way to do it, more like an ugly hack. getPrecision and
getScale are supposed to return the true precision and scale after all...

Peter

Peter написав(ла):
>>> Long story. We're migrating old Access app to Postgres but still need
>>> to be able to exchange datasets with the old app users (app supports
>>> it's own import/export in MDB format). Since migration is expected to
>>> last several years we need some sort of automated PG->MDB thing.
>>>
>>>
>> Why don't you just make you converter configurable on how it handles
>> decimal without specs?
>>
>
> I would need to hack Jackcess library in order to do that... besides it does
> not seem the proper way to do it, more like an ugly hack. getPrecision and
> getScale are supposed to return the true precision and scale after all...
>
> Peter
>
>
It may be easier to write a wrapper over JDBC driver doing needed
conversion. With Java Proxy it is not a complex task.
It may look much like the next:
public class DelegatingHandler<TP> implements InvocationHandler {
    protected final TP parent;

    public DelegatingHandler(TP parent) {
        this.parent = parent;
    }

    public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {
            Method override =
this.getClass().getMethod(method.getName(), method.getParameterTypes());
            if (override != null)
                return override.invoke(this, args);
            return method.invoke(parent, args);
        }
}

public class ConnectionWrapper extends DelegatingHandler<Connection> {

    public static Connection makeConnectionWrapper(Connection connection) {
        return (Connection)
Proxy.newProxyInstance(ConnectionWrapper.class.getClassLoader(),
            new Class[]{Connection.class}, new
ConnectionWrapper(connection));
    }
...
    public PreparedStatement prepareStatement(String sql) throws
SQLException {
        return makePreparedStatementWrapper(parent.prepareStatement(sql));
    }
...
}


You simply create a wrapper and "override" needed methods by making
methods in wrapper with exactly same name and signature, calling parent
instead of super when needed.