Re: My problems with PostgreSQL - Mailing list pgsql-jdbc

Pavel,

Pavel Tavoda wrote:
> 1. SQLException is fired back when query result set is empty.
> Is this right behaviour ???

This is most likely due to the fact that you haven't called
ResultSet.next() (or failed to check the result of the call), before
trying to access the resultset.  If this is not the case, please post a
test case that reproduces the problem, and someone will look at it.

>
> 2. Datatypes
> NUMBER isn't supported
> Is't here bigger int type than int8 ???

Oracle really only has one numeric datatype - NUMBER.  All other numeric
types Oracle supports are really just aliases for NUMBER.  In postgres
the equivalent datatype is DECIMAL or NUMERIC (they are really the same
thing in postgres).  So you could replace all of your Oracle NUMBER
columns with DECIMAL and you should be all set.  However, if you are
just storing integer data, then you might think about using the INTEGER
or INT8 datatypes in postgres as these are more efficient for storing
integer data than the generic DECIMAL type is.

>
> 3. "*ABORT STATE*" problem.
> I started our server engine against PostgresSQL. From
> generated logs it's was looking good but then I found
> BIIIIG problem. I'm doing following scenario (it's
> pseudocode not real code), I hope it's self explaining:
>
> insertStatement='insert into aa ....';
> try {
>    dbConn.executeUpdate(insertStatement);
> } catch (SQLException e1) {
>    try {
>        log.info("Some error when inserting into table -> trying create
> table");
>        dbConn.executeUpdate('create table aa ....');
>
>        log.info("Reexecute insert statement");
>        dbConn.executeUpdate(insertStatement);
>
>    } catch(SQLException e2) {
>        log.error("Some real DB error (wrong schema ?!?)");
>    }
> }
>

This is the way postgres works.  Any error aborts the transaction.  An
explicit rollback is needed before additional sql statements can be
processed.  While this can be a pain, if you are used to how Oracle does
things, you can generally work around the differences between Oracle and
postgres in this area.  For example your code above could be changed to:

insertStatement='insert into aa ....';
try {
    dbConn.executeUpdate(insertStatement);
} catch (SQLException e1) {
    try {
        //rollback so we can start a new transaction
        dbConn.rollback();

        log.info("Some error when inserting into table -> trying create
table");
        dbConn.executeUpdate('create table aa ....');

        log.info("Reexecute insert statement");
        dbConn.executeUpdate(insertStatement);

    } catch(SQLException e2) {
        log.error("Some real DB error (wrong schema ?!?)");
    }
}


Note that if you had other work done before the insert here that would
also be rolled back and the try block would need to redo everything you
wanted not just the one insert.  However if this is true (i.e. you have
other inserts/updates done before this insert) your code under Oracle is
wrong as well.  This is because a DDL statement in Oracle (i.e. your
create table in this example) causes an implicit commit to occur.  This
means that the work before the first insert will get committed when you
issue the create table statement in the try block.  If then later either
the second try at the insert fails, or something fails latter on and you
really do want to rollback, you will only rollback the changes after the
create table, because those before the create table are already
committed.  It is generally a bad idea to mix DDL and DML statements in
a transaction in Oracle because of this.

thanks,
--Barry