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

Hello

2012/11/25 Luby Liao <lubyliao@gmail.com>:
> Bruce Momjian's book says that (p109)
>
>> When POSTGRESQL updates a row, it keeps the old copy of the row in the
>> table file and writes a new one. The old row is marked as expired, and used
>> by other transactions still viewing the database in its prior state.
>> Deletions are similarly marked as expired, but not removed from the table
>> file.
>
>
> 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

What I know - PostgreSQL doesn't modify btree when tuples are deleted.

Regards

Pavel Stehule

On 25 November 2012 18:03, Luby Liao <lubyliao@gmail.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