Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?
I had a function that was set to SECURITY INVOKER. I needed to give access
to a view that uses this function to a role, so I made the function
SECURITY DEFINER.
The function is STABLE and is usually inlined and takes 2 ms to run.
Immediately, the function quit being inlined and took 1500ms to run.
Changing the function back to SECURITY DEFINER let the function be inlined
again.
On postgresql 9.3.1.
Is this expected behavior?
Joe Van Dyk <joe@tanga.com> writes:
I had a function that was set to SECURITY INVOKER. I needed to give access
to a view that uses this function to a role, so I made the function
SECURITY DEFINER.
The function is STABLE and is usually inlined and takes 2 ms to run.
Immediately, the function quit being inlined and took 1500ms to run.
Changing the function back to SECURITY DEFINER let the function be inlined
again.
On postgresql 9.3.1.
Is this expected behavior?
Yes. SECURITY DEFINER functions can't be inlined --- there would be
noplace to effect the change of user ID.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Nov 21, 2013 at 6:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Joe Van Dyk <joe@tanga.com> writes:
I had a function that was set to SECURITY INVOKER. I needed to give
access
to a view that uses this function to a role, so I made the function
SECURITY DEFINER.The function is STABLE and is usually inlined and takes 2 ms to run.
Immediately, the function quit being inlined and took 1500ms to run.
Changing the function back to SECURITY DEFINER let the function be
inlined
again.
On postgresql 9.3.1.
Is this expected behavior?
Yes. SECURITY DEFINER functions can't be inlined --- there would be
noplace to effect the change of user ID.regards, tom lane
Thanks. Is that documented somewhere? I looked, couldn't find anything.