Re: What happens to a primary key b-tree index when a table tuple is deleted by a transaction?

On 25 November 2012 18:03, Luby Liao <lubyliao(at)gmail(dot)com> wrote:
> If the b-tree changes for the transaction, would it not become broken for
> other transactions?
> Can anyone tell me how Postgres handles this? Thank you, Luby

Unfortunately, that book is a little out of date.

Even with a unique index, there can simultaneously be "duplicate" row
versions (I emphasize the distinction between logical rows and
physical row versions), provided that no two duplicate values are
simultaneously visible to a snapshot - they cannot exist at the same
"time". MVCC doesn't modify rows in place; in creates new row
versions. So, just as with tables, btree indexes will have multiple
row versions for the same logical row.

There is one notable exception to this, though. There was an
optimisation added to PostgreSQL 8.3 called HOT, or heap-only tuples.
This optimisation allows Postgres to use clever tricks to get away
with only having a new row version in tables, and not in each index,
if and only if the UPDATE statement only affects non-indexed columns.

--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services