Thread: BUG #1676: Statment order in rules
The following bug has been logged online: Bug reference: 1676 Logged by: Olleg Samoylov Email address: olleg@mipt.ru PostgreSQL version: 7.4.7 Operating system: on x86_64-pc-linux-gnu, compiled by GCC x86_64-linux-gcc (GCC) 3.3.5 (Debian 1:3.3.5-12) Description: Statment order in rules Details: -- Here example for delete rule, but for update rule bug arised also. -- This is simplified example from working database. -- create tables -- counter create table c (c integer); insert into c values (0); -- private table create table a (a integer); -- public view create view v as select a from a; -- create worked rules, for example create rule insert_last as on insert to v do instead (update c set c=c+1;insert into a values (new.a)); create rule delete_last as on delete to v do instead (update c set c=c-1;delete from a where a=old.a); insert into v values (0); select * from a; -- 1 row select * from c; -- 1 delete from v where a=0; select * from a; -- 0 rows select * from c; -- 0, Okey -- now change order of statment drop rule insert_last on v; drop rule delete_last on v; create rule insert_first as on insert to v do instead (insert into a values (new.a);update c set c=c+1); create rule delete_first as on delete to v do instead (delete from a where a=old.a;update c set c=c-1); insert into v values (0); select * from a; -- 1 row select * from c; -- 1, Ok delete from v where a=0; select * from a; -- 0 row, as expected :-) select * from c; -- 1, this is incorrect
"Olleg Samoylov" <olleg@mipt.ru> writes: > create rule delete_first as on delete to v do instead (delete from a where > a=old.a;update c set c=c-1); > delete from v where a=0; > select * from a; > -- 0 row, as expected :-) > select * from c; > -- 1, this is incorrect This isn't a bug. The DELETE causes the a=0 row to disappear from the view, so the subsequent statement (which is implicitly conditional on there being an a=0 row) doesn't do anything. If it did do something, then a "delete from v" that didn't delete any rows would still decrement c, which is not what you want, eh? regards, tom lane
Tom Lane wrote: > This isn't a bug. The DELETE causes the a=0 row to disappear from the I undestand. When I got choice between rules and triggers, I looked carefully is documantation, section "33.6. Rules versus Triggers". As I can see, rules prefered, there are many examples, why rules better then triggers. Only described advantage of triggers is using triggers as constraints. But later I got bugs and submit #1447, #1610, #1676. All resolved as "not a bug". IMHO this is due to lack of documentation, don't described explicitly what disadvatages or unexpected "features" rules have. Well #1676 looked strange, but can de derived from "33.3. Rules on INSERT, UPDATE, and DELETE". As I can undestand, if rule fire several commands, command to change table of rule must be last. This is not explicitly documented. ALSO rules didn't designed so. Why? As I begin undestand now, bug #1447 is consequence from "feature" #1676, isn't it? And can be avoided by changing ALSO rule to INSTEAD rule with command to modify table, implicitly existed in ALSO rule, in the last. This workaround must be documented. Or may be better change ALSO rule behaviour? #1610 disconform documentation. Documentation or rules must be fixed. Fix rules prefered. As I undestand from bug resolution, this "features" don't planed to be fixed. I add comments to documentation about this "features", but this is all, what I can. As I can undestand now, my comment in documentation for "feature" #1447 in section "33.6. Rules versus Triggers" is incorrect, but I can't remove it. -- Olleg Samoylov