Thread: Aborting transaction on error

Aborting transaction on error

From
Mark Rae
Date:
Hi,

Why does postgres abort the current transaction on error?
Is this actually required ANSI-SQL behaviour, or just a postgres
peculiarity.

More specifically, I have some DBI scripts which under Oracle
just insert rows into a table with a unique constraint. If they
come across a duplicate row they detect and report this error and
continue. (The duplicates are discarded as they are not needed)

However in postgres, it automatically aborts the transaction, so is
checking for the existance for a duplicate row beforehand the only way
to avoid this?

    -Mark

--
Mark Rae                                       Tel: +44(0)20 7074 4648
Inpharmatica                                   Fax: +44(0)20 7074 4700
m.rae@inpharmatica.co.uk                http://www.inpharmatica.co.uk/

Re: Aborting transaction on error

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Why does postgres abort the current transaction on error?

That's what it should do. An error means that the current command could
not be completed, thus there is no way the transaction can continue. You
cannot rollback part of a transaction: it is all or nothing within that
transaction. However, I suspect that is not quite what you are asking...

> More specifically, I have some DBI scripts which under Oracle
> just insert rows into a table with a unique constraint. If they
> come across a duplicate row they detect and report this error and
> continue. (The duplicates are discarded as they are not needed)

So basically Oracle is reporting the problem and then moving on to
the next record. If each insert is a separate transaction, this is
perfectly understandable behavior.

> However in postgres, it automatically aborts the transaction, so is
> checking for the existance for a duplicate row beforehand the only way
> to avoid this?

Yes, that is definitely one way. It sounds as if all the inserts are inside
one transaction, thus any problem with insert #50 causes 1-49 to be
invalidated as the entire transaction fails. One way to get around this is
to make every insert its own transaction. Either set AutoCommit to true
when connecting to the database, or issue a COMMIT after each successful
insert. As far as being able to continue after an error is detected (e.g.
violation of a unique constraint) make sure the the RaiseError attribute
is set to false, and have a way to catch the errors yourself.

If time is critical, you can do a bulk check of all your values beforehand,
then load all the good ones as a single transaction, or use the COPY FROM
function for maximum speed. You could even do something such as dropping
the constraint, loading the database, flushing the duplicates, and
reimplementing the constraint.

If time is not that critical, I'd go with the simplest option: setting
AutoCommit to true and letting the duplicates simply fail.

Greg Sabino Mullane  greg@turnstep.com
PGP Key: 0x14964AC8 200203020947

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE8gOa9vJuQZxSWSsgRAsoEAJ9l/WrZJblfGA8HkkhlPtHNwqcGSwCeNS4e
R6NRcBtKUkEq5N+VeiwM2Hw=
=J3qz
-----END PGP SIGNATURE-----



Re: Aborting transaction on error

From
Mark Rae
Date:
Greg Sabino Mullane wrote:
> > More specifically, I have some DBI scripts which under Oracle
> > just insert rows into a table with a unique constraint. If they
> > come across a duplicate row they detect and report this error and
> > continue. (The duplicates are discarded as they are not needed)
>
> So basically Oracle is reporting the problem and then moving on to
> the next record. If each insert is a separate transaction, this is
> perfectly understandable behavior.

No the whole thing is in one transaction, which is what I want so that
either all of the data from a particular file gets loaded or none of
it.

Oracle just reports the 'error' back through DBI::errstr and the
script can choose whether to abort the transaction or not. I was
hoping that there might be a way of making Postgres behave the same
way. i.e. The database just reports what happened and the user gets
to decide what constitutes a teminal error.

I had pretty much resigned myself to adding the duplicate checks,
I just wanted to make sure I had not overlooked an option somewhere
before I did so.

Thanks (also to the people who replied off list)

    -Mark

--
Mark Rae                                       Tel: +44(0)20 7074 4648
Inpharmatica                                   Fax: +44(0)20 7074 4700
m.rae@inpharmatica.co.uk                http://www.inpharmatica.co.uk/