Re: Allowing update of column only from trigger

Shawn Harrison wrote [01/28/05 3:53 PM]:
> I have a table like this:
>
> create table objects (
>     id     serial primary key,
>     name     varchar not null,
>     parent     integer references objects(id) default 1 not null,
>     uri    varchar not null
> ) without oids;
>
> The uri column is a denormalization for performance, storing a "path" to
> the object in the hierarchy, consisting of a sequence of names.
> [...]
> I want the trigger function and its helper alone to be able to update
> the uri.
>
> What is the best way to do this? [...]

Thank you, PFC and Andrey V. Semyonov, for your help in characterizing
the problem and its solution. Here's what I did.

"version 1" is the speedy, recursive version of the trigger function
that I wrote last week, which has the problem of letting uri be updated
directly.

My "version 2" solution was to (1) add auto-update when the uri had been
changed in addition to the parent and name, (2) make the trigger fire
_after_ update (to overcome data visibility problems), and (3) to add a
SQL update statement to the trigger rather than relying on changes to
new.uri (since changing new.uri won't work in a trigger after update).
This worked. But was very inefficient, because it was calculating the
same thing multiple times for every child object. To wit:

- loading a 300-item hierarchy (inserting all objects and updating parents):
    version 1: 1.8 seconds
    version 2: 7.9 seconds
- Updating the name of the root object in the hierarchy (which requires
updating the uri of 300 objects)
    version 1: 0.4 seconds
    version 2: 4.5 seconds

One can see how unscalable version 2 would be.

My "version 3" solution was to keep my "fast" version 1 trigger code,
but to change the data model a bit: The table is now named
"objects_data". I then created a view named "objects" which is just

create view objects as (select * from objects_data);

To protect uri, I created a rules on insert and update to objects that
doesn't pass to objects_data the changed uri value, like this:

create or replace rule objects__update as on update to objects
do instead (
     update objects_data set
         name = new.name,
         typename = new.typename,
         parent = new.parent,
     where id = new.id
);

The trigger function to update the uri then operates directly on the
objects_data table.

This solution provides as much security as I need -- to protect against
stupidity, mainly. If I wanted more security, I could change the
security on the objects_data table, as Andrey suggested.

So now all of my test cases pass, and its speedy to boot. :-)

Thanks, guys,
Shawn Harrison
--
Peace and joy,

Shawn Harrison
Tyndale House Publishers
_______________
sah@tyndale.com