On Tue, Feb 24, 2004 at 03:25:09PM -0500, Andrew Pimlott wrote:
> I am using DBD::Pg, and I noticed that after a statement causes an
> error, subsequent statements (until rollback) fail with "current
> transaction is aborted, queries ignored until end of transaction". I
> found some postgres documentation[1] suggesting that it is impossible to
> continue after an error. Is this true for other drivers, and is there
> an official DBI position on this? (I couldn't find any mention in the
> DBI documentation.)
The official DBI position is that the postgres behaviour seems silly.
An error in a *statement* should rollback the effect of *just that
statement* as if it never happened. You should them be able to
continue within the same transaction doing whatever you want as if
you've never even tried executing the statement that failed. Then
finally you can call commit or rollback on the whole lot.
> It seems somewhat unfortunate that I can't (within a transaction) do
> something like
>
> $sth = sth("delete from users where user_id = ?");
> eval { $sth->execute($id) };
> return unless $@;
>
> # must have been a referential integrity violation
> $sth = sth("update users set disabled = true where user_id = ?");
> $sth->execute($id);
Would work fine on Oracle, for example.
> Does anyone have suggestions for accomplishing something similar, all
> within one transaction? I don't know of any way to check for
> referential integrity violations without actually trying to delete the
> thing. If I can't come up with a better solution, I guess I'll just
> set disabled and forget about trying to delete.
Or set disabled, commit, then try to delete and commit.
Tim.
