Thread: Rules vs Triggers
Read the Rules section of the manual and the section on Rules vs Triggers. From what I get triggers are necessary for column constraints. As far as speed, it seems there are some differences between how fast rules/triggers would do the same action, but that some complex analysis is involved to determine this. And I gathered rules are necessary to allow update/insert/delete actions on views. Can anyone give me some simple reasons why they choose rules over triggers in their real-world dbs? -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/
# rgp@systame.com / 2005-07-26 17:53:35 -0400:
> Read the Rules section of the manual and the section on Rules vs Triggers.
>
> From what I get triggers are necessary for column constraints. As far as
> speed, it seems there are some differences between how fast rules/triggers
> would do the same action, but that some complex analysis is involved to
> determine this. And I gathered rules are necessary to allow
> update/insert/delete actions on views.
>
> Can anyone give me some simple reasons why they choose rules over triggers
> in their real-world dbs?
Something like this will ensure the user will not be able to modify
the author information in updatedon/updatedby columns:
CREATE TABLE t1 (
id SERIAL,
val TEXT,
updatedon TIMESTAMP,
updatedby TEXT
);
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE RULE v1i AS ON INSERT TO v1 DO INSTEAD
INSERT INTO t1 (val, updatedon, updatedby)
VALUES (NEW.val, NOW(), CURRENT_USER);
CREATE RULE v1u AS ON UPDATE TO v1 DO INSTEAD
UPDATE t1 SET
val = NEW.val,
updatedon = NOW(),
updatedby = CURRENT_USER
WHERE id = NEW.id;
(That should be taken as pseudocode, I'm sure there are bugs in it.)
Another common reason is the need/desire to keep values of certain
columns somehow synchronized, as in:
CREATE FUNCTION UNIXTS_TO_SQLTS(INTEGER) RETURNS TIMESTAMP AS ...;
CREATE TABLE t2 (
id SERIAL,
unixts INTEGER,
sqlts TIMESTAMP
);
CREATE VIEW v2 AS SELECT * FROM t2;
CREATE RULE v2i AS ON INSERT TO v2 DO INSTEAD
INSERT INTO t2 (unixts, sqlts)
VALUES (NEW.unixts, UNIXTS_TO_SQLTS(NEW.unixts);
CREATE RULE v2u AS ON UPDATE TO v2 DO INSTEAD
UPDATE t2 SET
unixts = NEW.unixts,
sqlts = UNIXTS_TO_SQLTS(NEW.unixts),
WHERE id = NEW.id;
So basically, it's these reasons:
* to have updatable views
- so you don't select from view_x, but insert into table_x;
- if updating certain view involves updating more than one table,
you'll want to have the code fixated in a rule to tighten the
space where clients can screw up
* to prevent clients from updating certain columns and/or rows
* to enforce certain characteristics of data
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
Am Dienstag, 26. Juli 2005 23:53 schrieb Randall Perry: > Read the Rules section of the manual and the section on Rules vs Triggers. > > From what I get triggers are necessary for column constraints. As far as > speed, it seems there are some differences between how fast rules/triggers > would do the same action, but that some complex analysis is involved to > determine this. And I gathered rules are necessary to allow > update/insert/delete actions on views. > > Can anyone give me some simple reasons why they choose rules over triggers > in their real-world dbs? Triggers are executed per row, so they are quite procedural. If you insert or update 500 rows they are fired 500 times. Rules modify the sql query tree. So rules are at some point nothing else as rewrites to your sql statement. If you update 500 rows and you have an on update rule. Your query tree is modified once and gets executed for all 500 rows. Rules are much faster an much more relational than triggers are, because they become pure sql before they reach the database. imagine an on delete trigger which record the deletion in an audit table like this: create trigger tg_member before delete on member for each row EXECUTE PROCEDURE audit_meber_deletion(); audit_meber_deletion() does an INSERT to an audit table. no think of members are organized in groups. If you delete a group ALL members are deleted because of cascading foreing keys references. Now delete a group with 20000 members. The trigger is fired 20000 times No Imagine a rule which does create rule rl_member AS ON DELETE TO member DO INSERT INTO member_deletion (membername) VALUES (OLD.membername) this is executed once and is as fast as SQL can be. Normally you dont see a difference between triggers and rules if you have update and insert statemnts which affect only a few rows. but if it comes to affecting many rows, you should use rules. But rules are more difficult to understand. kind regards, janning
Hi, Is it normal that when I select for update a record, but I don't select all the fields, that the contents of fields not selected will be deleted: create table pidtest(pid integer, szoveg text) without oids; select pid from pistest where pid>5 for update; After committing (autocommit), the contents of the szoveg field for the locked rows will be erased. PostgreSQL 8.0.3 WinXP Regards, Otto
Havasvölgyi Ottó wrote: > Hi, > > Is it normal that when I select for update a record, but I don't select > all the fields, that the contents of fields not selected will be deleted: > > create table pidtest(pid integer, szoveg text) without oids; > > select pid from pistest where pid>5 for update; > > After committing (autocommit), the contents of the szoveg field for the > locked rows will be erased. Committing what? You're not updating anything here. The "SELECT...FOR UPDATE" just locks the rows so you can update them, it doesn't perform the update. -- Richard Huxton Archonet Ltd
On Thu, Jul 28, 2005 at 01:22:31PM +0200, Havasvölgyi Ottó wrote: > Is it normal that when I select for update a record, but I don't select all > the fields, that the contents of fields not selected will be deleted: > > create table pidtest(pid integer, szoveg text) without oids; > > select pid from pistest where pid>5 for update; > > After committing (autocommit), the contents of the szoveg field for the > locked rows will be erased. Could you provide a complete test case? Works fine here: CREATE TABLE pidtest (pid integer, szoveg text) WITHOUT OIDS; INSERT INTO pidtest (pid, szoveg) VALUES (3, 'three'); INSERT INTO pidtest (pid, szoveg) VALUES (4, 'four'); INSERT INTO pidtest (pid, szoveg) VALUES (5, 'five'); INSERT INTO pidtest (pid, szoveg) VALUES (6, 'six'); INSERT INTO pidtest (pid, szoveg) VALUES (7, 'seven'); SELECT pid FROM pidtest WHERE pid > 5 FOR UPDATE; pid ----- 6 7 (2 rows) SELECT * FROM pidtest; pid | szoveg -----+-------- 3 | three 4 | four 5 | five 6 | six 7 | seven (5 rows) -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Hi, Oh, sorry. CREATE TABLE pidtest (pid integer, szoveg text) WITHOUT OIDS; INSERT INTO pidtest (pid, szoveg) VALUES (3, 'three'); INSERT INTO pidtest (pid, szoveg) VALUES (4, 'four'); INSERT INTO pidtest (pid, szoveg) VALUES (5, 'five'); INSERT INTO pidtest (pid, szoveg) VALUES (6, 'six'); INSERT INTO pidtest (pid, szoveg) VALUES (7, 'seven'); create function pidtest_del(_pid integer) returns void as $$ declare row pidtest; begin perform pid from pidtest where pid>=_pid for update; delete from pidtest where pid=_pid; for row in select * from pidtest where pid>_pid order by pid loop update pidtest set pid=pid-1 where pid=row.pid; end loop; return; end; $$ language plpgslq; This function deletes a row, and updates the pid field where pid is geater than the deleted pid value, so that the gap caused by the deletion is not present any more. Sorry, now I cannot reproduce it, but yesterday I was suprised that the szoveg field's contents in the locked records went away. Best Regards, Otto ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Havasvölgyi Ottó" <h.otto@freemail.hu> Cc: <pgsql-general@postgresql.org> Sent: Thursday, July 28, 2005 2:02 PM Subject: Re: [GENERAL] Select for update > On Thu, Jul 28, 2005 at 01:22:31PM +0200, Havasvölgyi Ottó wrote: >> Is it normal that when I select for update a record, but I don't select >> all >> the fields, that the contents of fields not selected will be deleted: >> >> create table pidtest(pid integer, szoveg text) without oids; >> >> select pid from pistest where pid>5 for update; >> >> After committing (autocommit), the contents of the szoveg field for the >> locked rows will be erased. > > Could you provide a complete test case? Works fine here: > > CREATE TABLE pidtest (pid integer, szoveg text) WITHOUT OIDS; > > INSERT INTO pidtest (pid, szoveg) VALUES (3, 'three'); > INSERT INTO pidtest (pid, szoveg) VALUES (4, 'four'); > INSERT INTO pidtest (pid, szoveg) VALUES (5, 'five'); > INSERT INTO pidtest (pid, szoveg) VALUES (6, 'six'); > INSERT INTO pidtest (pid, szoveg) VALUES (7, 'seven'); > > SELECT pid FROM pidtest WHERE pid > 5 FOR UPDATE; > pid > ----- > 6 > 7 > (2 rows) > > SELECT * FROM pidtest; > pid | szoveg > -----+-------- > 3 | three > 4 | four > 5 | five > 6 | six > 7 | seven > (5 rows) > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
On Fri, Jul 29, 2005 at 12:05:46AM +0200, Havasvölgyi Ottó wrote: > create function pidtest_del(_pid integer) returns void as $$ > declare > row pidtest; > begin > perform pid from pidtest where pid>=_pid for update; > delete from pidtest where pid=_pid; > for row in select * from pidtest where pid>_pid order by pid loop > update pidtest set pid=pid-1 where pid=row.pid; > end loop; > return; > end; > $$ language plpgslq; I suspect this isn't exactly the code you're running, because creating this function fails with the following error: ERROR: language "plpgslq" does not exist If I correct the spelling to "plpgsql" then I get the following results: SELECT * FROM pidtest; pid | szoveg -----+-------- 3 | three 4 | four 5 | five 6 | six 7 | seven (5 rows) SELECT pidtest_del(5); pidtest_del ------------- (1 row) SELECT * FROM pidtest; pid | szoveg -----+-------- 3 | three 4 | four 5 | six 6 | seven (4 rows) > Sorry, now I cannot reproduce it, but yesterday I was suprised that the > szoveg field's contents in the locked records went away. What you describe isn't supposed to happen, but we can't do much to investigate the problem unless we can see how to reproduce it. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Fri, Jul 29, 2005 at 00:05:46 +0200, Havasvölgyi Ottó <h.otto@freemail.hu> wrote: > > This function deletes a row, and updates the pid field where pid is geater > than the deleted pid value, so that the gap caused by the deletion is not > present any more. This isn't directly related to your problem, but why are you doing this? This is a pretty inefficient thing to be doing unless there is a business rule that there can't be gaps in the pid values. If you just want to number rows in reports, you should do that in your reporting application, not by continuously renumbering your records.
Hi, Yes, I misspelled in the mail, I don't any way to copy it from the console, so I rewrote it here. I will post if I can reproduce it again. I hope that I have missed something. Best Regards, Otto ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Havasvölgyi Ottó" <h.otto@freemail.hu> Cc: <pgsql-general@postgresql.org> Sent: Friday, July 29, 2005 3:12 AM Subject: Re: [GENERAL] Select for update > On Fri, Jul 29, 2005 at 12:05:46AM +0200, Havasvölgyi Ottó wrote: >> create function pidtest_del(_pid integer) returns void as $$ >> declare >> row pidtest; >> begin >> perform pid from pidtest where pid>=_pid for update; >> delete from pidtest where pid=_pid; >> for row in select * from pidtest where pid>_pid order by pid loop >> update pidtest set pid=pid-1 where pid=row.pid; >> end loop; >> return; >> end; >> $$ language plpgslq; > > I suspect this isn't exactly the code you're running, because creating > this function fails with the following error: > > ERROR: language "plpgslq" does not exist > > If I correct the spelling to "plpgsql" then I get the following results: > > SELECT * FROM pidtest; > pid | szoveg > -----+-------- > 3 | three > 4 | four > 5 | five > 6 | six > 7 | seven > (5 rows) > > SELECT pidtest_del(5); > pidtest_del > ------------- > > (1 row) > > SELECT * FROM pidtest; > pid | szoveg > -----+-------- > 3 | three > 4 | four > 5 | six > 6 | seven > (4 rows) > >> Sorry, now I cannot reproduce it, but yesterday I was suprised that the >> szoveg field's contents in the locked records went away. > > What you describe isn't supposed to happen, but we can't do much > to investigate the problem unless we can see how to reproduce it. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > >
Bruno, I know this is inefficient. In fact it was someone other's problem to eliminate gaps and I solved it this way. I don't do such things for myself. Best Regards, Otto ----- Original Message ----- From: "Bruno Wolff III" <bruno@wolff.to> To: "Havasvölgyi Ottó" <h.otto@freemail.hu> Cc: <pgsql-general@postgresql.org> Sent: Friday, July 29, 2005 8:58 AM Subject: Re: [GENERAL] Select for update > On Fri, Jul 29, 2005 at 00:05:46 +0200, > Havasvölgyi Ottó <h.otto@freemail.hu> wrote: >> >> This function deletes a row, and updates the pid field where pid is >> geater >> than the deleted pid value, so that the gap caused by the deletion is not >> present any more. > > This isn't directly related to your problem, but why are you doing this? > This is a pretty inefficient thing to be doing unless there is a business > rule that there can't be gaps in the pid values. If you just want to > number rows in reports, you should do that in your reporting application, > not by continuously renumbering your records. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > >
Thanks for the info guys; got a better understanding now. -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/