What happens to a primary key b-tree index when a table tuple is deleted by a transaction?
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general