Is there a way to "revive" a dead tuple in postgres?

Question

Since DELETE does not physically removes the data and just marks it for further cleaning, i wonder, can the 'dead' status in a tuple version inside the file page ever be changed for 'redirect' or 'unused'? Will it happen after insertion of a new tuple version? As far as i'm concerned, 'redirect' status is assigned to a tuple after in-page cleaning for maintainig HOT-updated chains of pointers, so basically, can a dead tuple be not dead again?

Answer 1

According to your comment, you are talking about the lp_flags in an item pointer, defined in src/include/storage/itemid.h as LP_UNUSED, LP_NORMAL, LP_REDIRECT and LP_DEAD.

A line pointer that is marked LP_DEAD has to be reclaimed (set to LP_UNUSED) by VACUUM, then it is no longer referenced by any index and can be reused.