Обсуждение: plperl and/or insert trigger problem

Bart Degryse wrote:
> I'm writing a function that fetches data in an Oracle database and stores it in postgresql database.
>
> The fetching from Oracle and the inserting in PostgreSQL both work correctly. I know this because with an empty
targettable and without an insert trigger the source data gets 'copied' perfectly to the target table. 
>
> Of course the target won't always be empty. So the unique index might cause some inserts to fail. So I wanted to put
someerror handling in my function. To test what kind of information I would get I added an insert trigger to the target
table.This trigger raises to different errors based on the data inserted: if dataareaid is 'lil' an error is raised, if
dataareaid= 'bol' another error is raised, other values for dataareaid don't raise an error. 
> So the plperl function should receive these error messages and handle them appropriatly. For this test the function
reportsthem just as info to the screen. 
>
> When I 'manually' (without using the function and without interference of Oracle) insert a record that should trigger
theerror raising, the correct error is raised. When I use the function (see below) but change the query that fetches
theOracle data so that only one type of error is triggered (eg "SELECT * FROM AddressFormatHeading WHERE dataareaid =
'lil'"or "SELECT * FROM AddressFormatHeading WHERE dataareaid = 'bol'") the correct error message is passed from the
triggerto my function. 
> However when both error triggering dataareaids exist in the set of data fetched from Oracle only one type of error
messageseems to get passed from the trigger to my function. Apparently it is the one that is caused first. Obviously
thatis not what I expect. This is what I get: 
> executing 14 generated 4 errors
> ERROR:  lil foutje Address Belgium
> ERROR:  lil foutje Address Belgium
> ERROR:  lil foutje Address Belgium
> ERROR:  lil foutje Address Belgium
> And this is what I expect to get:
> executing 14 generated 4 errors
> ERROR:  lil foutje Address Belgium
> ERROR:  lil foutje Address France
> ERROR:  bol nog een foutje Italie
> ERROR:  bol nog een foutje Beglie

I don't think this is the cause, but you're using DBI->err|errstr - do
you not want $dbh_pg->err|errstr? Otherwise, you can't identify
different errors on the oracle vs pg connections.

Second - it's not just that your function does the inserts in the
context of a single transaction, is it? That would mean you're just
seeing the original error repeated.

--
   Richard Huxton
   Archonet Ltd

Bart Degryse wrote:
> Using DBI->err was a leftover from earlier testing. $dbh_pg->err is of course better. But it doesn't solve the
problem.
>
> I'm not sure what you mean with your second remark.
> The call to my function ( SELECT dbi_insert3(); ) is one transaction I suppose.
> According to the documentation on execute_for_fetch (http://search.cpan.org/~timb/DBI-1.48/DBI.pm#execute_for_fetch)
however
> an execute is done for every fetched record and @tuple_status should contain the error message associated with each
failedexecute. 

I was wondering if there was a hidden BEGIN...COMMIT sneaking into the
process somewhere - either from execute_for_fetch() or in the context of
using DBI from within plperl. Reading back through, you say that the
"good" rows get inserted, so that can't be the case.

The only other reasons that spring to mind are:
1. A bug in your looping through tuple-status
2. A bug in execute_for_fetch() filling the tuple-status array.

What happens if you elog the whole array (just to get the ref numbers) -
that should show whether DBI is filling the array incorrectly.

--
   Richard Huxton
   Archonet Ltd

Bart Degryse wrote:
> I tend to exclude reason 1: I've dumped the whole array using a debugger and it really contains what I return when
loopingthrough it. 
> As far as I can see it's rather reason 2: execute_for_fetch seems to fill the array incorrectly, that is: it's a
validarray, but the last value added to it also seems to overwrite previously added values. 

Hmm - see below

>
> I seem to have found a workaround but my perl knowledge is too limited to evaluate if it's a good one.

I'm no guru myself...

> When I replace
> my $fetch_tuple_sub = sub { $sel->fetchrow_arrayref };
> by
> my $fetch_tuple_sub = sub {
>   my $ary_ref = $sel->fetchrow_arrayref;
>   print "my method: ".$dbh_pg->errstr."\n" if $dbh_pg->err;
>   return $ary_ref;
>  };
> then the expected exception messages get printed.
> Is this a acceptable way to do it in your opinion?

Looks OK to me, except you'll not get any error message on the last row
- the insert will be called after the fetch.


I've had a quick look at my copy of DBI.pm (Debian Etch - lives in
/usr/lib/perl5/DBI.pm)

Around line 1930, we have the error-handling for execute_for_fetch()

else {
   $err_count++;
   my $err = $sth->err;
   push @$tuple_status, [ $err, $errstr_cache{$err} ||= $sth->errstr,
$sth->state ];
...

Notice how it's taking a copy of the error code ("my $err = ") but not
the error-string? What happens if you change the code to take a copy of
sth->errstr too:

   my ($err,$errstr) = ($sth->err, $sth->errstr);
   push @$tuple_status, [ $err, $errstr_cache{$err} ||= $errstr,
$sth->state];

--
   Richard Huxton
   Archonet Ltd

On Wed, Jun 06, 2007 at 04:44:03PM +0100, Richard Huxton wrote:
> I've had a quick look at my copy of DBI.pm (Debian Etch - lives in
> /usr/lib/perl5/DBI.pm)
>
> Around line 1930, we have the error-handling for execute_for_fetch()
>
> else {
>   $err_count++;
>   my $err = $sth->err;
>   push @$tuple_status, [ $err, $errstr_cache{$err} ||= $sth->errstr,
> $sth->state ];

The reference to erstr_cache seems to infer that the code assumes there
can be only one error string for any particular. Looking at the code I
can't work out why that variable even exists.

I'd say replace that line with:

push @$tuple_status, [ $err, $sth->errstr, $sth->state];

And be done with it...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.