> 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.
> # select id, name, parent, uri from objects;
> id | name | parent | uri
> 1 | | 1 | /
> 2 | foo | 1 | /foo
> 3 | bar | 2 | /foo/bar
> (3 rows)
> The uri is calculated by a trigger before update on objects.
> The original version of the trigger function would re-calculate the uri
> for an object and its immediate children if the name, parent, or uri
> changed. It would apply the uri change to the children, which would
> cascade down the hierarchy. This generally worked, but (1) I was having
> data visibility voodoo, and (2) it was calculating every child's uri
> twice, which would be inefficient for large hierarchies.
> So I changed the trigger function so that it would only fire if name or
> parent had changed. I created a recursive helper function that changes
> the uri for all descendants of an object, if the object's name or parent
> has changed. There is no cascade of changes (the trigger fires for all
> the descendants, of course, but doesn't do anything). Works great, is
> more efficient, and I can manage the max_stack_size to fit the size of
> the hierarchy.
> The PROBLEM with this is that anyone can now
> # update objects set uri='/ha/ha/your/ed' where id=2;
> I want the trigger function and its helper alone to be able to update
> the uri.
> What is the best way to do this? Should I put the uri column in a
> separate table, and play with permissions? (Yuck.) Do I need to bite the
> bullet, go back to cascading triggers, and work out the data visibility
> voodoo and the efficiency issue? Or is there a better way that I haven't
> thought of?
> Shawn Harrison