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

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 & Regards,
Kuntal Ghosh
EnterpriseDB: http://www.enterprisedb.com