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

On Mon, Jun 10, 2019 at 1:30 PM Alex <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
>
>
>
> On Mon, Jun 10, 2019 at 3:28 PM Kuntal Ghosh <kuntalghosh(dot)2007(at)gmail(dot)com> wrote:
>>
>> On Mon, Jun 10, 2019 at 12:15 PM Alex <zhihui(dot)fan1213(at)gmail(dot)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 & Regards,
Kuntal Ghosh
EnterpriseDB: http://www.enterprisedb.com