Re: strange commit behavior

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