Thread: Bug for view with rules

Bug for view with rules

From
"Donald Fraser"
Date:
Hi Dave,
thanks for the latest fix.
 
Have spent all morning creating the situation for a view that causes a crash - here it is...
 
First create a simple table such as:
 
CREATE
TABLE public.tbl_catchbug (
id int4,
s_sometext text
) WITHOUT OIDS;
GRANT
ALL ON TABLE public.tbl_catchbug TO PUBLIC;
GRANT
ALL ON TABLE public.tbl_catchbug TO postgres;
 
Next create a view for the table such as:
 
CREATE
VIEW public.vu_tbl_catchbug AS SELECT t.id, t.s_sometext FROM tbl_catchbug AS t;
GRANT
INSERT, SELECT, UPDATE ON TABLE public.vu_tbl_catchbug TO PUBLIC;
GRANT
ALL ON TABLE public.vu_tbl_catchbug TO postgres;
 
Next create a function in PL/SQL such as:
 
CREATE
FUNCTION raise_exception1(text) RETURNS int4 AS '
DECLARE s_message ALIAS FOR $1;
BEGIN
    RAISE EXCEPTION s_message;
    RETURN -1;
END; '
LANGUAGE 'plpgsql';
 
Next create the following rules for the view:
 
CREATE
RULE rul_vu_tbl_catchbug_i1 AS ON INSERT TO vu_tbl_catchbug WHERE (id IS NULL AND s_sometext IS NULL) DO (SELECT raise_exception1('You must supply some data'));
CREATE
RULE rul_vu_tbl_catchbug_i2 AS ON INSERT TO vu_tbl_catchbug WHERE (id IS NULL) DO (INSERT INTO tbl_catchbug (id, s_sometext) VALUES('1', NEW.s_sometext));
CREATE
RULE rul_vu_tbl_catchbug_i3 AS ON INSERT TO vu_tbl_catchbug WHERE (id IS NOT NULL) DO (SELECT raise_exception1('This is an exception'));
CREATE
RULE rul_vu_tbl_catchbug_i4 AS ON INSERT TO vu_tbl_catchbug DO INSTEAD NOTHING;
 
Finally do an insert into the view with the following SQL statement:
 
INSERT INTO vu_tbl_catchbug (s_sometext) VALUES('This should crash pgAdminII');
 
That should do the trick...
 
I'm off for the rest of the festive season (back to work on the 2nd),
hope you have a good one too,
regards,
Donald.
 

Re: Bug for view with rules

From
"Dave Page"
Date:
Thanks Donald. I'll take a look into this when I get a minute. Have a great Christmas.
 
Regards, Dave.
-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 23 December 2002 14:22
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Bug for view with rules

Hi Dave,
thanks for the latest fix.
 
Have spent all morning creating the situation for a view that causes a crash - here it is...
 
First create a simple table such as:
 
CREATE
TABLE public.tbl_catchbug (
id int4,
s_sometext text
) WITHOUT OIDS;
GRANT
ALL ON TABLE public.tbl_catchbug TO PUBLIC;
GRANT
ALL ON TABLE public.tbl_catchbug TO postgres;
 
Next create a view for the table such as:
 
CREATE
VIEW public.vu_tbl_catchbug AS SELECT t.id, t.s_sometext FROM tbl_catchbug AS t;
GRANT
INSERT, SELECT, UPDATE ON TABLE public.vu_tbl_catchbug TO PUBLIC;
GRANT
ALL ON TABLE public.vu_tbl_catchbug TO postgres;
 
Next create a function in PL/SQL such as:
 
CREATE
FUNCTION raise_exception1(text) RETURNS int4 AS '
DECLARE s_message ALIAS FOR $1;
BEGIN
    RAISE EXCEPTION s_message;
    RETURN -1;
END; '
LANGUAGE 'plpgsql';
 
Next create the following rules for the view:
 
CREATE
RULE rul_vu_tbl_catchbug_i1 AS ON INSERT TO vu_tbl_catchbug WHERE (id IS NULL AND s_sometext IS NULL) DO (SELECT raise_exception1('You must supply some data'));
CREATE
RULE rul_vu_tbl_catchbug_i2 AS ON INSERT TO vu_tbl_catchbug WHERE (id IS NULL) DO (INSERT INTO tbl_catchbug (id, s_sometext) VALUES('1', NEW.s_sometext));
CREATE
RULE rul_vu_tbl_catchbug_i3 AS ON INSERT TO vu_tbl_catchbug WHERE (id IS NOT NULL) DO (SELECT raise_exception1('This is an exception'));
CREATE
RULE rul_vu_tbl_catchbug_i4 AS ON INSERT TO vu_tbl_catchbug DO INSTEAD NOTHING;
 
Finally do an insert into the view with the following SQL statement:
 
INSERT INTO vu_tbl_catchbug (s_sometext) VALUES('This should crash pgAdminII');
 
That should do the trick...
 
I'm off for the rest of the festive season (back to work on the 2nd),
hope you have a good one too,
regards,
Donald.
 

Re: Bug for view with rules

From
"Dave Page"
Date:
Hi Donald,
 
I've tried this and it does indeed cause a crash. As suspected though, it does appear to be ADO at fault, so I don't think there's a great deal I can do about it I'm afraid.
 
Of course, pgAdmin III won't have this problem...
 
Regards, Dave.
-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 23 December 2002 14:22
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Bug for view with rules

Hi Dave,
thanks for the latest fix.
 
Have spent all morning creating the situation for a view that causes a crash - here it is...
 
First create a simple table such as:
 
CREATE
TABLE public.tbl_catchbug (
id int4,
s_sometext text
) WITHOUT OIDS;
GRANT
ALL ON TABLE public.tbl_catchbug TO PUBLIC;
GRANT
ALL ON TABLE public.tbl_catchbug TO postgres;
 
Next create a view for the table such as:
 
CREATE
VIEW public.vu_tbl_catchbug AS SELECT t.id, t.s_sometext FROM tbl_catchbug AS t;
GRANT
INSERT, SELECT, UPDATE ON TABLE public.vu_tbl_catchbug TO PUBLIC;
GRANT
ALL ON TABLE public.vu_tbl_catchbug TO postgres;
 
Next create a function in PL/SQL such as:
 
CREATE
FUNCTION raise_exception1(text) RETURNS int4 AS '
DECLARE s_message ALIAS FOR $1;
BEGIN
    RAISE EXCEPTION s_message;
    RETURN -1;
END; '
LANGUAGE 'plpgsql';
 
Next create the following rules for the view:
 
CREATE
RULE rul_vu_tbl_catchbug_i1 AS ON INSERT TO vu_tbl_catchbug WHERE (id IS NULL AND s_sometext IS NULL) DO (SELECT raise_exception1('You must supply some data'));
CREATE
RULE rul_vu_tbl_catchbug_i2 AS ON INSERT TO vu_tbl_catchbug WHERE (id IS NULL) DO (INSERT INTO tbl_catchbug (id, s_sometext) VALUES('1', NEW.s_sometext));
CREATE
RULE rul_vu_tbl_catchbug_i3 AS ON INSERT TO vu_tbl_catchbug WHERE (id IS NOT NULL) DO (SELECT raise_exception1('This is an exception'));
CREATE
RULE rul_vu_tbl_catchbug_i4 AS ON INSERT TO vu_tbl_catchbug DO INSTEAD NOTHING;
 
Finally do an insert into the view with the following SQL statement:
 
INSERT INTO vu_tbl_catchbug (s_sometext) VALUES('This should crash pgAdminII');
 
That should do the trick...
 
I'm off for the rest of the festive season (back to work on the 2nd),
hope you have a good one too,
regards,
Donald.