Обсуждение: 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