On Mon, 2 Jun 2025 at 23:30, Tom Lane <t...@sss.pgh.pa.us> wrote: > Isaac Morland <isaac.morl...@gmail.com> writes: >
> My fix would > > be for check constraints, triggers, and view definitions to run as the > > owner of the object in question (constraint, trigger, or view or > > materialized view), essentially using the same facility as used to run > > security definer functions. Then, as an optimization only, skip actually > > doing the security definer stuff (which I understand to be slow) when it > > can be proven by the planner to be safe to do so (i.e., no difference in > > result). > > I am interested to know how you think the planner could prove that. > The same general way it establishes that any other transformation is OK: it recognizes patterns that are known to allow the application of an optimization technique. Inevitably, not just in practice but even in theory due to well-known basic results in the theory of computability, there will be situations where optimizations could be applied but which will not be recognized by the planner. So for example maybe when a check constraint is defined the system could check to see if it consists entirely of calls to stable functions provided with the system and if so marks it as safe to run as the effective user rather than as the constraint owner. I foresee all sorts of complications including unforeseen ones but it should be clear that I not proposing to violate any theorems of Gödel.
