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

Jim Fulton <jim@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? 

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

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.

                        regards, tom lane