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
