Обсуждение: My problems with PostgreSQL

Hello everybody,
I'm new in this list and at beging I want thanks to all
members of postgresql community for stuff you made.
Pretty amazing piece of work. After playing around
with postgresql I found that's right time to thing about
using it for real project. When I was trying switch our
JDBC based project from Oracle (SQL92 compliant code
- no Oracle extensions) to PostgreSQL I found following
obstacles:
1. SQLException is fired back when query result set is empty.
Is this right behaviour ???

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

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 ?!?)");
    }
}

Then simillar scenario via 'psql' tool:
test=# begin;
BEGIN
test=# insert into aa values (23);
ERROR:  Relation "aa" does not exist
test=# create table aa (aa int8);
NOTICE:  current transaction is aborted, queries ignored until end of
transaction block
*ABORT STATE*

HUH, what is ABORT STATE - I can't make wrong statement ???!!!!???
Why it's necessary abort whole transaction ???!!!???

I'm very experienced Java programmer and experienced C/C++ programmer
(or I was 3 years ago but you can't forget bicycling ;-) now pure Java ).
I'm interested to help you with development (this problem can't stop me) but
I'm very new in postgres and I need help.
Can I turn off this behaviour ?
Is't here some workaround ?
If not, know somebody where to look at first (which part of source code) ?

Thanks for your time and best regards

Pavel

Pavel,

I will try to answer what I can, hopefully some more experience people
can chime in with their opinions

1) If the result set is empty you should find out when you do a
ResultSet.next() I'm not sure how you are getting the Exception
2) There is a numeric data type which is bigger. How big do you want it,
int8 is pretty big!
3) Once a transaction has gone astray it needs to be rolled back, or
ended, you cannot continue to do inserts on it. This only makes sense
from my POV. The idea is that a transaction should be atomic, and if
anything interrupts the transaction it should be dealt with.

Dave

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Pavel Tavoda
Sent: Tuesday, February 12, 2002 9:25 AM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] My problems with PostgreSQL


Hello everybody,
I'm new in this list and at beging I want thanks to all
members of postgresql community for stuff you made.
Pretty amazing piece of work. After playing around
with postgresql I found that's right time to thing about
using it for real project. When I was trying switch our
JDBC based project from Oracle (SQL92 compliant code
- no Oracle extensions) to PostgreSQL I found following
obstacles:
1. SQLException is fired back when query result set is empty. Is this
right behaviour ???

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

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 ?!?)");
    }
}

Then simillar scenario via 'psql' tool:
test=# begin;
BEGIN
test=# insert into aa values (23);
ERROR:  Relation "aa" does not exist
test=# create table aa (aa int8);
NOTICE:  current transaction is aborted, queries ignored until end of
transaction block
*ABORT STATE*

HUH, what is ABORT STATE - I can't make wrong statement ???!!!!??? Why
it's necessary abort whole transaction ???!!!???

I'm very experienced Java programmer and experienced C/C++ programmer
(or I was 3 years ago but you can't forget bicycling ;-) now pure Java
). I'm interested to help you with development (this problem can't stop
me) but I'm very new in postgres and I need help. Can I turn off this
behaviour ? Is't here some workaround ? If not, know somebody where to
look at first (which part of source code) ?

Thanks for your time and best regards

Pavel


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

I converted all my oracle numbers to decimal. The precision
of none or zero gives me an integer. This may be a novice
way of doing things but I needed to get a demo up and
running. The decimal number seems to work for my sequence
as well. Can anyone shed some light on the problems with this
approach as I would be glad to change this 'partial hack'. Do
I need to use int8 or something like that. I was used to using
number (X,Y) in Oracle where Y=0 for an integer.

At 03:24 PM 2/12/2002 +0100, Pavel Tavoda wrote:
>Hello everybody,
>I'm new in this list and at beging I want thanks to all
>members of postgresql community for stuff you made.
>Pretty amazing piece of work. After playing around
>with postgresql I found that's right time to thing about
>using it for real project. When I was trying switch our
>JDBC based project from Oracle (SQL92 compliant code
>- no Oracle extensions) to PostgreSQL I found following
>obstacles:
>1. SQLException is fired back when query result set is empty.
>Is this right behaviour ???
>
>2. Datatypes
>NUMBER isn't supported
>Is't here bigger int type than int8 ???
>
>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 ?!?)");
>    }
>}
>
>Then simillar scenario via 'psql' tool:
>test=# begin;
>BEGIN
>test=# insert into aa values (23);
>ERROR:  Relation "aa" does not exist
>test=# create table aa (aa int8);
>NOTICE:  current transaction is aborted, queries ignored until end of
>transaction block
>*ABORT STATE*
>
>HUH, what is ABORT STATE - I can't make wrong statement ???!!!!???
>Why it's necessary abort whole transaction ???!!!???
>
>I'm very experienced Java programmer and experienced C/C++ programmer
>(or I was 3 years ago but you can't forget bicycling ;-) now pure Java ).
>I'm interested to help you with development (this problem can't stop me) but
>I'm very new in postgres and I need help.
>Can I turn off this behaviour ?
>Is't here some workaround ?
>If not, know somebody where to look at first (which part of source code) ?
>
>Thanks for your time and best regards
>
>Pavel
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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