Re: strange commit behavior

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.