Re: [PATCH] SQL assertions prototype

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.

I'm leaning towards the alternative that Assertions require SERIALIZABLE
mode, and throw a WARNING at the user and the log every time we create,
modify, or trigger an assertion while not in SERIALIZABLE mode. And
beyond, that, we don't guarantee the integrity of Assertions if people
choose to run in READ COMMITTED anyway.

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.

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.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com