Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?

From: Joe Van Dyk <joe(at)tanga(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?
Date: 2013-11-22 02:08:40
Message-ID: CACfv+p+aO1hwi+YjN7Gw8Vcpwn3_LLzQSAN0HhwVu4EuP4hO=w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2013-11-22 02:11:44 Re: Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?
Previous Message Joshua D. Drake 2013-11-22 01:15:30 Re: Primary Key