Philip Yarra wrote:
> I always assumed what Dave just said, but porting from Oracle & Sybase
> to PostgreSQL, we ran into exactly the same issue - we also solved it
> with savepoints. However, I threw together the attached sample app to
> test *precisely* what ends up in the database when auto-commit is off.
> For the impatient, it sets auto-commit off, and tries to insert 3 rows.
> The first succeeds, the second violates a unique index, so fails, and
> the third is issued after the second, so should also fail. We ignore the
> exceptions, then commit. The results puzzle me somewhat:
>
> Oracle 10g: first and third inserts are in the DB
> Sybase ASE 12.5: first and third inserts are in the DB
> PostgreSQL 8.1.1: first insert is in the DB
>
> Now I agree that Oracle and Sybase have this kind of wrong - the third
> insert should not succeed. However, reading Dave's statement "The
> concept of an atomic transaction means that it must either succeed
> completely or fail completely. PostgreSQL does this." makes me wonder if
> the first insert should be in the DB either? Or am I making some sort of
> mistake here? From my results, it looks more like PostgreSQL's behaviour
> is "Everything up the first failure can be committed" which isn't quite
> the same thing as an indivisible unit of work that succeeds or fails
> completely.
Can we see your testcase? The behaviour you describe is not what I'd expect.
-O
