Re: [GENERAL] Trigger problems/questions - Mailing list pgsql-general

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.klaver@aklaver.com