Re: [PATCH] SQL assertions prototype

On 12/18/13, 11:57 AM, Josh Berkus wrote:
> On 12/18/2013 08:44 AM, Alvaro Herrera wrote:
>> Another thought: at the initial run of the assertion, note which tables
>> it locked, and record this as an OID array in the catalog row for the
>> assertion; consider running the assertion only when those tables are
>> touched.  This doesn't work if the assertion code locks some tables when
>> run under certain conditions and other tables under different
>> conditions.  But then this can be checked too: if an assertion lists in
>> its catalog row that it involves tables A, B, C and then, under
>> different conditions, it tries to acquire lock on table D, have the
>> whole thing fail indicating that the assertion is misdeclared.
>
> This sounds like you're re-inventing SSI.
>
> SERIALIZABLE mode *exists* in order to be able to enforce constraints
> which potentially involve more than one transaction.  "Balance can never
> go below 0", for example. The whole reason we have this really cool and
> unique SSI mode is so that we can do such things without killing
> performance.  These sorts of requirements are ideally suited to
> Assertions, so it's logically consistent to require Serializable mode in
> order to use Assertions.

The flip-side is that now you can get serialization failures, and I think there's a ton of software that has no clue
howto deal with that. So now you don't get to use assertions at all unless you re-engineer your application (but see
below).

Now, if Postgres could re-attempt serialization failures, maybe that would become a non-issue... (though, there's
probablya lot of dangers in doing that...)
 

> This is consistent with how we treat the interaction of constraints and
> triggers; under some circumstances, we allow triggers to violate CHECK
> and FK constraints.

We do? Under what circumstances?

> Alternately, we add a GUC assertion_serializable_mode, which can be
> "off", "warn" or "error".  If it's set to "error", and the user triggers
> an assertion while in READ COMMITTED mode, an exception occurs.  If it's
> set to "off", then assertions are disabled, in order to deal with buggy
> assertions.
>
> Now, it would be even better if we could prevent users from switching
> transaction mode, but that's a MUCH bigger and  more complicated patch.

Another possibility is to allow for two different types of assertions, one based on SSI and one based on locking.
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net