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@xxxxxxx
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
