Thread: Commit strange 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 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.
>
> Does this behavior appears right for postgres ?

Yes. Either everything in a transaction works or nothing does. That is
the nature of a transaction. Some systems weaken this rule to allow
certain types of error but not others, but PostgreSQL just says
all-or-nothing.

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

You could look into savepoints, that allows you to rollback the
transaction to a known point and then continue from there. So:

BEGIN;
SAVEPOINT foo;
...insert 1000 rows...
SAVEPOINT foo; -- now we can rollback to here
...insert 1000 rows...
SAVEPOINT foo; -- now we can rollback to here
...insert 173 rows, get an error...
ROLLBACK TO SAVEPOINT foo;
...insert the 172 rows that worked, skip the next one, and continue
...
if number of errors < threshold then COMMIT else ROLLBACK

What number of rows to put in a block (e.g. 1000) will depend on how
many errors you expect to have. One common tactic is to start off small
(say 100) and increase the block size for every block that was OK, and
decrease the block size every time you hit an error.

HTH
--
  Richard Huxton
  Archonet Ltd