From: | Jim Nasby <jim(at)nasby(dot)net> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> |
Cc: | Andres Freund <andres(at)2ndquadrant(dot)com>, Kevin Grittner <kgrittn(at)ymail(dot)com>, Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PATCH] SQL assertions prototype |
Date: | 2013-12-18 19:26:50 |
Message-ID: | 52B1F6FA.1090800@nasby.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 how to 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 probably a 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(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2013-12-18 19:35:17 | Re: [PATCH] SQL assertions prototype |
Previous Message | Andres Freund | 2013-12-18 19:22:28 | Re: [PATCH] SQL assertions prototype |