* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> >> The whole problem with search_path and role is very frustrating. We've
> >> taken to just hacking things to be dynamic SQL whenever it's
> >> role-specific, but that's a really poor solution. I wonder if it would
> >> be possible to have the function and prepare'd plan caches be key'd off
> >> of the search_path and role too..? So if you change one of those you
> >> end up having to re-plan it, but then that's also cached, etc..
>
> FWIW, I can see the point of making cached plan lookup be
> search-path-specific. But why does the active role need to factor
> into it?
Because of $user being used in the search_path. Thinking about it, I'm
sure we'd handle the look-up at query time and would resolve the $user
in the search_path to an actual schema first, so the cache doesn't need
to be keyed off role itself.
IOW, yeah, you're right, the role doesn't really matter. One thing that
occurs to me when I last ran into a problem with this- it was painful to
debug because the "permission denied" error didn't indicate which schema
the table it was trying to access was in. I wonder if we could fix
that.
Thanks,
Stephen