Re: [PATCH] SQL assertions prototype

On 12/18/13, 1:42 PM, Kevin Grittner wrote:
> Jim Nasby <jim(at)nasby(dot)net> wrote:
>
>> This is another case where it would be very useful to restrict
>> what relations a transaction (or in this case, a substransaction)
>> can access. If we had the ability to make that restriction then
>> we could force assertions that aren't plain SQL to explicitly
>> specify what tables the assert is going to hit, and if the assert
>> tries to do something different then we throw an error.
>>
>> The ability to restrict object access within a transaction would
>> also benefit VACUUM and possibly the Changeset stuff.
>
> I'm pretty sure that SSI could also optimize based on that,
> although there are probably about 10 other optimizations that would
> be bigger gains before getting to that.

Any ideas how hard this would be? My thought is that we might be able to perform this check in the functions that do catalog lookups, but I'm worried that that wouldn't allow us to support subtransaction checks (which we'd need for assertions), and it runs the risk of long-lasting object references spanning the transaction (or subtransaction) and thereby thwarting the check.

Another option would be in heap accessor functions, but that means we could only restrict access to tables. For assertions, it would be nice to also disallow access to functions that could have unintended consequences that could violate the assertion (like dblink).
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net