Thread: PG and undo logging
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. thanks.
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.
On May 23, 2015 01:48:11 PM David G. Johnston wrote:
> On Sat, May 23, 2015 at 1:34 PM, Ravi Krishna <sravikrishna3@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 :-)
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.johnston@gmail.com> wrote: > On Sat, May 23, 2015 at 1:34 PM, Ravi Krishna <sravikrishna3@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. >
On Saturday, May 23, 2015, Ravi Krishna <sravikrishna3@gmail.com> wrote:
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.
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.
David J.
On Sat, May 23, 2015 at 2:34 PM, Ravi Krishna <sravikrishna3@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.
On Sat, May 23, 2015 at 10:12 PM, Scott Marlowe <scott.marlowe@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?
Ravi Krishna <sravikrishna3@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