pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedperlu: did I find a bug, or did I make one?
3+ messages / 2 participants
[nested] [flat]
* perlu: did I find a bug, or did I make one?
@ 2007-06-04 10:29 Bart Degryse <Bart.Degryse@indicator.be>
2007-06-04 14:52 ` Re: perlu: did I find a bug, or did I make one? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Bart Degryse @ 2007-06-04 10:29 UTC (permalink / raw)
To: pgsql-sql
Situation:
I'm writing a function that fetches data in an Oracle database and stores it in postgresql database. The function works, but I can't seem to get the error handling right. I get something but it's 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 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 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");
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();
Thanks for any help!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: perlu: did I find a bug, or did I make one?
2007-06-04 10:29 perlu: did I find a bug, or did I make one? Bart Degryse <Bart.Degryse@indicator.be>
@ 2007-06-04 14:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-04 14:58 ` Re: perlu: did I find a bug, or did I make one? Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2007-06-04 14:52 UTC (permalink / raw)
To: Bart Degryse <Bart.Degryse@indicator.be>; +Cc: pgsql-sql
"Bart Degryse" <Bart.Degryse@indicator.be> writes:
> 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 =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;
You probably don't want this trigger doing RETURN NULL; that's
turning all your inserts into no-ops.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: perlu: did I find a bug, or did I make one?
2007-06-04 10:29 perlu: did I find a bug, or did I make one? Bart Degryse <Bart.Degryse@indicator.be>
2007-06-04 14:52 ` Re: perlu: did I find a bug, or did I make one? Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-06-04 14:58 ` Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 0 replies; 3+ messages in thread
From: Bart Degryse @ 2007-06-04 14:58 UTC (permalink / raw)
To: pgsql-sql
Well, actually I do. If there's any error, I want nothing done.
But my real point was that although there are 2 records in my source table with dataareaid = 'lil' and two with dataareaid = 'bol' I still get 4 times the 'lil' error message, while I was expecting 2 times the 'lil' error message and two times the 'bol' error message.
>>> Tom Lane <tgl@sss.pgh.pa.us> 2007-06-04 16:52 >>>
"Bart Degryse" <Bart.Degryse@indicator.be> writes:
> 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 =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;
You probably don't want this trigger doing RETURN NULL; that's
turning all your inserts into no-ops.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-06-04 14:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-04 10:29 perlu: did I find a bug, or did I make one? Bart Degryse <Bart.Degryse@indicator.be>
2007-06-04 14:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-04 14:58 ` Bart Degryse <Bart.Degryse@indicator.be>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox