Queries using rules show no rows modified?

From: Michael Alan Dorman <mdorman(at)debian(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Queries using rules show no rows modified?
Date: 2002-05-08 23:08:01
Message-ID: 87znzaqlv2.fsf@amanda.mallet-assembly.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I'm using 7.2.1 on a Debian system.

If I do an insert or update or delete on a table, postgres tells me
how many rows were affected.

Using the following input to psql, I got the results:

INSERT 0 0
UPDATE 0
DELETE 0

Is this expected? The principle of least suprise suggests to me that
regardless of the query being rewritten, there is some number of
tuples being affected, and it would thus still be appropriate to
return that number.

I realize it's not technically a "bug", since there's no particular
guarantee that someone specified existing records or whatnot, but as
an additional fourth-string check in some web code I put together, I
was checking to see if stuff was returned or updated (since the system
should only being allowing changes to things that exist) as a
heuristic to guard against 1) bugs, and 2) attempts to maliciously
subvert the public interface.

I can find no mention of this issue in the documentation regarding the
rule system. Anyone have any guidance?

Mike.

-----8<-----
drop sequence member_id_seq;
create sequence member_id_seq;

drop table member;
create table member (
id integer not null constraint member_id primary key default nextval('member_id_seq'),
created timestamp not null default now (),
modified timestamp not null default now (),
deleted timestamp default null,
email character varying (128) not null constraint member_email unique,
password character varying (128) not null
);

drop view members;
create view members as select * from member m1 where m1.deleted is null;

drop rule members_delete;
create rule members_delete as on delete to members do instead update member set deleted = current_timestamp;

drop rule members_insert;
create rule members_insert as on insert to members do instead insert into member (email, password) values (new.email, new.password);

drop rule members_update;
create rule members_update as on update to members do instead update member set email = new.email, password = new.password;

insert into members (email, password) values ('mdorman(at)wombat(dot)org','pinochle');

update members set email='mdorman(at)lemur(dot)org', password='wombat' where id = 1;

delete from members where id = 1;
----->8-----

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dann Corbit 2002-05-08 23:30:50 Re: Path to PostgreSQL portabiliy
Previous Message Joel Burton 2002-05-08 22:24:51 Re: Path to PostgreSQL portabiliy