Re: Multi-Versions and Vacuum -- cf Oracle & Vacuum alt
A combined response to several posts. I am not advocating Oracle in
general, just noting differences and places where Postgres might benefit.
ORACLE MVC
Oracle definitely has MVC. By default it is in Read Committed mode. But
you can still get record locks in both Postgresql and Oracle if you Select
FOR UPDATE, which you must do in Read Committed mode to produce correct
transactions.
ORACLE LOCKING
My note about Oracle is that in its Read Committed mode transactions are
safer without being Serializable. See http://www.SimpleORM.org/DBNotes.html
for details. The difference is subtle but it is is important because
otherwise report may not be consistent. And I do not think that there is
any down side. So I commend the approach to the Postgres community.
TRANSACTION LOG ALTERNATIVE TO VACUUM AND ROLLBACK SEGMENTS
Oracle's use of Rollback segments to implement MVC has several issues. But
it does stop the main database blocks becoming cluttered with old data.
But rather than try to make vacuum faster, incremental etc. Why not just
use the transaction log to implement MVC and avoid the whole problem?
The idea is that you only store the current row's value in the main
database. You are already keeping track of the updated row values in the
transaction log, and presumably at least one copy of those logs will be on
disk, ie. allow random access.
So if you find that you need to get an old version of a row, just look up
the log files. And that can be very efficient because the new row can keep
a pointer to the log block containing the next older version. And as that
block is likely to have been recently written to the disk it is likely to be
in the cache anyway.
In this way you only pay for MVC when you actually need to read behind a new
row. The approaches used by Postgres and Oracle mean that you pay for the
MVC every time you update a row. You also pay for them every time you
retrieve a row in Postgres because the blocks contain extra data and so the
cache will not be as effective.
In practice, you will only need the MVC read behinds if you are running
updating transactions AND reports on the same database AT THE SAME TIME. If
you are doing a lot of that, build a wharehouse.
Anthony
Anthony Berglas wrote:
A combined response to several posts. I am not advocating Oracle in
general, just noting differences and places where Postgres might benefit.ORACLE MVC
Oracle definitely has MVC. By default it is in Read Committed mode. But
you can still get record locks in both Postgresql and Oracle if you Select
FOR UPDATE, which you must do in Read Committed mode to produce correct
transactions.ORACLE LOCKING
My note about Oracle is that in its Read Committed mode transactions are
safer without being Serializable. See http://www.SimpleORM.org/DBNotes.html
for details. The difference is subtle but it is is important because
otherwise report may not be consistent. And I do not think that there is
any down side. So I commend the approach to the Postgres community.
I just tried this in Oracle 8.0.5:
CREATE TABLE employees (key integer, value varchar(32);
Session #1
---------------
SQL> insert into employees values (1, 'Mike');
SQL> insert into employees values (2, 'Joe');
SQL> commit;
SQL> insert into foo values (1) <-- To ensure a new xact
SQL> select * from employees;
1 Mike
2 Joe
Session #2:
---------------
SQL> select * from employees;
1 Mike
2 Joe
SQL> update employees set value = 'Tom' where key = 1;
SQL> insert into employees values (3, 'Jim');
SQL> delete from employees where key = 2;
SQL> select * from employees;
1 Tom
3 Jim
Session #1:
---------------
SQL> select * from employees;
1 Mike
2 Joe
Session #2:
---------------
SQL> commit;
Session #1:
---------------
SQL> select * from employees;
1 Tom
3 Jim
So when you say:
"By memory, Oracle has similar behaviour in Read Committed mode except
that the PostCommitSelect would NOT show the changes made by session
two, and thus be more serializable."
it doesn't match the behavior with Oracle 8.0.5. Tom and Jim are phantom
reads, as expected, in Read Committed mode. Perhaps this has changed in
version 9?
Mike Mascari
mascarm@mascari.com
I (Mike Mascari) wrote:
Anthony Berglas wrote:
A combined response to several posts. I am not advocating Oracle in
general, just noting differences and places where Postgres might benefit.ORACLE MVC
Oracle definitely has MVC. By default it is in Read Committed mode. But
you can still get record locks in both Postgresql and Oracle if you Select
FOR UPDATE, which you must do in Read Committed mode to produce correct
transactions.ORACLE LOCKING
My note about Oracle is that in its Read Committed mode transactions are
safer without being Serializable. See http://www.SimpleORM.org/DBNotes.html
for details. The difference is subtle but it is is important because
otherwise report may not be consistent. And I do not think that there is
any down side. So I commend the approach to the Postgres community.I just tried this in Oracle 8.0.5:
..
Session #1:
---------------
SQL> select * from employees;1 Tom
3 JimSo when you say:
"By memory, Oracle has similar behaviour in Read Committed mode except
that the PostCommitSelect would NOT show the changes made by session
two, and thus be more serializable."it doesn't match the behavior with Oracle 8.0.5. Tom and Jim are phantom
reads, as expected, in Read Committed mode. Perhaps this has changed in
version 9?
I should have written:
Tom -> non-repeatable read
Jim -> phantom read
but the point remains...
Mike Mascari
mascarm@mascari.com
Thanks for doing the test in Oracle. What I did not see is you setting the
Isolation Level. Or maybe that is what I forgot to do when I did similar
tests some time ago! (I don't have Oracle handy or I'd try again.)
However, the point remains, that there is no reason why PostgreSQL could not
provide more serialized transactions in Read Committed mode without taking
the hits with rollback that Serializable mode gives.
Anthony
PS. Any feedback on the idea of using the transaction log to implement MVC
would be appreciated.
Show quoted text
ORACLE MVC
Oracle definitely has MVC. By default it is in Read
Committed mode. But
you can still get record locks in both Postgresql and
Oracle if you Select
FOR UPDATE, which you must do in Read Committed mode to
produce correct
transactions.
I just tried this in Oracle 8.0.5:
Session #1:
---------------
SQL> select * from employees;1 Tom
3 JimSo when you say:
"By memory, Oracle has similar behaviour in Read Committed mode except
that the PostCommitSelect would NOT show the changes made by session
two, and thus be more serializable."it doesn't match the behavior with Oracle 8.0.5. Tom and Jim
are phantom
reads, as expected, in Read Committed mode. Perhaps this has
changed in
version 9?Mike Mascari
mascarm@mascari.com
Import Notes
Resolved by subject fallback
Anthony Berglas wrote:
Thanks for doing the test in Oracle. What I did not see is you setting the
Isolation Level. Or maybe that is what I forgot to do when I did similar
tests some time ago! (I don't have Oracle handy or I'd try again.)However, the point remains, that there is no reason why PostgreSQL could not
provide more serialized transactions in Read Committed mode without taking
the hits with rollback that Serializable mode gives.
Yes, we could probably do it, but it doesn't make sense for SELECT to
use SERIALIZABLE while in the same transaction UPDATE/DELETE is using
READ COMMITTED. Seems that would cause all sorts of confusion.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026