On Mon, Jun 10, 2019 at 1:30 PM Alex <zhihui.fan1213@gmail.com> wrote: > > > > On Mon, Jun 10, 2019 at 3:28 PM Kuntal Ghosh <kuntalghosh.2007@gmail.com> wrote: >> >> On Mon, Jun 10, 2019 at 12:15 PM Alex <zhihui.fan1213@gmail.com> wrote: >>> >>> HEAPTUPLE_RECENTLY_DEAD, /* tuple is dead, but not deletable yet */ >>> >>> It is a tuple which has been deleted AND committed but before the delete there is a transaction started but not committed. Let call this transaction as Transaction A. >>> >>> if we create index on this time, Let's call this index as Index A, it still index this record. my question is why need this. >>> >> In this case, the changes of the tuple is not visible yet. Now suppose, your transaction A is serializable and you've another serializable transaction B which can see the index A. It generates a plan that requires to fetch the deleted tuple through an index scan. If the tuple is not present in the index, how are you going to create a conflict edge between transaction A and transaction B? >> >> Basically, you need to identify the following clause to detect serializable conflicts: >> Transaction A precedes transaction B. (Because, transaction A has deleted a tuple and it's not visible to transaction B) >> > > thanks Ghosh. Looks your answer is similar with my previous point (transaction is serializable). actually if the transaction B can't see the “deleted" which has been committed, should it see the index A which is created after the "delete" transaction? > 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?
2. If we only support "Read Committed" isolation level, is there a safe way to not index such data?