Skip site navigation (1) Skip section navigation (2)

Re: Allowing update of column only from trigger

From: PFC <lists(at)boutiquenumerique(dot)com>
To: "Shawn Harrison" <harrison(at)tbc(dot)net>, pgsql-general(at)postgresql(dot)org
Subject: Re: Allowing update of column only from trigger
Date: 2005-01-29 01:08:12
Message-ID: opslcarysyth1vuj@musicbox (view raw or flat)
Thread:
Lists: 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



In response to

Responses

pgsql-general by date

Next:From: Robert BernierDate: 2005-01-29 02:10:07
Subject: Re: OLS BOF for linux & postgresql
Previous:From: Marc G. FournierDate: 2005-01-29 01:05:20
Subject: Re: Mail list / web issues

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group