Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux