Re: Allowing update of column only from trigger - Mailing list pgsql-general

    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