Point-in-time Recovery (PITR) is the ability to roll the database back to the state it was in at a certain time in the past. That would only include all committed transactions, not transactions that were open in the moment.

In the previous GeneralBits article on Slony we described a way for Slony to enable short term point in time recovery. The technique that Slony enables is back Backup and Point-In-Time capability with a twist.

It is possible, with some scripting, to maintain a delayed slave as a backup which might, for example, be two hours behind the master. This is done by storing and delaying the application of the transaction blocks. With this technique it is possible to do a Point-In-Time-Recovery anytime within the last two hours on this slave. The time it takes to recover will only depends on the time to which you choose to recover. Choosing "45 minutes ago" would take about 1 hour and 15 minutes, for example, independent of database database size.

In Robert Treat's Weekly News* this past week we were pointed to a write up the the current work for Point-In-Time Recovery by Simon Riggs in a message on the hackers list. Robert writes, "it provides a nice summary of PostgreSQL's current crash recovery mechanisms".

First some background from Simon on the information and data used to create point-in-time recovery. In normal usage, transaction records with data in an xlog structure are written to $PGDATA/pg_xlog/ just before a transaction is about to be committed. When the transaction is committed, the location of the transaction information is written to $PGDATA/pg_clog.

For recovery purposes, the last committed transaction in the clog is used to determine the last stable state and the data in the xlog is used, if necessary to rollforward to that state.

Point-in-time recovery is meant to alleviate the recovery process by doing several things that the current recovery system do not cover. These are recognition of dropped database objects, transaction information in xlogs does not extend back to the for which time recovery is desired, and problems with the actual database file systems.

The primary features of this proposal for point-in-time recovery include calculated archiving of the clog and xlog files and using that information to recover when an installation is started up.

There is much more detail in the hackers message itself and no doubt ongoing discussion of the features. Look to pgsql-hackers for more information on this promising work of a much needed feature.

* Don't forget to read Robert Treat's PostgreSQL Weekly news. It is available by subscribing to the pgsql-announce mailing list.

Contributors: elein at varlena.com Robert Treat xzilla at sourceforce.net Simon Riggs simon at 2ndquadrant.com

Transactions in Statements [GENERAL] transactions in plpgsql 19-Mar-2004

When a statement calls a function in plpgsql it can raise an exception to rollback the current transaction. It cannot do any kind of partial rollback to a point inside or outside of the function.

When nested transactions are enabled (we hope for 7.5) then it will be possible to bracket a subtransaction and rollback that part. Until then, any exception raised by any function in the transaction will abort the transaction.

Postgres was designed so that functions are an integral part of a statement. With autocommit on, one statement from a client connection is one transaction. It is very interesting to see all of the visible and behind the scenes functions that can be executed in a function. The lack of limits is quite powerful.

Let us look at a select statement that is used to report on transactions and update accounts. The base select is the implicit start of a transaction. The case statement in the green box is a form of subselect. Each yellow highlighted item is a function call. Of course, now() is a function call within a function call. And the green highlighted items have an implicit function call to cast them to the correct type for the operators.

The function upd_balance() is meant to programmatically update the account balance and return the new balance. The update statement in the upd_balance() function may also have a host of explicit or implicit functions it evokes, for example it may have a trigger to log the update and a few casts to get the types right.

We are not even looking at aggregates, more sophisticated subselects (like IN or EXISTS) and set returning functions returning either datums or rows. Nor does it address more complex join cases.

This is the complexity that must be handled for the case of nested transactions. In each possible branch of the query tree, transaction stability and depth must be maintained. An exception raised four levels deep to abort a statement two levels deep must correctly abort all of actions, related or not, up to the second level statement.

This is a complex problem that is being actively worked on. The functionality of nested transactions will be very valuable, but as we wait, it is good to understand the nature of the problem.

Contributors: Martin Marques martin at bugs.unl.edu.ar, Richard Huxton dev at archonet.com, Gregory Wood gwood at ewebengine.com elein at varlena.com

ACID Definition What does it mean? 20-Mar-2004

ACID stands for Atomicity, Consistency, Isolation and Durability. These are the primary requirements of a system implementing transaction management. Postgres implements transactions and is "ACID Compliant." Although this term is thrown around quite a bit, for those unfamiliar with the term, here is a bit of explanation.

Atomicity means that all of the actions in a transaction are completed or none are. For example, an exception raised inside of a block transaction will abort the entire transaction, from BEGIN to COMMIT. Savepoints and nested transactions are confusing ideas with regards to atomicity. Both allow a partial rollback within a transaction. A savepoint is a marker of sorts, saying that there is a tentative commit at that point in the transaction. A rollback statement is enabled to be invoked to rollback to a savepoint. Savepoints are not implemented (yet?) in PostgreSQL. Nested transactions were discussed in this issue. They allow a sub-transaction to commit or rollback without affecting the parent transaction's ability to commit.

Consistency means that if a transaction is committed a change of data and/or state has occurred and if the transaction is aborted, then the data and/or state has remained unchanged by the transaction. If an update occurred in a transaction that was aborted, then the tuple updated remains untouched.

An interesting deviation of transaction consistency is seen with sequences. Sequences are only guaranteed to be sequential and are allowed by definition to have gaps. Calls to nextval() to increment a sequence are done "out-of-transaction". A statement which calls nextval() will affect the current value of the sequence whether or not the transaction in which it was called was aborted. To see this behavior, try this simple test which uses a SERIAL that invokes nextval() on INSERT:

	CREATE TABLE seqtrial (
	   keyid SERIAL PRIMARY KEY,
	   data  TEXT
	);
	SELECT * FROM seqtrial_keyid_seq;
	INSERT INTO seqtrial VALUES (default, 'first');
	SELECT * FROM seqtrial_keyid_seq;

	BEGIN;
	   INSERT INTO seqtrial VALUES (default, 'second');
	ROLLBACK;

	SELECT * FROM seqtrial_keyid_seq;
	INSERT INTO seqtrial VALUES (default, 'third');
	SELECT * FROM seqtrial;

The term isolation determines the scope or visibility of the transaction in process. It means that any action or data or state change within a transaction is not visible to any other transaction until or unless that transaction is committed. For example there is a transaction that updates my bank account with 50 million dollars. But it is caught by a trigger that checks for very large amounts that are probably in error, alas. The transaction is aborted by the trigger. In the same moment, I am at an ATM querying my account balance exactly at the time the update is made but before the rollback. I will never see the 50 million dollars in the account. Alas.

Durability means that changes made by committed transactions are not transient. If the database is shut down or fails for any reason and the system is restarted, the all changes committed will still be available. In PostgreSQL this is implemented using the write-ahead log (WAL). If the system fails after a commit and before the data is flushed to disk, then the recovery system should be able to restore the data from the log files.

The ACID concept is described in ISO/IEC 10026-1:1992 Section 4.

Contributors: elein at varlena.com