Search Postgresql Archives

Re: Allowing update of column only from trigger

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Shawn Harrison wrote [01/28/05 3:53 PM]:
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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux