Hello.
I found a bug in the behaviour of plpgsql error handling system
while trying to handle foreign key violation exception.
When this error occured, control doesn't jump to exception handling block.
It moves to the next statement instead. When control leaves the
function exception is occured. So it's impossible to handle this kind of
exception.
Furthermore, the FOUND local variable is set to true after insert
statement which raises foreign key violation and
GET DIAGNOSTICS var = ROW_COUNT set var to 1.
Attached file contains sample that shows this bug.
Execute fk_violation_bug, fk_violation_bug2 and fk_violation_bug3
plpgsql functions to see this error.
---------
I'm using
"PostgreSQL 8.0.0beta1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.2.3 (mingw
special 20030504-1)"
on
OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 2 Build 2600
Thank you.
--
Best regards,
Ivan mailto:[EMAIL PROTECTED]
--
-- PostgreSQL database dump
--
SET client_encoding = 'WIN';
SET check_function_bodies = false;
SET SESSION AUTHORIZATION 'root';
--
-- Name: public; Type: SCHEMA; Schema: -; Owner: root
--
--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: root
--
COMMENT ON SCHEMA public IS 'Standard public schema';
SET search_path = public, pg_catalog;
--
-- Name: plpgsql_call_handler(); Type: FUNCTION; Schema: public; Owner: root
--
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
AS '$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAGE c;
--
-- Name: plpgsql_validator(oid); Type: FUNCTION; Schema: public; Owner: root
--
CREATE FUNCTION plpgsql_validator(oid) RETURNS void
AS '$libdir/plpgsql', 'plpgsql_validator'
LANGUAGE c;
SET SESSION AUTHORIZATION DEFAULT;
--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner:
--
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler VALIDATOR
plpgsql_validator;
SET SESSION AUTHORIZATION 'root';
--
-- Name: fk_violation_bug(); Type: FUNCTION; Schema: public; Owner: root
--
CREATE FUNCTION fk_violation_bug() RETURNS integer
AS '
begin
insert into detailed (main_id, task)
values (-1, ''bug'');
return 0;
end;
'
LANGUAGE plpgsql;
--
-- Name: fk_violation_bug2(); Type: FUNCTION; Schema: public; Owner: root
--
CREATE FUNCTION fk_violation_bug2() RETURNS integer
AS '
begin
insert into detailed (main_id, task)
values (-1, ''bug'');
return 0;
exception
when foreign_key_violation then
raise warning ''foreign key violation'';
return -1;
when others then
raise warning ''other error occured'';
return -2;
end;
'
LANGUAGE plpgsql;
--
-- Name: fk_violation_bug3(); Type: FUNCTION; Schema: public; Owner: root
--
CREATE FUNCTION fk_violation_bug3() RETURNS integer
AS '
declare
tmp int4;
begin
insert into detailed (main_id, task)
values (-1, ''bug'');
raise notice ''should not be there if the foreign key violation occured'';
raise notice ''local variable FOUND value: %'', FOUND;
GET DIAGNOSTICS tmp = ROW_COUNT;
raise notice ''DIAGNOSTICS ROW_COUNT value: %'', tmp;
tmp := 1/0;
raise notice ''should not be there if division by zero occured'';
return 0;
exception
when foreign_key_violation then
raise warning ''foreign key violation'';
return -1;
when division_by_zero then
raise warning ''division by zero'';
return -2;
when others then
raise warning ''other error occured'';
return -3;
end;
'
LANGUAGE plpgsql;
SET default_with_oids = true;
--
-- Name: detailed; Type: TABLE; Schema: public; Owner: root
--
CREATE TABLE detailed (
main_id integer,
task text NOT NULL
);
--
-- Name: main; Type: TABLE; Schema: public; Owner: root
--
CREATE TABLE main (
id serial NOT NULL,
name character varying(64)
);
--
-- Name: main_id_seq; Type: SEQUENCE SET; Schema: public; Owner: root
--
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('main', 'id'), 1, false);
--
-- Data for Name: detailed; Type: TABLE DATA; Schema: public; Owner: root
--
INSERT INTO detailed (main_id, task) VALUES (1, 'task11');
INSERT INTO detailed (main_id, task) VALUES (1, 'task12');
--
-- Data for Name: main; Type: TABLE DATA; Schema: public; Owner: root
--
INSERT INTO main (id, name) VALUES (1, 'name1');
INSERT INTO main (id, name) VALUES (2, 'name2');
INSERT INTO main (id, name) VALUES (3, 'name3');
--
-- Name: main_pkey; Type: CONSTRAINT; Schema: public; Owner: root
--
ALTER TABLE ONLY main
ADD CONSTRAINT main_pkey PRIMARY KEY (id);
--
-- Name: FK_main_detailed; Type: FK CONSTRAINT; Schema: public; Owner: root
--
ALTER TABLE ONLY detailed
ADD CONSTRAINT "FK_main_detailed" FOREIGN KEY (main_id) REFERENCES main(id) ON
UPDATE RESTRICT ON DELETE CASCADE;
SET SESSION AUTHORIZATION DEFAULT;
SET SESSION AUTHORIZATION 'root';
--
-- PostgreSQL database dump complete
--
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
