Re: Documenting serializable vs snapshot isolation levels

On Fri, 2009-01-02 at 13:47 -0500, Tom Lane wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> >> What do you mean by referential integrity?  I don't believe you can
> >> construct a foreign key problem at any transaction isolation level.
>  
> > I mean that if someone attempts to maintain referential integrity with
> > SQL code, without using explicit locks, it is not reliable. 
> > Presumably the implementation of foreign keys in PostgreSQL takes this
> > into account and blocks the kind of behavior shown below.  This
> > behavior would not occur with true serializable transactions.
> 
> IIRC the RI code has to fudge the normal serializable-snapshot behavior
> in order to guarantee no constraint violation --- it has to be aware of
> concurrent changes that would otherwise be invisible to a serializable
> transaction.

...just to add that this is exactly as required by SQL Standard, i.e. RI
works in Read Committed mode even within a Serializable transaction.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support