Re: [PATCH] SQL assertions prototype

On 12/18/13, 10:44 AM, Alvaro Herrera wrote:
> It might prove useful to note that any given assertion involves tables
> A, B, C.  If a transaction doesn't modify any of those tables then
> there's no need to run the assertion when the transaction commits,
> skipping acquisition of the assertion lock.
>
> Probably this can only be implemented for SQL-language assertions, but
> even so it might be worth considering.  (Assertions in any other
> language would be checked by every transaction.)

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
plainSQL to explicitly specify what tables the assert is going to hit, and if the assert tries to do something
differentthen we throw an error.
 

The ability to restrict object access within a transaction would also benefit VACUUM and possibly the Changeset stuff.
Fromhttp://www.postgresql.org/message-id/52ACAAFD.6060509@nasby.net:
 

"This would be useful when you have some tables that see very frequent updates/deletes in a database that also has to
supportlong-running transactions that don't hit those tables. You'd explicitly limit the tables your long-running
transactionwill touch and that way vacuum can ignore the long-running XID when calculating minimum tuple age for the
heavy-hittables."
 
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net