Thread: strange commit behavior

Hi all,

I developed a little tool in Java that updates databases throught text
files.

In this tool there is an option that allows the user accepts a defined
amount of errors and save the well formed data.

To do this I start commitment control when the process begins and, at the
end, if the thershold is not reached or there are no errors I commit data,
else rollback.

I tested this tool under MySql and Oracle and everything went as expected.

Unfortunately postgres seems to work in a different way, cause if there is
just one error while the transaction is active I'm not able to commit the
well formed data in the db, no matter if the good records were inserted
sooner or later the error.

Does this behavior appears right for postgres ?

There is any way or workaround to achieve my goal ?

Any hint would be appreciated.

Flavio


-----------------------------------------------------------
Il presente messaggio non costituisce un impegno contrattuale tra SILMA S.r.l. ed il destinatario.
Le opinioni ivi espresse sono quelle dell'autore.
SILMA S.r.l. non assume alcuna responsabilita riguardo al contenuto del presente messaggio.
Il messaggio è destinato esclusivamente al destinatario.
Il contenuto e gli allegati sono da considerarsi di natura confidenziale

Nel caso abbiate ricevuto il presente messaggio per errore siete pregati di comunicarlo
alla casella segreteria@silmasoftware.com.

Flavio Palumbo, 18.11.2008 10:01:
> I tested this tool under MySql and Oracle and everything went as expected.
>
> Unfortunately postgres seems to work in a different way, cause if there is
> just one error while the transaction is active I'm not able to commit the
> well formed data in the db, no matter if the good records were inserted
> sooner or later the error.
>
> Does this behavior appears right for postgres ?

Yes this is the way Postgres works (been there as well)

> There is any way or workaround to achieve my goal ?

You need to wrap each UPDATE/INSERT statement with a Savepoint

Savepoint sp = connection.setSavepoint()
try
{
  // do your insert/update/delete here
  connection.release(sp);
}
catch (SQLException sql)
{
  connection.rollback(sp);
}

Thomas

Flavio Palumbo wrote:
> I developed a little tool in Java that updates databases throught text
> files.
>
> In this tool there is an option that allows the user accepts a defined
> amount of errors and save the well formed data.
>
> To do this I start commitment control when the process begins
> and, at the
> end, if the thershold is not reached or there are no errors I
> commit data,
> else rollback.
>
> I tested this tool under MySql and Oracle and everything went
> as expected.
>
> Unfortunately postgres seems to work in a different way,
> cause if there is
> just one error while the transaction is active I'm not able
> to commit the
> well formed data in the db, no matter if the good records
> were inserted
> sooner or later the error.
>
> Does this behavior appears right for postgres ?
>
> There is any way or workaround to achieve my goal ?

You use savepoints for this:
http://www.postgresql.org/docs/8.3/static/tutorial-transactions.html
http://www.postgresql.org/docs/current/static/sql-savepoint.html

Yours,
Laurenz Albe

Flavio Palumbo wrote:

> Unfortunately postgres seems to work in a different way, cause if there is
> just one error while the transaction is active I'm not able to commit the
> well formed data in the db, no matter if the good records were inserted
> sooner or later the error.

Yes, that's right. As soon as something goes wrong in the transaction
it's assumed that, unless you handle the error, the transaction is bad
and shouldn't be committed.

You can use savepoints (see the manual) to recover from errors, though
they have some issues if you use hundreds of thousands of savepoints in
a single transaction. It's much better to check the data on INSERT to
make sure it's OK. You can do this with an INSERT ... SELECT (and check
the rowcount), with a PL/PgSQL function that returns a result value, do
it client-side, etc.

--
Craig Ringer

Hi Thomas,

I wrote the following test case that seems to work fine.

Any suggestion ?

Thanks a lot

Flavio


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Savepoint;

public class TestCommitPostgres {

    public TestCommitPostgres() {
        java.io.BufferedReader br = null;
        Connection con = null;
        try {
            Class.forName("org.postgresql.Driver").newInstance();
            con =
java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/postg
res", "user", "pass");
            PreparedStatement ps = con.prepareStatement("insert into
public.test1 values(?,?,?,?)");

            java.io.FileReader fr = new
java.io.FileReader("test2_postgres.txt");
            br = new java.io.BufferedReader(fr);
            con.setAutoCommit(false);


            while (true) {
                String s = br.readLine();
                if (s == null) {
                    break;
                }
                String[] sch = s.split("::");
                if (sch.length != 4) {
                    continue;
                }
                Savepoint sp = con.setSavepoint();
                try {
                    ps.setString(1, sch[0]);
                    ps.setString(2, sch[1]);
                    ps.setDouble(3, new Double(sch[2]).doubleValue());
                    ps.setInt(4, new Integer(sch[3]).intValue());
                    ps.executeUpdate();
                    con.releaseSavepoint(sp);
                } catch (Throwable t) {
                    con.rollback(sp);
                }
            }

        } catch (Throwable t) {
            System.out.println("errore - " + t);
        }
        try {
            if (con != null) {
                con.commit();
                con.setAutoCommit(true);
                con.close();
            }
            if (br != null) {
                br.close();
            }
        } catch (Throwable t) {
        }
    }

    public static void main(String[] args) {
        new TestCommitPostgres();
        System.exit(0);
    }
}
-------------------------------------------- test1 table
CREATE TABLE test1
(
  testfield1 varchar(40),
  testfield2 varchar(40),
  testfield3 numeric(30,10),
  chiave numeric(7) NOT NULL,
  CONSTRAINT test1_pkey PRIMARY KEY (chiave)
)
WITHOUT OIDS;
ALTER TABLE test1 OWNER TO postgres;
-------------------------------------------- test2_postgres.txt
aa::bb::1::71::
aa1::bb1::11::70::
aa2::bb2::12::75::
aa3::bb2::13::77::
aa2::bb2::12::75::
aa2::bb2::12::78::
aa2::bb2::12::71::

Flavio Palumbo, 18.11.2008 10:01:
> I tested this tool under MySql and Oracle and everything went as expected.
>
> Unfortunately postgres seems to work in a different way, cause if there is
> just one error while the transaction is active I'm not able to commit the
> well formed data in the db, no matter if the good records were inserted
> sooner or later the error.
>
> Does this behavior appears right for postgres ?

Yes this is the way Postgres works (been there as well)

> There is any way or workaround to achieve my goal ?

You need to wrap each UPDATE/INSERT statement with a Savepoint

Savepoint sp = connection.setSavepoint()
try
{
  // do your insert/update/delete here
  connection.release(sp);
}
catch (SQLException sql)
{
  connection.rollback(sp);
}

Thomas


-----------------------------------------------------------
Il presente messaggio non costituisce un impegno contrattuale tra SILMA S.r.l. ed il destinatario.
Le opinioni ivi espresse sono quelle dell'autore.
SILMA S.r.l. non assume alcuna responsabilita riguardo al contenuto del presente messaggio.
Il messaggio è destinato esclusivamente al destinatario.
Il contenuto e gli allegati sono da considerarsi di natura confidenziale

Nel caso abbiate ricevuto il presente messaggio per errore siete pregati di comunicarlo
alla casella segreteria@silmasoftware.com.