Re: Understanding pg_xlog

On Thu, 2005-03-31 at 13:35 +0530, shingav wrote:
> I am trying to understand the way in which PostgreSQL creates, updates
> and uses the transaction log.

If you say why, we might be able to help.

> I browsed through the sources and what I understand is that, when we
> "initdb", the "initdb" binary reads postgres.bki which is created by
> genbki.sh.
>
> But I fail to understand how data is stored (written) in the
> "000001xxxxx" file. I came across some CRC and compression code.
>
> I used a hexeditor to deciphar the contents of "000001xxxx" file but
> it was not of much help.
>
> Are the query (inserts/updates) put as is in the transaction log file ?

The query itself is not stored in the log...this isn't HSQLDB.

Changes made are stored in the xlog, typically changes to data blocks.
The record types show which types of change have been made. Each part of
the system that can write to the database also has a mechanism for
replaying those changes. For example, the REDO for a CREATE DATABASE is
quite different from the REDO for an INSERT into a table (heap). There
are a few actions which are not logged and these are documented.

You'll need to read the code in xlog.c and xlog*.h to understand the
file formats and how things work.

> Does the CRC and compression logic transform the query ?

> While we do a recovery, does it backtrace these CRC and compression
> logic to retrieve the query ?

The CRC confirms that record written have been correctly read back from
the xlog during recovery.

Overall, the mechanism is best understood by reading something like Gray
and Reuter, though there are other good books on transaction processing.
The recovery model looks very similar to the Redo-only type, since
PostgreSQL doesn't support Undo. However, MVCC provides the capability
to ignore uncommitted transaction changes which is effectively
equivalent to Undo. It is my view that PostgreSQL supports the
equivalent of a full with Redo/with Undo recovery model as a result.

Best Regards, Simon Riggs