Forum OpenACS Development: Undescriptive postgresql error

Collapse
Posted by Gilbert Wong on

What does this error mean?

ERROR:  triggered data change violation on 
relation "acs_privilege_hierarchy_index" 

I received this error when I tried to run acs_privilege__add_child. How should I go about debugging this error? Is there any way to find out more information about SQL command that caused this error? I already tried looking into the trigger procedure acs_priv_hier_ins_del_tr but I don't want to study this procedure if it won't help me debug this error. Any ideas?

Thanks.

Collapse
Posted by Dan Wickstrom on
This will happen if the add_child method is called more than once within a transaction.  Look at packages/acs-kernel/sql/postgresql/acs-create.sql for example of a work-around.  If this work-around doesn't fix your problem, post more information, and I'll see if I can come up with a better suggestion.  I might need to take a look a rewriting that trigger procedure to see if I can avoid the data-change violation error.

If you're interested, Don can explain the root cause of the problem in more detail, but my understanding is that it has something to do with restrictions that pg places on the number of times a primary key can be updated during a transaction.  This is something that will be addressed in a future release of pg as the current implementation seems to be incorrect in some cases.

Collapse
Posted by Gilbert Wong on

Thanks Dan. That did the trick.

One a somewhat related note, what is the difference between creating the inline functions with a PERFORM, then doing a select on that inline function

versus

doing a select on the function between a begin; and end; block?

Collapse
Posted by Dan Wickstrom on
They are essentially the same thing since a function is an implied transaction.  Function calls are used to port inline code when it is necessary to pass the results of one function call as an input to another function call, whereas selects within a transaction are used when you are calling one or more functions for effect only.