Re: 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 target table 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 some error 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 reports them just as info to the screen.
>
> When I 'manually' (without using the function and without interference of Oracle) insert a record that should trigger the error raising, the correct error is raised. When I use the function (see below) but change the query that fetches the Oracle 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 trigger to my function.
> However when both error triggering dataareaids exist in the set of data fetched from Oracle only one type of error message seems to get passed from the trigger to my function. Apparently it is the one that is caused first. Obviously that is 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