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