On Thu, Jun 15, 2017 at 1:56 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Jim Fulton <j...@jimfulton.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