Re: Trigger problems/questions

On Thu, Jun 15, 2017 at 1:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jim Fulton <jim(at)jimfulton(dot)info> writes:
> > 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.
> > ...
> > This scheme succeeds most of the time, but occasionally, it fails.
>
> Since your original idea failed, I suppose that the parent relationships
> are changeable?

Good question.

A few kinds of objects can, rarely, move in the hierarchy, and, they never
move between communities, so their community id never changes.

IDK WTF my indexing attempt. I could build the index, then add an object
to the tree and it wouldn't be indexed. This was in a staging database
where there were no other changes.

> What mechanism have you got in place to propagate a
> relationship change back down to the child records?
>

This is a non-issue, at least WRT community ids. If I were, for example,
to index paths, it would be an issue for some objects, but I'm not at that
point yet.

> Also, this looks to have a race condition: if you search for a record's
> community id at about the same time that someone else is changing the
> parent linkage, you may get the old answer, but by the time you commit the
> record update that answer may be obsolete. This is a problem because even
> if you had another trigger that was trying (in the someone else's session)
> to propagate new community ids back to affected records, it wouldn't think
> that the record you're working on needs a change, because it would also
> see the old version of that record.
>
> Solutions to the race problem usually involve either SELECT FOR UPDATE
> to lock rows involved in identifying the target record's community ID,
> or use of SERIALIZABLE to cause the whole transaction to fail if its
> results might be inconsistent. Either one will add some complexity
> to your application code.
>

There's a global lock around all of the updates to the table. (This isn't
as unreasonable as it sounds :), but anyway, that's outside the scope of
this discussion.)

Even if there was some kind of race, I'd still get a community id set, it
might be wrong, but it would be set.

regards, tom lane
>

Thanks.

Jim

--
Jim Fulton
http://jimfulton.info