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@xxxxxxxxxxx
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
