Re: Set search_path + server-prepared statements = cached plan must not change result type

On Thu, Jan 28, 2016 at 7:48 AM, Vladimir Sitnikov <
sitnikov(dot)vladimir(at)gmail(dot)com> wrote:

> Robert>Hmm, so in your example, you actually want replanning to be able to
> Robert>change the cached plan's result type?
>
> I want backend to cache _several_ plans behind a single "statement name".
> I want to treat "prepare...exec...deallocate" dance as an optimization
> step for a simple "exec...exec...exec" sequence.
> I do not want to care if "previously prepared query is still valid or
> not". For instance, I do not want to check if search_path is still the
> same.
>
> Current backend implementation does not report changes to
> "search_path", thus clients have no solid way to detect "search_path
> changes".
>
> David>Maybe call the new command "PARSE name AS query".
>
> From JDBC perspective, there is no need in "prepare vs parse" distinction:
> 1) Explicit "prepare...execute" are not used in typical application code
> 2) That means, in 99.9% cases, "prepare" would be used by the jdbc driver
> itself
> 3) Thus just a single "protocol command" is sufficient.
>
> What I am saying is there are lots of consumers that want to avoid
> parsing overhead: plpgsql, pgjdbc, pgjdbc-ng, postgresql-async,
> 8kdata/phoebe, etc, etc.
>
> All of them will have to deal with search_path vs prepare issue.
> If you suggest to deprecate "prepare" in favor of "parse", then all of
> the above clients would have to switch to that "parse".
> It does not look like a good solution, since lots of existing clients
> assume "prepare just works".
>
> If "prepare" command gets deprecated, why "parse" would be better?
> What would be the use of "prepare" if all the clients would have to
> use "parse" in order to be search_path-compatible?
>
>
​Further pondering on this topic reveals that I need a more solid
understanding of the underlying layers...I'm not really sure at this point
whether further redefining the behavior of PREPARE is as undesirable as it
first seemed to be. It does impose some constraints and makes assumptions
in order to provides its capability and so instead of trying to add yet
more complexity to it in order to fulfill this different use case it can at
least be considered that a different module be provided as a solution. I
guess if it got to the point where the new facility could supersede PREPARE
you would just modify PREPARE but if they end up performing two different
things then no deprecation would be involved.

David J.