Queries using rules show no rows modified?
От | Michael Alan Dorman |
---|---|
Тема | Queries using rules show no rows modified? |
Дата | |
Msg-id | 87znzaqlv2.fsf@amanda.mallet-assembly.org обсуждение исходный текст |
Список | 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 timestampnot null default now (), modified timestamp not null default now (), deleted timestamp default null, email charactervarying (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@wombat.org','pinochle'); update members set email='mdorman@lemur.org', password='wombat' where id = 1; delete from members where id = 1; ----->8-----
В списке pgsql-hackers по дате отправления: