uDIG

Postgresql OID's can be larger than INT even though JDBC driver casts them as such

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: UDIG 1.1.RC4
  • Fix Version/s: UDIG 1.1.RC5
  • Component/s: database
  • Labels:
    None
  • Environment:
    ALL

Description

With some PostGIS databases I can't access all records

Caused by: org.postgresql.util.PSQLException: Bad value for type int : 2182558298
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2ResultSet.java:2507)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:1994)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.internalGetObject(AbstractJdbc2ResultSet.java:122)
at org.postgresql.jdbc3.AbstractJdbc3ResultSet.internalGetObject(AbstractJdbc3ResultSet.java:39)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getObject(AbstractJdbc2ResultSet.java:2325)
at org.geotools.data.jdbc.QueryData.readFidColumn(QueryData.java:208)

As long as the OID's are small enough to be cast into java INT's everything works well.

A potential resolution is to cast OID's as VARBINARY or LONG

issue might spring forth from geotools
module/main/src/org/geotools/data/jdbc/QueryData.java

public Object readFidColumn(int index) throws IOException {
try

{ return resultSet.getObject(index + 1); }

catch (SQLException e)

{ throw new DataSourceException("Error reading fid column " + index, e); }

}

C.

Issue Links

is related to

Improvement - An improvement or enhancement to an existing feature or task. GEOT-958 Support FID values greater than int

  • Major - Major loss of function.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Cory Horner added a comment -
Carl,

Could you attach your table schema?

Thanks,
Cory.
Show
Cory Horner added a comment - Carl, Could you attach your table schema? Thanks, Cory.
Hide
Permalink
Cory Horner added a comment -
I've submitted a patch to geotools (http://jira.codehaus.org/browse/GEOT-958) which should extend the OID a little bit.

According to http://www.postgresql.org/docs/8.1/static/ddl-system-columns.html OID peaks at 2^32, so there can only be about 4,294,967,295 unique features in an OID-keyed table. Please note that Geotools only uses OID as a last resort, when it cannot find a suitable primary key; to go beyond 4 billion records, one should use a bigserial sequence as the primary key.

Carl: we may need you to test this for us, as we cannot reproduce this issue at the moment.
Show
Cory Horner added a comment - I've submitted a patch to geotools ( http://jira.codehaus.org/browse/GEOT-958 ) which should extend the OID a little bit. According to http://www.postgresql.org/docs/8.1/static/ddl-system-columns.html OID peaks at 2^32, so there can only be about 4,294,967,295 unique features in an OID-keyed table. Please note that Geotools only uses OID as a last resort, when it cannot find a suitable primary key; to go beyond 4 billion records, one should use a bigserial sequence as the primary key. Carl: we may need you to test this for us, as we cannot reproduce this issue at the moment.
Hide
Permalink
Cory Horner added a comment -
applied fixes to geotools -- untested, but likely fixed.
Show
Cory Horner added a comment - applied fixes to geotools -- untested, but likely fixed.

People

  • Assignee:
    Cory Horner
    Reporter:
    carl anderson
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: