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

The following bug has been logged on the website:

Bug reference:      14483
Logged by:          CN Liou
Email address:      cnliou9@fastmail.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