Re: Serializable Snapshot Isolation

Greg Stark  wrote:
> Just to be clear I wasn't saying it was or wasn't a problem, I was
> just trying to see if I understand the problem and if I do maybe
> help bring others up to speed.
Thanks for that, and my apologies for misunderstanding you.  It does
sound like you have a firm grasp on my concern, and you expressed it
clearly and concisely.
To build on what you said, there are two properties which bother me
about a backup based on snapshot isolation in an environment where
data integrity is enforced with application code, including
user-written triggers.
(1) If the backup is used to make a copy of the database for running
reports, while the main database continues to be updated, it could
represent a state which never existed in the database, at least as
visible to serializable transactions.  This makes any reports run
against it of dubious value.
(2) It may not be possible to update such a copy of the database to
the later state of the database using replay of the same transaction
stream, in any order -- particularly if the recovery counts on firing
of the same triggers to supply default data or enforce integrity
rules which were in place during the initial run.  To continue with
the receipting example, replaying the receipt which was in flight
during the pg_dump run would result in assigning the wrong deposit
date. This could cause problems for some replication or recovery
strategies, although it's not apparent that it breaks anything
actually shipped in the base PostgreSQL distribution.
FWIW, it seems premature to spend a lot of time on the concept of the
DEFERRABLE (or whatever term we might choose) transaction snapshots. 
I think I'll update the patch to use REPEATABLE READ for pg_dump for
now, and we can revisit this as a possible enhancement later.  As you
point out, it doesn't really impact the usefulness of the dump for
crash recovery beyond the issue I mention in point 2 above, and
that's only going to come into play for certain recovery strategies. 
Even then, it's only an issue if pg_dump gets its snapshot while
certain very specific conditions exist.  And we're certainly in no
worse shape regarding these concerns with the patch than without;
they're long-standing issues.
-Kevin