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

Vladimir Sitnikov <sitnikov.vladimir@gmail.com> writes:

Here's my question: why change in search_path does NOT generate
ParameterStatus message from the backend?

The protocol specification includes a list of the small number of GUCs
for which ParameterStatus messages are generated, and that is not one
of them.

This has been discussed before, I believe, and the conclusion was that
if you think you need this, you're doing it wrong. More robust solutions
involve having individual functions use SET clauses to locally force
search_path to be what they want. If we had search_path marked as
GUC_REPORT, that would both lead to a storm of useless client messages
when such techniques were in use, and encourage people to use methods
that won't really work reliably.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I believe, and the conclusion was that
if you think you need this, you're doing it wrong

So what is the recommended approach to use server-prepared statements
at the client side (I mean at JDBC driver side)?

Currently "prepare, switch search_path, execute" leads to "cached plan
must not change result type" error.
Can one expect the issue to be fixed in subsequent 8.4, 8.5, ..., 9.5 versions?

Vladimir

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jan 20, 2016 at 10:23 AM, Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:

I believe, and the conclusion was that
if you think you need this, you're doing it wrong

So what is the recommended approach to use server-prepared statements
at the client side (I mean at JDBC driver side)?

Currently "prepare, switch search_path, execute" leads to "cached plan
must not change result type" error.
Can one expect the issue to be fixed in subsequent 8.4, 8.5, ..., 9.5 versions?

Are you really seeing the same behavior in all versions? Because I
thought we changed this pretty significantly in this commit:

commit 0d5fbdc157a17abc379052f5099b1c29a33cebe2
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Jan 25 14:14:41 2013 -0500

Change plan caching to honor, not resist, changes in search_path.

In the initial implementation of plan caching, we saved the active
search_path when a plan was first cached, then reinstalled that path
anytime we needed to reparse or replan. The idea of that was to try to
reselect the same referenced objects, in somewhat the same way that views
continue to refer to the same objects in the face of schema or name
changes. Of course, that analogy doesn't bear close inspection, since
holding the search_path fixed doesn't cope with object drops or renames.
Moreover sticking with the old path seems to create more surprises than
it avoids. So instead of doing that, consider that the cached plan depends
on search_path, and force reparse/replan if the active search_path is
different than it was when we last saved the plan.

This gets us fairly close to having "transparency" of plan caching, in the
sense that the cached statement acts the same as if you'd just resubmitted
the original query text for another execution. There are still some corner
cases where this fails though: a new object added in the search path
schema(s) might capture a reference in the query text, but we'd not realize
that and force a reparse. We might try to fix that in the future, but for
the moment it looks too expensive and complicated.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert>Are you really seeing the same behavior in all versions?

I do not have "pre 9.1" at hand, however all 9.1, 9.2, 9.3, 9.4, and
9.5 are affected.

9.1 just silently executes "old statement" as if search_path was not
modified at all.
9.2, 9.3, 9.4, and 9.5 all fail with "cached plan must not change
result type" error.

See java-based test in [1]https://github.com/pgjdbc/pgjdbc/commit/8fcd07a24666de308419d54e49e2f65f40661e2a#diff-526a72847ed4c9f31f699515d06e508bR188, and build logs for 9.1-9.4 in [2]https://travis-ci.org/pgjdbc/pgjdbc/builds/103940843

I do not have "brand new 9.5", however I think 9.5rc1 is good enough:
"PostgreSQL 9.5rc1 on x86_64-apple-darwin15.2.0, compiled by Apple
LLVM version 7.0.0 (clang-700.1.76), 64-bit"

Here's my test case:

select version();

create schema customer1;
create table customer1.test(i int4);

create schema customer2;
create table customer2.test(i varchar);

set search_path to customer1,public;
prepare stmt as select * from test;

set search_path to customer2,public;

execute stmt;

--ERROR: cached plan must not change result type
--SQL state: 0A000

[1]: https://github.com/pgjdbc/pgjdbc/commit/8fcd07a24666de308419d54e49e2f65f40661e2a#diff-526a72847ed4c9f31f699515d06e508bR188
[2]: https://travis-ci.org/pgjdbc/pgjdbc/builds/103940843

Vladimir

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jan 21, 2016 at 3:55 PM, Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:

Robert>Are you really seeing the same behavior in all versions?

I do not have "pre 9.1" at hand, however all 9.1, 9.2, 9.3, 9.4, and
9.5 are affected.

9.1 just silently executes "old statement" as if search_path was not
modified at all.
9.2, 9.3, 9.4, and 9.5 all fail with "cached plan must not change
result type" error.

What is the ideal behavior, in your view?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2016-01-25 12:39:29 -0500, Robert Haas wrote:

What is the ideal behavior, in your view?

FWIW, I think that for a lot of practical cases the previous behaviour,
where a prepared statement was defined in the context of the search path
set during the PREPARE, made a lot more sense. The current behaviour
makes a few corner cases (dropped, or relations moved between schemas)
simpler, while making real world things harder (different parts of an
application using different search paths, drivers, increase in planning
time).

Andres

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jan 25, 2016 at 12:47 PM, Andres Freund <andres@anarazel.de> wrote:

On 2016-01-25 12:39:29 -0500, Robert Haas wrote:

What is the ideal behavior, in your view?

FWIW, I think that for a lot of practical cases the previous behaviour,
where a prepared statement was defined in the context of the search path
set during the PREPARE, made a lot more sense. The current behaviour
makes a few corner cases (dropped, or relations moved between schemas)
simpler, while making real world things harder (different parts of an
application using different search paths, drivers, increase in planning
time).

That's a defensible position, but Vladimir didn't seem to like
*either* behavior.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund <andres@anarazel.de> writes:

On 2016-01-25 12:39:29 -0500, Robert Haas wrote:

What is the ideal behavior, in your view?

FWIW, I think that for a lot of practical cases the previous behaviour,
where a prepared statement was defined in the context of the search path
set during the PREPARE, made a lot more sense.

That argument is defensible probably for explicit PREPARE, but not at all
so for preparing done behind the scenes, as plpgsql does it, or as jdbc
does it. The previous behavior amounted to silent inconsistency for
plpgsql users; statements that weren't obviously different might be
executed with the current search_path or with some previous setting.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I want to treat 'prepare' operation as an optimization step, so it is functionally equivalent to sending a query text.

In other words, I would like backend to track search_path and other parameters if necessary transparently‎, creating (caching) different execution plans if different plans are required.
‎
Does that make sense?‎
‎
Vladimir‎

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jan 25, 2016 at 2:11 PM, Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:

I want to treat 'prepare' operation as an optimization step, so it is functionally equivalent to sending a query text.

In other words, I would like backend to track search_path and other parameters if necessary transparently‎, creating (caching) different execution plans if different plans are required.
‎
Does that make sense?‎

Hmm, so in your example, you actually want replanning to be able to
change the cached plan's result type?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Monday, January 25, 2016, Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
wrote:

I want to treat 'prepare' operation as an optimization step, so it is
functionally equivalent to sending a query text.

In other words, I would like backend to track search_path and other
parameters if necessary transparently‎, creating (caching) different
execution plans if different plans are required.
‎
Does that make sense?‎
‎

Prepare creates a plan and a plan has a known output structure. What you
want is an ability to give a name to a parsed but unplanned query. This is
not something that prepare should do as it is not a natural extension of
its present responsibility.

Maybe call the new command "PARSE name AS query".

Subsequent prepare commands could refer to named parsed commands to
generate an execution plan in the current context. If the current context
matches a previously existing plan the command would effectively become a
no-op. Otherwise a new plan would be generated. Or, more simply, using
execute and a named parsed query would implicitly perform prepare per the
description above.

I'm not sure how different this is from writing views...though it can be
used for stuff like updates and deletes as well. You can, I think, already
get something similar by using set from current with a function...

David J.

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?

Vladimir

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jan 28, 2016 at 7:48 AM, Vladimir Sitnikov <
sitnikov.vladimir@gmail.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.

On Wed, Jan 27, 2016 at 10:18 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

Prepare creates a plan and a plan has a known output structure. What you
want is an ability to give a name to a parsed but unplanned query. This is
not something that prepare should do as it is not a natural extension of its
present responsibility.

The distinction you're talking about here actually does exist at the
Protocol level. You can send a Parse message to create a prepared
statement (which is parsed but unplanned), a Bind message to create a
portal (which is planned), and then you can send an Execute message to
execute a previously-created portal.

However, I'm not really sure this helps. It seems like what Vladimir
wants is basically automatic plan caching. He wants the server to
re-parse-analyze and re-plan the statement any time that would produce
a different outcome, but ideally also consider holding onto the old
plan in case the search_path or whatever is switched back. I gather
that the reason he wants to use prepared statements at all is just to
minimize parse-plan overhead.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers