Rules vs Triggers

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

# 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

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 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

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

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/