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

On Mon, Jun 10, 2019 at 5:26 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Kuntal Ghosh <kuntalghosh(dot)2007(at)gmail(dot)com> writes:
> >> 2. If we only support "Read Committed" isolation level, is there a safe way to not index such data?
>
> > I can't think of a case where the RECENTLY_DELETED tuple needs to be
> > indexed in "Read Committed" case.
>
> I think you're making dangerously optimistic assumptions about how
> long a query snapshot might survive in READ COMMITTED mode.
>
> In particular, I suspect you're reasoning that the new index couldn't
> be used except by a freshly-created query plan, which is possibly
> true, and that such a plan must be used with a freshly-created
> snapshot, which is simply wrong. A counterexample could be built
> using a SQL or PL function that's marked STABLE, because such a
> function is defined to be executed using the calling query's
> snapshot. But it'll make query plans using current reality.
>
Wow. I've not thought of this scenario. Also, I'm not aware about this
different snapshot usage as well. I'll debug the same. Thank you Tom.

So, the READ COMMITTED mode will also cause this kind of issues.

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