BUG REPORT: Incorrect status information withexecute_for_fetch
am 07.06.2007 15:42:59 von Bart.Degryse--=__Part3E191D73.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
According to the perldoc for DBI.pm module version 1.56 (http://search.cpan=
..org/~timb/DBI/DBI.pm#execute_for_fetch):=20
If \@tuple_status is passed then the execute_for_fetch method uses it to =
return status information. The tuple_status array holds one element per =
tuple. (...) If the execute() did fail then the element holds a reference =
to an array containing ($sth->err, $sth->errstr, $sth->state).
As I understand this for each tuple that fails execution @tuple_status =
will hold an array with information on why this particular tuple failed.
In my test scenario (see below) I'm fetching 14 records from Oracle and I =
try to insert them into a PostgreSQL database. The insert trigger on the =
target table will raise an exception when field [dataareaid] contains the =
value 'lil' and another exception when it contains the value 'bol'. Both =
exceptions will thus cause the insert to fail.
Of those 14 records two have 'lil' and two have 'bol' as [dataareaid] =
value. So based on my understanding of the perldoc I expect @tuple_status =
to hold 10 scalars (the return value from the records that executed), two =
arrays holding the lil exception message and two arrays holding the bol =
exception message.
That is however not what I get. In my test scenario @tuple_status holds =
the 10 scalars and 4 arrays holding one of the exception messages (namely =
the one that is encountered first while inserting).
I think the code on line 1931 is causing this behaviour
push @$tuple_status, [ $err, $errstr_cache{$err} ||=3D $sth->errstr, =
$sth->state ];
The first exception message being raised gets cached in $errstr_cache. =
Subsequent (different) messages that have the same $err are not cached.
As PostgreSQL's plpgsql always generates the same SQLSTATE for a raised =
exception (http://www.postgresql.org/docs/8.2/interactive/plpgsql-erro rs-an=
d-messages.html), no matter what message it is invoced with, $err (native =
database engine error code) will always be 1 and only the first message =
will be cached and returned into @tuple_status.
As I can see no obvious advantage in caching the messages, nor in storing =
the value of $sth->err temporarily in $err, I propose to replace lines =
1930 and 1931
my $err =3D $sth->err;
push @$tuple_status, [ $err, $errstr_cache{$err} ||=3D $sth->errstr, =
$sth->state ];
with this one line
push @$tuple_status, [ $sth->err, $sth->errstr, $sth->state ];
This replacement provides the expected values in @tuple_status.
Thanks for looking into this.
Bart Degryse
Senior Developer
--------------------------------------
My test scenario:
This is the target table definition in PostgreSQL
CREATE TABLE "public"."afh_test" (
"addrformat" VARCHAR(10) NOT NULL,=20
"name" VARCHAR(30) NOT NULL,=20
"dataareaid" VARCHAR(3) NOT NULL,=20
"recid" NUMERIC(10,0) NOT NULL
) WITHOUT OIDS;
=20
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=20
ON "public"."afh_test" FOR EACH ROW=20
EXECUTE PROCEDURE "public"."temp_func1"();
=20
CREATE OR REPLACE FUNCTION "public"."temp_func1" () RETURNS trigger AS
$body$
BEGIN
IF NEW.dataareaid =3D 'lil' THEN
RAISE EXCEPTION '% foutje %', NEW.dataareaid, NEW.name;
elsIF NEW.dataareaid =3D '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;
=20
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 =3D 'SELECT * FROM AddressFormatHeading';
$target =3D 'INSERT INTO afh_test (addrformat, name, dataareaid, recid) =
VALUES (?,?,?,?)';
=20
my $dbh_ora =3D DBI->connect('dbi:Oracle:database=3Dbmssa;host=3Dfirev120=
-1.indicator.be;sid=3Dmars', 'bmssa', '********')
or elog(ERROR, "Couldn't connect to database: " . =
DBI->errstr);
my $dbh_pg =3D DBI->connect('dbi:Pg:dbname=3Ddefrevdev;host=3D10.100.1.21=
;port=3D2345', 'defrevsys', '********')
or elog(ERROR, "Couldn't connect to database: " . DBI->errst=
r);
=20
my $sel =3D $dbh_ora->prepare($query)
or elog(ERROR, "Couldn't prepare statement: " . $dbh_ora->errst=
r);
$sel->execute;
my $ins =3D $dbh_pg->prepare($target)
or elog(ERROR, "Couldn't prepare statement: " . $dbh_pg->errstr=
);
my $fetch_tuple_sub =3D sub { $sel->fetchrow_arrayref };
my @tuple_status;
my $rc =3D $ins->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
if ($dbh_pg->err) {
my @errors =3D grep { ref $_ } @tuple_status;
foreach my $error (@errors) {
elog(INFO, $error->[0] . ' - ' . $error->[1]);
}
}
$dbh_ora->disconnect;
$dbh_pg->disconnect;
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
To call the function:
SELECT dbi_insert3();
Alternatively this can be done outside PostgreSQL:
#!/usr/bin/perl
use DBI;
$query =3D "SELECT * FROM AddressFormatHeading";
$target =3D 'INSERT INTO afh_test (addrformat, name, dataareaid, recid) =
VALUES (?,?,?,?)';
my $dbh_ora =3D DBI->connect('dbi:Oracle:database=3Dbmssa;host=3Dfirev120=
-1.indicator.be;sid=3Dmars', 'bmssa', '********')
or die "Couldn't connect to database: " . DBI->errstr;
my $dbh_pg =3D DBI->connect('dbi:PgPP:dbname=3Ddefrevdev;host=3D10.100.1.=
21;port=3D2345', 'defrevsys', '********')
or die "Couldn't connect to database: " . DBI->errstr;
my $sel =3D $dbh_ora->prepare($query)
or die "Couldn't prepare statement: " . $dbh_ora->errstr;
$sel->execute;
my $ins =3D $dbh_pg->prepare($target)
or die "Couldn't prepare statement: " . $dbh_pg->errstr;
my $fetch_tuple_sub =3D sub { $sel->fetchrow_arrayref };
my @tuple_status;
my $rc =3D $ins->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
if ($dbh_pg->err) {
my @errors =3D grep { ref $_ } @tuple_status;
foreach my $error (@errors) {
print $error->[0].' - '.$error->[1];
}
}
$dbh_ora->disconnect;
$dbh_pg->disconnect;
--------------------------------------
Output of the script outside PostgreSQL without my proposed patch:
1 - ERROR: lil foutje Address Belgium
1 - ERROR: lil foutje Address Belgium
1 - ERROR: lil foutje Address Belgium
1 - ERROR: lil foutje Address Belgium
Output of the script outside PostgreSQL with my proposed patch:
1 - ERROR: lil foutje Address Belgium
1 - ERROR: lil foutje Address France
1 - ERROR: bol nog een foutje Italie
1 - ERROR: bol nog een foutje Beglie
=20
--=__Part3E191D73.0__=--