"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.
