Обсуждение: 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 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? I should have written: Tom -> non-repeatable read Jim -> phantom read but the point remains... Mike Mascari mascarm@mascari.com