plperl and/or insert trigger problem

["Bart Degryse" <Bart.Degryse@xxxxxxxxxxxx>]

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
 
This is the target table definition in PostgreSQL
CREATE TABLE "public"."afh_test" (
  "addrformat" VARCHAR(10) NOT NULL,
  "name" VARCHAR(30) NOT NULL,
  "dataareaid" VARCHAR(3) NOT NULL,
  "recid" NUMERIC(10,0) NOT NULL
) WITHOUT OIDS;
 
CREATE UNIQUE INDEX "afh_test_idx" ON "public"."afh_test"
  USING btree ("addrformat", "dataareaid");

 

The source table definition is the same as the target table definition, though the syntax differs slightly:
CREATE TABLE ADDRESSFORMATHEADING (
  ADDRFORMAT VARCHAR2(10 BYTE) DEFAULT '.' NOT NULL,
  NAME VARCHAR2(30 BYTE) DEFAULT '.' NOT NULL,
  DATAAREAID VARCHAR2(3 BYTE) DEFAULT 'dat' NOT NULL,
  RECID NUMBER(10) NOT NULL
)

 

This is the data in Oracle:
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'This country', 'ash', 30);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('be', 'Address Belgium', 'lil', 501);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('fr', 'Address France', 'lil', 496);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('it', 'Italie', 'bol', 3138);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'National', '012', 687181679);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'International countries', 'ash', 29);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('be', 'Beglie', 'bol', 3187);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('sp', 'Address Spain', 'bar', 1302174);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'International countries', 'as0', 29);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'This country', 'as0', 30);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'National', '011', 216774985);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'International', '011', 216774984);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'National', 'hlm', 451094066);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'International', 'hlm', 451094067);

 

This is the trigger I added to the target table. Like it's written here records with a dataareaid that doesn't trigger an error is not inserted. By using RETURN NEW; in the trigger they do get inserted, but that doesn't change the fact that I only get one type of error message instead of two.
CREATE TRIGGER "afh_test_tr" BEFORE INSERT
ON "public"."afh_test" FOR EACH ROW
EXECUTE PROCEDURE "public"."temp_func1"();
 
CREATE OR REPLACE FUNCTION "public"."temp_func1" () RETURNS trigger AS
$body$
BEGIN
  IF NEW.dataareaid = 'lil' THEN
    RAISE EXCEPTION '% foutje %', NEW.dataareaid, NEW.name;
  elsIF NEW.dataareaid = 'bol' THEN
    RAISE EXCEPTION '% nog een foutje %', NEW.dataareaid, NEW.name;
  END IF;
  RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
 
This is the function that retrieves the Oracle data and inserts it in the target table
CREATE OR REPLACE FUNCTION "public"."dbi_insert3" () RETURNS integer AS
$body$
  use DBI;
  $query = 'SELECT * FROM AddressFormatHeading';
  $target = 'INSERT INTO afh_test (addrformat, name, dataareaid, recid) VALUES (?,?,?,?)';
 
  my $dbh_ora = DBI->connect('dbi:Oracle:database=bmssa;host=firev120-1.indicator.be;sid=mars', 'bmssa', '8QD6ibmD')
                or die "Couldn't connect to database: " . DBI->errstr;
  my $dbh_pg = DBI->connect('dbi:Pg:dbname=defrevdev;host=10.100.1.21;port=2345', 'defrevsys', 'Y2I6vbEW')
               or die "Couldn't connect to database: " . DBI->errstr;
 
  my $sel = $dbh_ora->prepare($query)
            or elog(ERROR, "Couldn't prepare statement: " . $dbh_ora->errstr);
  $sel->execute;
  my $ins = $dbh_pg->prepare($target)
            or elog(ERROR, "Couldn't prepare statement: " . $dbh_pg->errstr);
  my $fetch_tuple_sub = sub { $sel->fetchrow_arrayref };
  my @tuple_status;
  my $rc = $ins->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
  if (DBI->err) {
    elog(INFO, DBI->errstr."\n");
    my @errors = grep { ref $_ } @tuple_status;
    foreach my $error (@errors) {
      elog(INFO, $error->[1]);
    }
  }
  $dbh_ora->disconnect;
  $dbh_pg->disconnect;
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
 
And this ... well you can guess...
select dbi_insert3();
 
Does anyone have an idea what's going on? Am I doing something wrong or might this be a bug?

 

Thanks,
Bart