Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> Clearly one of those transactions should abort, because that will
> happen in either serialized order. But I don't see where any lock
> is stored, nor how the conflict is detected.
That depends on where in the development cycle of this feature you
are. I'm anticipating that throughout, the locks to support SSI
will be kept in RAM, probably in the existing lock heap table or
something based on it. Near the beginning, all locking will be at
the table level, as the fastest way to develop something which is
"correct" in the sense of not allowing any of the snapshot
anomalies. Later in development, we will try to optimize initial
locks to smaller granularity and promote to coarser granularity only
as needed to keep RAM usage reasonable. Behavior will be no more
"correct" with such optimizations, but it should become more
acceptable in terms of performance and rollback rates. I will not
spend any significant amount of time looking at the specifics of any
particular optimizations yet, because such premature optimization is
certain to kill the whole project.
> There has been a lot of theoretical discussion on this matter, but
> I'd like to know how it will work in this specific case. You can't
> merely lock a few index pages, because the INSERT might put the
> tuple in another page.
I don't yet know a lot about GiST indexes beyond the high-level
theory (it's an area where I haven't yet delved into the code), but
it's pretty easy to get to page level locks if (and only if) an
index search is guaranteed to look at some page which will be
modified if a later conflicting INSERT or UPDATE will be required to
modify either that page or a logically adjacent page. My initial
intuition is that a search can't decide that there are no matching
rows unless it has looked at some page which would be different if a
matching row existed.
> One approach that might work for GiST is to get some kind of lock
> (SIREAD?) on the predicates for the pages that the search does not
> match. That way, the conflict can be detected if an INSERT tries
> to update the predicate of a page to something that the search may
> have matched.
That sounds right to me.
> If the index was GIN instead of GiST, I think the fastupdate
> feature would cause a problem, though (as Greg brought up).
> Fastupdate may need to be disabled when using truly serializable
> transactions.
Again, if I spent the time to evaluate all such details now, we
would never get to the point where such ideas can be examined in
context or quickly tested.
I'm trying to keep this process as open as possible. If I hid in a
corner and worked on this in isolation I could probably (eventually)
present it with answers to all such questions "at the ready." I
think there are obvious down-sides to such a strategy, so I'm forced
into the position of saying, with regards to most potential
optimizations, "we'll cross that bridge when we come to it" --
knowing full well that many optimizations will indeed be necessary
before the patch is acceptable.
I hope that helps.
-Kevin