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/screwed' 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?
Thanks,
Shawn Harrison
--
________________
harrison@tbc.net