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
