Re: Weird indices

Ian Lance Taylor wrote:
>
> Joseph Shraibman <jks(at)selectacast(dot)net> writes:
>
> > > > I understand that keeping different views for different open
> > > > transactions can be difficult, but after a transaction that updates a
> > > > row is over why isn't the row marked as 'universally visible' for all
> > > > new transactions until another update occurs?
> > >
> > > It is. This mark is on the tuple in the heap. When a tuple is
> > > current, and not locked for update, HEAP_XMAX_INVALID is set. After
> > > the tuple is removed, HEAP_XMAX_COMMITTED is set.
> >
> > On the heap, but when is the index updated? Not until the next vacuum?
>
> The index is updated right away. Otherwise it could never be used,
> since it would be inaccurate.

I meant cleaned up. Which you answered: when vacuumed.

<snip>
>
> Note that this all implies that when walking through the index to find
> heap tuples, you must check the current validity of each heap tuple.
> It is normal for an index tuple to point to a heap tuple which has
> been deleted.

<snip>
> >
> > I'm talking about indices. The index should be updated to only point at
> > valid rows.
>
> When should the index be updated to only point at valid rows? That is
> only possible when a heap tuple is finally and completely removed.
> But currently that is only known at the time of a VACUUM.
>
You just said above 'It is normal for an index tuple to point to a heap
tuple which has been deleted.'

> Consider a transaction which sits around for a while and then aborts.
> At the moment that the transaction aborts, Postgres may become able to
> remove some heap tuples and some index tuples, and it might be invalid
> for Postgres to remove those tuples before the transaction aborts.
>
> But the transaction might never have looked at those tuples. So,
> given the Postgres data structures, the only way to keep an index
> fully up to date would be to effectively run a VACUUM over the entire
> database every time a transaction aborts.

Why? There is a mechanism for keeping track of which heap tuples are
valid, why not index tuples? It is the nature of indices to be updated
on inserts, why not deletes? I would think that the advantage of being
able to use the index in the planner would outweigh the immediate cost
of doing the update.

<snip>
>
> > But if the index isn't used by the planner then the point
> > is moot.
>
> As far as I know the index itself isn't used by the planner.
>
But could be. As I understand it the reason the index isn't used by the
planner is because the index could point at non-visible rows (row = heap
tuple). If the index could be used, many things now that are seq scans
could be converted to faster index scans.

<snip>

> I don't think there is any way to do that today. It would be possible
> to implement something along the lines I suggest above. I have no
> idea if the Postgres maintainers have any plans along these lines.
>
At the end of a transaction, when it sets the bit that this tuple isn't
valid, couldn't it at the same time also remove it if was no longer
visible to any transaction? It wouldn't remove the need for vacuum
because there may be another transaction that prevents it from being
removed right then and there.

But for index tuples we could use your list system because (as I see it)
the value of being able to use the index in the planner would outweigh
the cost of the list system.

> Ian

--
Joseph Shraibman
jks(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com