Re: Major features for 9.1 - Mailing list pgsql-advocacy

Roy Hann <specially@processed.almost.meat> wrote:
> Peter Eisentraut wrote:
>> On ons, 2011-04-06 at 11:01 -0700, Jeff Davis wrote:

>>> One of the use cases of SSI is to enforce constraints, which are
>>> certainly of interest to DBAs.
>>
>> Well, but users can freely change the isolation level, so it
>> would not really be an effective constraint mechanism.

> The checking of constraints is expected to be done using
> serializable isolation regardless of the isolation level of the
> transation that triggered it. See section 4.35.4 of the latest SQL
> standard:
>
> "Regardless of the isolation level of the SQL-transaction,
> phenomena P1, P2, and P3 shall not occur during the implied
> reading of schema definitions performed on behalf of executing an
> SQL-statement, the checking of integrity constraints, and the
> execution of referential actions associated with referential
> constraints."

I'm pretty sure Jeff was talking about enforcing business rules too
complex to be covered by the declarative constraints.  While Jeff's
exclusion constraints greatly expanded the types of constraints on
the data which can be defined declaratively and enforced
independently of transaction isolation level, there will always be
constraints people wish to apply to the data which are too complex
for such declarations to cover, which will require enforcement
through triggers or application code. These are much more easily
developed under consistent use of the serializable transaction level
than less strict levels.

Find any presentation from before SSI on why SERIALIZABLE isn't
truly serializable and how to work around that.  (One such example,
more or less at random, is the "sum of accounts for a person must
meet some minimum balance, which was a fairly popular such example.)
Now remove the workaround to get back to what was the original
failing case and use serializable isolation under 9.1 for all
transactions involved in the example.  It now "just works" without
taking table locks, SELECT FOR UPDATE, or using any other tricks.

http://wiki.postgresql.org/wiki/SSI#Overdraft_Protection

For some shops that has a lot of value.  The question is how to
punch that in release notes.  I expect that this feature will be
most useful, and most likely to be used by, "big" shops.  If you've
got over a dozen programmers actively working on thousands of
transaction types against a schema with several hundred tables,
you're probably going to like this feature a lot.  I don't know if
there's a particular venue where we could reach those people in
particular, but that is the audience which is most likely to be
willing to read a longer explanation of this feature and be
motivated to use it.

-Kevin