Re: pg_dump, MVCC and consistency

On Mon, Oct 24, 2005 at 02:29:24PM +0200, Florian Ledoux wrote:
> If I have well understood, the defaut transaction isolation level in
> PG is the "read commited" isolation level. If it is the isolation
> scheme used by pg_dump how can I be sure that tables accessed at the
> end of my export are consistent with those accessed at the begining ?
> Does pg_dump use a serializable isolation scheme ?

There are at least two ways to find out: examine the source code
or enable query logging on the server. You'll discover that a
pg_dump session starts with:

BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

> We have this kind of concerns with Oracle and a "CONSISTENT" flag can
> be set in the exp utility to use a consistent snapshot of the database
> from the begining to the end of the export process. Unfortunately,
> this mode use intensively rollback segments and can drive to obsolete
> data (also knows as "Snapshot too old"). Is there the equivalent of
> rollback segments in PG ? Is there some issues like "snapshot too old"
> with intensive multi-users and transactional databases ?

PostgreSQL uses MVCC to get snapshots. See "Concurrency Control"
and "Preventing transaction ID wraparound failures" in the documentation
for discussion of how this works, what problems you might encounter,
and how to avoid them.

http://www.postgresql.org/docs/8.0/interactive/mvcc.html
http://www.postgresql.org/docs/8.0/interactive/maintenance.html#VACUUM-FOR-WRAPAROUND

PostgreSQL 8.1 makes checks to avoid data loss due to transaction
ID wraparound, but there's one situation I'm not sure how it handles:
when a transaction is so long-lived that it would appear to be in
the future of newly-created transactions due to wraparound. I'd
have to dig into the source code to find out if that's possible,
and if so, what happens. Maybe one of the developers will comment.

--
Michael Fuhr