Joern,
Perhaps more detail on your original example would be useful. I know from
working with Oracle that if you feed it an insert with a primary key violation in
the middle of a transaction, it will abort the transaction and rollback, just
like PostgreSQL. As your original example was provided, it didn't really seem to
be a PostgreSQL issue. In your new example, if you put everything in an eval
loop and have a commit after each insert then it should work ok:
eval {
insert...
commit
};
if ($@) {
check for type of error
do update if appropriate
}
But again this doesn't seem to be really different in PostgreSQL and other DBs
with transaction control. That is, it isn't a matter of "tricking" PostgreSQL,
because Oracle and other DBs will also rollback a transaction when they hit SQL
that has a problem. However, maybe there is more going on in your code than I
can see from your example -- perhaps if you provide a small version of the exact
code that was working in Oracle and not in PostgreSQL, maybe somebody will be
able to suggest something. Good luck in any event,
Rob
Joern Reder wrote:
> Rob Messer wrote:
>
> > This is really how transactions are supposed to work -- to maintain
> > database integrity, in a transaction either everything should go through
> > or nothing. It would work the same way in Oracle and other databases
> > with transaction control.
>
> Here I fully contradict. Yes, in a transaction either everything should
> go through or nothing, but it should be my decision what "everything"
> is. The example I wrote works inside our application with Oracle,
> Informix, Sybase Enterprise Server and Sybase SQL Anywhere without any
> problems since years.
>
> > One solution would seem be to do checking
> > before the insert to make sure you are not inserting duplicate keys.
>
> But if it would work, as it should (in my opinion and like it does in
> any other dbms I know), I can write the following code, which is much
> more elegant and more effective than explicite checking of conditions:
>
> begin work
>
> update table y
> insert into table z
>
> try
> insert into table x
> commit
>
> catch pk_constraint_violation
> update table x
> commit
>
> catch the rest
> rollback
> print error message
>
> And this contradicts not the idea of a transaction. It's the programmers
> choice, when the transaction is committed and when it is rollbacked. And
> using exception handling this way leads to very nice and readable code,
> whereas using nested if() conditions is bad code in my eyes.
>
> > Or you could perhaps break the transaction into smaller parts and then just
> > ignore the errors if you don't care about them. In any event PostgreSQL
> > seems to be doing what it should be doing here.
>
> Ok, to use your words ;) Is it possible to trick PostgreSQL into doing
> what it not should do: not aborting the transcation?
>
> Thanks,
>
> Joern
>
> --
> Joern Reder -- Software Development, dimedis GmbH, 50672 Koeln
> http://www.dimedis.de/ http://spirit.dimedis.de/
> supporting: http://www.zyn.de/ http://www.netcologne.de/~nc-joernre/
> CPAN: http://www.perl.com/CPAN/modules/by-module/CIPP/JRED/
