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 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. From http://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 support long-running transactions that don't hit those tables. You'd explicitly limit the tables your long-running transaction will touch and that way vacuum can ignore the long-running XID when calculating minimum tuple age for the heavy-hit tables."
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net