Re: How is statement level read consistency implemented?

> Pavan Deolasee, 22.04.2008 12:57:
> > On Tue, Apr 22, 2008 at 4:11 PM, Thomas Kellerer
<spam_eater(at)gmx(dot)net>
> wrote:
> >>
> >> I am interested in the technical details on how PG determines that
a
> block
> >> needs to be read from from "some other place than the data block"
> because
> >> another transaction has updated the data block.
> >>
> > Postgres uses MVCC for transaction consistency. When a row is
updated,
> > a new copy of the row is created and the old version is marked for
> > deletion (though its not removed immediately). The old readers
> > continue to read from the old copy whereas new transactions see the
> > new copy. This is all managed by XMIN and XMAX which are transaction
> > ids of the inserting and deleting transactions respectively and
> > control the visibility of the different versions.
> >
> > If you want to understand MVCC, please see this excellent
> > documentation by Tom Lane:
> > http://www.postgresql.org/files/developer/transactions.pdf
>
> The xmin, xmax info is basically what I was looking for.
>
> As far as I can tell (from the PDF and your quote) Postgres uses a
very
> similar concept as Oracle. .
> Each transaction has a uniqe number and each tuple contains the
> information for which transaction number it is visible.

Oracle moves the old row(s) to the rollback segment instead of putting a
new row in the table as PostgreSQL does. The new value goes right in
place of the old row and it adds a pointer to the rollback segment.

A commit removes the pointer and a rollback forces the db to move the
old row from the rollback segment back in place of the row updated.
Oracle's rollback segment can be tuned so that retains data up to a
certain age and then it is flushed. If rollback is too small, you can
get a "snapshot too old" error.

Back to PostgreSQL, when a vacuum is issued, the old deleted rows are
removed. This can be done with the autovacuum feature or manually.

-Does vacuum make sure there are no long running queries referencing the
deleted rows before it attempts to remove the old rows?
-With autovacuum, does it skip these rows still being referenced in a
transaction or does it wait?

I'm guessing that you will never get the snapshot too old error in
PostgreSQL based on the architecture.

Jon