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.
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?
