Skip site navigation (1) Skip section navigation (2)

BUG #3368: plperl error handling fails

From: "Bart Degryse" <bart(dot)degryse(at)indicator(dot)be>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3368: plperl error handling fails
Date: 2007-06-06 07:57:55
Message-ID: 200706060757.l567vtZi028748@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3368
Logged by:          Bart Degryse
Email address:      bart(dot)degryse(at)indicator(dot)be
PostgreSQL version: 8.2.4
Operating system:   Windows XP Professional
Description:        plperl error handling fails
Details: 

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=mar
s', '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, \(at)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();
 
I posted the problem on the pgsql-sql forum but nobody seemed to be able to
help me out. Since I have a feeling that this problem might be a bug I'm now
posting it here. In case it's just me doing something stupid (in which case
I appologize for bothering you) I would appreciate to know what I've done
wrong.

Thanks,
Bart

pgsql-bugs by date

Next:From: Herve BoulouisDate: 2007-06-06 09:27:28
Subject: Re: BUG #3362: xlog corruption just after initdb on irix
Previous:From: Heikki LinnakangasDate: 2007-06-06 07:24:09
Subject: Re: BUG #3362: xlog corruption just after initdb on irix

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group