Re: Trigger problems/questions

On 06/15/2017 10:23 AM, Jim Fulton wrote:
> 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
> <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
> <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
> <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?

I do not pretend to fully understand what the the triggers/functions are
really doing, but I did notice this:

create or replace function populate_community_zoid_triggerf()

...

new_zoid := NEW.state ->> 'community_zoid';
zoid := find_community_zoid(
NEW.zoid, NEW.class_name, NEW.state)::text;
if zoid is null then
if new_zoid is not null then
NEW.state := NEW.state - 'community_zoid';
end if;
else
if new_zoid is null or zoid != new_zoid then
NEW.state :=
NEW.state || ('{"community_zoid": ' || zoid || '}')::jsonb;
end if;
end if;

...

Not sure what happens if zoid is null and new_zoid is null also?
Or if it matters?

>
> Jim
>
> --
> Jim Fulton
> http://jimfulton.info

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com