Re: BUG #14483: Prepared statement does not re-plan while it should

On Mon, Jan 2, 2017 at 8:40 AM, <cnliou9(at)fastmail(dot)fm> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14483
> Logged by: CN Liou
> Email address: cnliou9(at)fastmail(dot)fm
> PostgreSQL version: 9.6.0
> Operating system: Debian Wheezy
> Description:
>
> The documentation
>
> file:///usr/share/doc/postgresql-doc-9.6/html/sql-prepare.html
>
> says so:
>
> "PostgreSQL will force re-analysis and re-planning of the statement before
> using it whenever database objects used in the statement have undergone
> definitional (DDL) changes since the previous use of the prepared
> statement.
> Also, if the value of search_path changes from one use to the next, the
> statement will be re-parsed using the new search_path. (This latter
> behavior
> is new as of PostgreSQL 9.3.)"
>
> ==first test===
> test=# CREATE TABLE x1 (c1 INTEGER);
> CREATE TABLE
> test=# PREPARE p1 AS SELECT * FROM x1;
> PREPARE
>
> (
> Run this SQL in another session:
> ALTER TABLE x1 ADD c2 TEXT;
> )
>
> test=# EXECUTE p1;
> ERROR: cached plan must not change result type
>
> ==second test===
>
> test=# SET SEARCH_PATH TO s1;
> SET
> test=# CREATE TABLE x2(c1 INTEGER);
> CREATE TABLE
> test=# PREPARE p2 AS SELECT * FROM x1;
> PREPARE
> test=# EXECUTE p2;
> c1
> ----
> (0 rows)
>
> (
> Run the following SQL's in another session:
>
> test=# SET SEARCH_PATH TO s2;
> SET
> test=# CREATE TABLE x2(c1 INTEGER,c2 TEXT);
> CREATE TABLE
> )
>
> test=# SET SEARCH_PATH TO s2;
> SET
> test=# EXECUTE p2;
> ERROR: cached plan must not change result type
>
>
​IIRC the fundamental difference here is that while a query will be
re-planned it will not be re-parsed - and the column structure of the
output is determined at parse time and hence must remain constant.

Your second example should work if the x2 in schema s2 has an identical
structure to s1.x1.

In the first, the use of "*" results in the select list results in
fragility.

David J.