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

From David G. Johnston
Subject Re: [BUGS] BUG #14483: Prepared statement does not re-plan while it should
Date
Msg-id CAKFQuwbn0C0us=SgXjtv6OnnU288aS6Ermd_5=bNDMvgGsLtyQ@mail.gmail.com
Whole thread Raw
In response to [BUGS] BUG #14483: Prepared statement does not re-plan while it should  (cnliou9@fastmail.fm)
List pgsql-bugs
On Mon, Jan 2, 2017 at 8:40 AM, <cnliou9@fastmail.fm> wrote:
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


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

pgsql-bugs by date:

Previous
From: cnliou9@fastmail.fm
Date:
Subject: [BUGS] BUG #14483: Prepared statement does not re-plan while it should
Next
From: Tom Lane
Date:
Subject: Re: [BUGS] BUG #14483: Prepared statement does not re-plan while it should