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

On Mon, Jun 10, 2019 at 4:10 PM Kuntal Ghosh <kuntalghosh(dot)2007(at)gmail(dot)com>
wrote:

> 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, 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?

--
> Thanks & Regards,
> Kuntal Ghosh
> EnterpriseDB: http://www.enterprisedb.com
>