Re: SQL Rule - Mailing list pgsql-general

Bert wrote:

>Hi list
>
>I have a table construction like the one seen below, when i am updating
>or inserting i get a recurion, logical. But how to manage it that the
>rule is just doing it one time. Or is it possible to do the sum of a
>and b in an other way?
>
>
Bert, i do this with triggers.  There are pros and cons.

One pro is that you can guarantee the correct result with code that
looks like this (I'm coding from memory, there may be some syntax errors):

if new.column_c <> old.column_c then
  raise error 'Cannot make direct assignment to calculated column
*column_c*';
end if;

Then you follow that up with the assignment, so that the code looks like:

if new.column_c <> old.column_c then
  raise error 'Cannot make direct assignment to calculated column
*column_c*';
end if;
new.column_c = new.column_a + new.column_b

The con is that these triggers go row-by-row.  Shockingly I have found
the degradation to be only 100% (instead of 700% or 1000%), so that
updates take twice as long.  In small-transaction situations this is not
a problem, it is lost in the overhead of the transaction itself.  On
large assigment statements that would take 2 minutes you now have to
wait 4 minutes, or break up the assignment.

The really cool thing about it is that you can  provide automation built
on top of normalized tables.  You get this by doing two things:

1) Derived values depend only upon normalized values or other derived values
2) never allow user writes to automated columns, raise an error when
that happens

Using views is fine for simple cases, but, and I know this because I've
done it, if you expect to automate calculations across 100's of tables
including complex and compound  calculations, your views will become
utterly unworkable, or destroy performance when 28 tables have to be
joined together when sombody issues "SELECT Total_exposure FROM Customers"

To really get the benefit, you can provide for a FETCH from parents to
children, and also SUMs from children to parent.  With that and the
simple extension of your example you can have really powerful normalized
and automated databases.

>CREATE TABLE test
>(
>  a int2,
>  b int2,
>  c int2,
>  id int2 NOT NULL,
>  CONSTRAINT id_test PRIMARY KEY (id)
>)
>WITHOUT OIDS;
>ALTER TABLE test OWNER TO postgres;
>
>
>CREATE OR REPLACE RULE sum_op AS
>    ON INSERT TO test DO  UPDATE test SET c = new.a + new.b
>  WHERE test.id = new.id;
>
>CREATE OR REPLACE RULE sum_op_up AS
>    ON UPDATE TO test DO  UPDATE test SET c = test.a + test.b
>  WHERE test.id = new.id;
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>