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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
