Re: [PATCH] SQL assertions prototype - Mailing list pgsql-hackers

Andres Freund <andres@2ndquadrant.com> wrote:
> Peter Eisentraut <peter_e@gmx.net> schrieb:
>> On 12/18/13, 2:22 PM, Andres Freund wrote:
>>> It would only force serialization for transactions that modify
>>> tables covered by the assert, that doesn't seem to bad.
>>> Anything covered by an assert shoulnd't be modified frequently,
>>> otherwise you'll run into major performance problems.
>>
>> I think that makes sense.  If you want to use assertions, you
>> need to run in serializable mode, otherwise you get an error if
>> you modify anything covered by an assertion.
>>
>> In the future, someone could enhance this for other isolation
>> levels, but as Josh has pointed out, that would likely just be
>> reimplementing SSI with big locks.
>
> SSI only actually works correctly if all transactions use SSI...
> I am not sure if we can guarantee that the subset we'd require'd
> be safe without the read sie using SSI.

You could definitely see a state which would not be consistent with
getting to some later state under procedural business rules;
however, I don't think any connection could ever see a state which
violated the constraint as of the moment it was viewed.

For examples of essentially enforcing multi-table constraints using
triggers and SSI see this section:

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

For an example of how things can look OK in terms of enforced
constraints as of different moments in time, yet those moments in
time could be inconsistent, see this section:

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

SSI gives you a guarantee that with any set of concurrently running
transactions, the effect is the same as some serial (one-at-a-time)
execution of those transactions; but it says little about the mix
of serializable and non-serializable transactions. Non-serializable
transactions will, after the last of those serializable
transactions has committed or rolled back, see a state which is
consistent with some serial execution of those serializable
transactions which committed, but it will not necessarily be
consistent with them having run in any *particular* order.  NOTE:
the state might be consistent with some order other than commit
order.  This means that a non-serializable transaction running in
the midst of those serializable transaction commits might see the
work of some transaction which will appear to all serializable
transactions as having been run *later* while not yet seeing the
work of a transaction which will appear to all serializable
transactions to have run *earlier*.

I'm pretty sure that this means that an invariant, if it is an
expression which must always hold for any view of the database, can
be enforced by requiring modifying transactions to be serializable.
What it doesn't guarantee is that business rules about
*transitions* can be enforced without requiring all *transactions*
to be serializable.  In the Deposit Report example, note that a
non-serializable transaction would never be able to see a receipt
with a deposit number that was not open; but it *would* be able to
see a closed batch header with a set of receipts which was not yet
complete.

So I think the answer is that the suggested approach is sufficient
for enforcing assertions about "static" database state.  If you
want to make sure that nobody sees a state for which a given
expression is false, it is sufficient.  Just don't overestimate
what that means.  You can't ensure that a non-serializable
transaction won't see a state which is inconsistent with a later
database state according to *procedural* business rules.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company