First you should use a ltree type for the uri field :
- you write it foo.bar instead of /foo/bar
- there are operators on ltree types to express "is parent of", "is children of"
- these operators are indexed
Check the readme : http://www.sai.msu.su/~megera/postgres/gist/ltree/
If you have this type of path, I guess you'll often make tree traversal operations, and that you'll find the ltree operators extremely useful.
You can update it with a trigger just like before.
Now about your update problem, when you rename foo.bar into foo.crum.bar you could, in a single update, replace all foo.bar by foo.crum.bar in all your table with the ltree operators and special functions.
And for your checks, you can add a CHECK on the url field to be sure it's equal to the url of the parent + the name of the current row. It'll make one more SELECT request, though.
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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
