Re: Why to index a "Recently DEAD" tuple when creating index

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
needthis. 
>>>
>> In this case, the changes of the tuple is not visible yet. Now suppose, your transaction A is serializable and
you'veanother serializable transaction B which can see the index A. It generates a plan that requires to fetch the
deletedtuple through an index scan. If the tuple is not present in the index, how are you going to create a conflict
edgebetween 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
transactionB) 
>>
>
> thanks Ghosh.  Looks your answer is similar with my previous point (transaction is  serializable).   actually if the
transactionB 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 & Regards,
Kuntal Ghosh
EnterpriseDB: http://www.enterprisedb.com