Re: [pgsql-general] Daily digest v1.5657 (16 messages)

Florian,
Reponses from, an ex-Oracle DBA, below.

On Mon, 2005-10-24 at 11:51 -0300, pgsql-general-owner(at)postgresql(dot)org
wrote:
> Date: Mon, 24 Oct 2005 14:29:24 +0200
> From: Florian Ledoux <florian(dot)ledoux(at)gmail(dot)com>
> To: pgsql-general(at)postgresql(dot)org
> Subject: pg_dump, MVCC and consistency
> Message-ID: <d4f1fdd90510240529p64a9980fl(at)mail(dot)gmail(dot)com>
>
> Hello everybody !
>
> I am coming from the (expensive) "Oracle World" and I am a newbie in
> PG administration. I am currently working on backup concerns... I am
> using pg_dump and I have not encountered any problems but I have some
> questions about the internal management of data consistency in PG
> server.
> I have read some articles about the MVCC mechanism but I can't see how
> it handles a consistent "snapshot" of the database during all the
> export process.

The whole secret, as I understand it, is that updates and deletes do not
overwrite the original tuple. The original tuple remains in place,
marked with transaction ids describing the transactions to which it is
visible. These "old" tuples remain until a vacuum is performed. The
vacuum removes only those tuples which are no longer visible to any
running transaction.

> 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 ?

I believe pg_dump uses 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 ?

One of the greats joy of postgres is never seeing a "snapshot too old"
error. There is no rollback or undo space required as the original
tuples remain in place. This has other benefits too - you don't have to
reconstruct the original tuple from rollback in order to retrieve it,
making selects faster, and you don't have to write rollback data, making
writes faster.

> I have not a good knowledge of PG internal mechanism, I hope that my
> questions are clear enough...
>
Yep. I hope the answers were too.

> Florian
>
__
Marc