Trigger problems/questions

I have an object database that's mirrored to a table with data in a JSONB
column. Data are organized into "communities". Community ids aren't
stored directly in content but can be found by recursively following
__parent__ properties. I want to be able to index content records on their
community ids.

(I originally tried to index functions that got ids, but apparently lying
about immutability is a bad idea and I suffered the consequences. :-])

I tried creating a trigger to populate a community_zoid property with a
community id when a record is inserted or updated. The trigger calls a
recursive functions to get the community id.

Trigger: https://github.com/karlproject/karl/blob/master/
karl/scripts/pgevolve.py#L261

Trigger procedure: https://github.com/karlproject/karl/blob/master/
karl/scripts/pgevolve.py#L236

Function to find a community id: https://github.com/
karlproject/karl/blob/master/karl/scripts/pgevolve.py#L209

This scheme succeeds most of the time, but occasionally, it fails.

I can find records where it has failed with a query like:

select zoid
from newt
where find_community_zoid(zoid, class_name, state) is not null
and not state ? 'community_zoid';

If I update the records where it has failed:

update newt set class_name=class_name
where find_community_zoid(zoid, class_name, state) is not null
and not state ? 'community_zoid';

Then retry the query above, I get 0 rows back. This would seem to indicate
that the trigger is logically correct.

Questions:

- Am I doing it wrong? For example, is there some limitation on trigger
procedures that I'm violating?
- If a trigger procedure errors, is the transaction aborted? (I would
hope so.)
- Should I expect triggers to be reliable and rely on them for database
integrity?
- Any suggestions on how to debug this?

Jim

--
Jim Fulton
http://jimfulton.info