On Mon, Jun 10, 2019 at 2:12 PM Alex <zhihui.fan1213@gmail.com> wrote: > On Mon, Jun 10, 2019 at 4:10 PM Kuntal Ghosh <kuntalghosh.2007@gmail.com> wrote: >> I think what I'm trying to say is different. >> >> For my case, the sequence is as following: >> 1. Transaction A has deleted a tuple, say t1 and got committed. >> 2. Index A has been created successfully. >> 3. Now, transaction B starts and use the index A to fetch the tuple >> t1. While doing visibility check, transaction B gets to know that t1 >> has been deleted by a committed transaction A, so it can't see the >> tuple. But, it creates a dependency edge that transaction A precedes >> transaction B. This edge is required to detect a serializable conflict >> failure. >> >> If you don't create the index entry, it'll not be able to create that edge. > > > Thanks, I got the difference now, but still not get the necessity of it. > 1. Assume we don't index it, in which situation we can get a wrong result?
Consider the following sequence of three different transactions X,A and B:
1. Transaction X reads a tuple t2. 2. Transaction A updates the tuple t2, deletes a tuple t1 and gets committed. So, there transaction X precedes transaction A, i.e., X <- A. 3. Index A is created successfully. 4. Transaction B starts and use the index A to fetch tuple t1. But, it's already deleted by the committed transaction A. So, transaction A precedes transaction B, i.e., A<-B. 5. At this point you've a dangerous structure X<-A<-B (definition of dangerous structure src/backend/storage/lmgr/README-SSI) in the graph which can produce an anomaly. For example now, if X tries to update another tuple previously read by B, you'll have a dependency B<-X. But, you already have X<-B which leads to serializable conflict. Postgres tries to resolve this anomaly by rolling back one of the transaction.
In your case, it'll be difficult to detect.
> 2. If we only support "Read Committed" isolation level, is there a safe way to not index such data? > I can't think of a case where the RECENTLY_DELETED tuple needs to be indexed in "Read Committed" case. So, your suggestion likely to work logically in "Read committed" isolation level. But, I'm not sure whether you'll encounter any assertion failures in vacuum path or concurrent index paths.
