Re: CREATE ASSERTION: database level assertions feature

On Sat, 14 Feb 2026, Tom Lane wrote:

> Marcos Magueta <maguetamarcos(at)gmail(dot)com> writes:
>> I would be willing to at least get started with a patch for this, but
>> before that, I want to assess the interest and thoughts on how to properly
>> implement it.
>
> Everyone who has looked at that has run away screaming. The
> consequences for performance, complexity, deadlock potential,
> etc are all horrible. There are also fun questions about
> appropriate permissions. As the spec is written, it appears
> that anyone with read permission on a table can block updates
> on that table (by creating an assertion that attempted updates
> will fail).

Oracle have introduced a new type of permission to cater for that oddity. I
can't actually remember how their permission system hangs together with respect
to schema ownership, but they allow a schema-level declaration to say whether or
not the owner of schema X can create an assertion about objects in schema Y
(this was gleaned from watching one of their dev videos on this topic).

It strikes me that that might not be granular enough, and you could perhaps
instead allow assertions to be created for only those expressions whose re-check
circumstances match you own mutation rights (as per my other reply on this
thread), but that's probably academic at this point. An alternative would be a
whole set of other permissions too, I guess.

Cheers,
-Joe