Re: [GENERAL] PG and undo logging
Ravi Krishna sravikrish...@gmail.com writes: Thanks for the detailed explanation. The explanation makes me wonder that PG must do more work at commit time, right? No. Commit and abort are both O(1). Where we pay the piper is in having to run VACUUM to clean up no-longer-needed row versions. This is a better design in principle, because the necessary maintenance can be done in background processes rather than making clients wait for transactions to finish. In practice, it's still pretty annoying, just in different ways than Oracle's UNDO. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG and undo logging
On Sat, May 23, 2015 at 10:12 PM, Scott Marlowe scott.marl...@gmail.com wrote: Ever run an insert with 1M rows, and roll it back in postgresql and compare that to oracle. Time the rollback in both. That should give you an idea of how differently the two dbs operate. A rollback in postgres is immediate because it's already rolled back so to speak. NONE of it's in the main data store yet, it's all in the transaction log waiting. Oracle spends it's time and effort creating an undo log because it commits every so often, whether or not you've committed your transaction. PostgreSQL doesn't. The whole transaction exists in the transaction log (called xlog dir in pg lingo.) When you roll back a pg transaction it literally requires almost no work. Mark the transaction as aborted etc and get on with life. Transaction logs get cleaned up as usual in the background and we go on our way. This means that Oracle uses space for rollback, while postgres uses space for roll forward (aka the transaction logs) so to speak. Thanks for the detailed explanation. The explanation makes me wonder that PG must do more work at commit time, right? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG and undo logging
On Sat, May 23, 2015 at 1:34 PM, Ravi Krishna sravikrish...@gmail.com wrote: Is it true that PG does not log undo information, only redo. If true, then how does it bring a database back to consistent state during crash recovery. Just curious. What does undo mean? David J.
Re: [GENERAL] PG and undo logging
On May 23, 2015 01:48:11 PM David G. Johnston wrote: On Sat, May 23, 2015 at 1:34 PM, Ravi Krishna sravikrish...@gmail.com wrote: Is it true that PG does not log undo information, only redo. If true, then how does it bring a database back to consistent state during crash recovery. Just curious. What does undo mean? David J. Methinks rolling back the changes that transactions which got interrupted by the crash scribbled onto the data file. It's an Oracleism where not-consolidated data is kept in undo- and redo datafiles. While I roughly understand Postgres' MVCC I don't feel qualified to answer Ravi's question :-)
Re: [GENERAL] PG and undo logging
undo means that reading the WAL logs and able to rollback a row back to its original state before the update. Typically it is used to rollback a long running transaction which got aborted due to a crash. Here is an example: 2.pm You kick off a huge transaction to update say 1 million rows. between 2.01 pm and 2.05pm, the db did multiple checkpoints. 2.06pm - machine crashed. Note that at 2.06, your transaction of 2pm was still running. So when the db starts later on, after redoing all transactions from the last checkpoint @2.05pm till the time of crash 2.06pm, it also has to rollback the 2pm update also because it never got completed. This means, the rollback will have to go past several check points (between 2.01pm and 2.05pm). Hope this explains it clearly. On Sat, May 23, 2015 at 4:48 PM, David G. Johnston david.g.johns...@gmail.com wrote: On Sat, May 23, 2015 at 1:34 PM, Ravi Krishna sravikrish...@gmail.com wrote: Is it true that PG does not log undo information, only redo. If true, then how does it bring a database back to consistent state during crash recovery. Just curious. What does undo mean? David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG and undo logging
On Saturday, May 23, 2015, Ravi Krishna sravikrish...@gmail.com wrote: undo means that reading the WAL logs and able to rollback a row back to its original state before the update. Typically it is used to rollback a long running transaction which got aborted due to a crash. Here is an example: Not an expert here but... It doesn't matter why the newly written data was deemed invalid, MVCC ensures that absent a successful commit all of that data is considered invisible to anything other than the session writing the data. Eventually vacuum is needed to cleanup the dead data. One possible implementation is that uncommitted data simply is never written to the data files...only the buffers see the changes until commit when the WAL sees it. This seems likely but I do not know for sure. David J.
Re: [GENERAL] PG and undo logging
On Sat, May 23, 2015 at 2:34 PM, Ravi Krishna sravikrish...@gmail.com wrote: Is it true that PG does not log undo information, only redo. If true, then how does it bring a database back to consistent state during crash recovery. Just curious. PostgreSQL is NOT oracle. And it doesn't work like oracle. This is normal. Ever run an insert with 1M rows, and roll it back in postgresql and compare that to oracle. Time the rollback in both. That should give you an idea of how differently the two dbs operate. A rollback in postgres is immediate because it's already rolled back so to speak. NONE of it's in the main data store yet, it's all in the transaction log waiting. Oracle spends it's time and effort creating an undo log because it commits every so often, whether or not you've committed your transaction. PostgreSQL doesn't. The whole transaction exists in the transaction log (called xlog dir in pg lingo.) When you roll back a pg transaction it literally requires almost no work. Mark the transaction as aborted etc and get on with life. Transaction logs get cleaned up as usual in the background and we go on our way. This means that Oracle uses space for rollback, while postgres uses space for roll forward (aka the transaction logs) so to speak. -- To understand recursion, one must first understand recursion. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
