Description of problem: The PostgreSQL function dnm_context_change_context has a variable defined as: v_new_granted_context = dnm_context_get_granted_context(v_context_id) This obviously does not make sense because v_context_id has not been initialized to anything yet. Looking at the oracle procedure it looks like it should be: v_new_granted_context = dnm_context_get_granted_context(p_context_id) Also, the function acs_object_dnm_ctx_add_fn called by trigger acs_object_dnm_ctx_add_trg looks like it should be calling dnm_context_add_object(new.object_id,null) not dnm_context_add_object (new.object_id,0). But I'm not positive about that one.
Related to the function acs_object_dnm_ctx_add_fn above, object_context_dnm_fn called by trigger object_context_dnm_trg should also be calling dnm_context_change_context(old.object_id, null) instead of dnm_context_change_context(old.object_id, 0)
> v_new_granted_context = dnm_context_get_granted_context(v_context_id) > > This obviously does not make sense because v_context_id has not been > initialized to anything yet. Sorry, but v_context_id _is_ initialized, in the previous line <code_snippet> v_context_id = coalesce(p_context_id,0); v_new_granted_context = dnm_context_get_granted_context(v_context_id); </code_snippet> > Looking at the oracle procedure it looks like it should be: Due the fact that pg doesnt support "connect by" and difference in sql syntax, the oracle and pg code differs a lot, also there are some tables in pg for maintaining denormalization which are not exist in oracle. > Also, the function acs_object_dnm_ctx_add_fn called by trigger > acs_object_dnm_ctx_add_trg looks like it should be calling > dnm_context_add_object(new.object_id,null) not dnm_context_add_object > (new.object_id,0). But I'm not positive about that one. Object with id = 0 is special object which is Root of context hierarchy, so even if you dont specify context for object, or set it to null it still points to the object 0. > object_context_dnm_fn called by trigger object_context_dnm_trg should > also be calling dnm_context_change_context(old.object_id, null) > instead of dnm_context_change_context(old.object_id, 0) The in the object_context table only context_id value can be changed, not the value for object_id so no need for mangling with old.object_id. Regards
You are right about v_context_id being initialized. I guess I was starring at too much code that day and missed that. But there is still a problem here. I get a not null violation constraint on dnm_object_1_granted_context.pd_context_id when I try and insert the 0 object into acs_objects. Tracing through the code dnm_context_add_object(0, 0) dnm_context_get_granted_context(0) [returns null] If you pass in null this function will return 0 However if you pass in a value it will return pd_context_id from dnm_object_1_granted_context for the passed in p_object_id. Working with a blank database there isn't anything in dnm_object_1_granted_context so it returns null The next line in dnm_context_add_object then trys to insert the null value returned by dnm_context_get_granted_context into dnm_object_1_granted_context.pd_context_id which fails. I corrected this problem by changing the trigger to call dnm_context_add_object(0, null) which causes dnm_context_get_granted_context(null) to be called which then returns 0. Their is no bug in dnm_context_change_context but there is one in acs_object_dnm_ctx_add_fn.
The object id = 0 is created before creating triggers, and _NEVER_ modified/deleted. It has very special purpose, so it is _hardcoded_ and denorm api _will_not_work_properly_ without it, producing logical inconsystency in the denormalization dm. So the triggers are never meant to work for this case. object 0 should be inserted before the creation of triggers.