Обсуждение: [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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14483: Prepared statement does not re-plan while it should
От
"David G. Johnston"
Дата:
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.
cnliou9@fastmail.fm writes:
> 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
This behavior is intentional: it's to keep applications from having to
deal with the possibility that they prepare a statement, Describe it
to find out what columns it returns, and then when they actually execute
it, it returns some other column set.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
> This behavior is intentional: it's to keep applications from having to > deal with the possibility that they prepare a statement, Describe it > to find out what columns it returns, and then when they actually execute > it, it returns some other column set. Many thanks for the clarification! I am not sure if it is appropriate to turn this bug report into feature request, again. We will get several results by searching key phrase "cached plan must not change result type". It will be much easier for me to use prepared statements if they are automatically re-parsed after any DDL that affects them or "SET SEARCH_PATH TO" is executed. I believe this is especially true where connection pool products are used. Even without using connections pooling, the following example shows that simple usage can also cause production applications to malfunction - they get the error when developers are also changing the database schema: ==== test=# CREATE TABLE t1 (c1 INTEGER); CREATE TABLE test=# PREPARE p1 AS SELECT c1 FROM t1; PREPARE test=# EXECUTE p1; c1 ---- (0 rows) test=# ALTER TABLE t1 ALTER c1 TYPE TEXT; ALTER TABLE test=# EXECUTE p1; ERROR: cached plan must not change result type ==== Once an application encounters this problem, disconnecting from the database and reconnecting back, which usually means restarting the application, seems to be the only way to "fix" it. Although deallocating prepared statements wherever necessary ultimately avoids this issue, it is complicated for me to correctly implement such applications. As a result, I currently avoid using prepared statements altogether and hence obviously greatly degrade the overall performance. Best Regards, CN -- http://www.fastmail.com - Choose from over 50 domains or use your own -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
2017-01-03 8:25 GMT+01:00 CN <cnliou9@fastmail.fm>:
> This behavior is intentional: it's to keep applications from having to
> deal with the possibility that they prepare a statement, Describe it
> to find out what columns it returns, and then when they actually execute
> it, it returns some other column set.
Many thanks for the clarification!
I am not sure if it is appropriate to turn this bug report into feature
request, again. We will get several results by searching key phrase
"cached plan must not change result type".
It will be much easier for me to use prepared statements if they are
automatically re-parsed after any DDL that affects them or "SET
SEARCH_PATH TO" is executed. I believe this is especially true where
connection pool products are used.
Is good to switch SEARCH_PATH only on session start - or reset session before.
Regards
Pavel
Even without using connections pooling, the following example shows that
simple usage can also cause production applications to malfunction -
they get the error when developers are also changing the database
schema:
====
test=# CREATE TABLE t1 (c1 INTEGER);
CREATE TABLE
test=# PREPARE p1 AS SELECT c1 FROM t1;
PREPARE
test=# EXECUTE p1;
c1
----
(0 rows)
test=# ALTER TABLE t1 ALTER c1 TYPE TEXT;
ALTER TABLE
test=# EXECUTE p1;
ERROR: cached plan must not change result type
====
Once an application encounters this problem, disconnecting from the
database and reconnecting back, which usually means restarting the
application, seems to be the only way to "fix" it.
Although deallocating prepared statements wherever necessary ultimately
avoids this issue, it is complicated for me to correctly implement such
applications. As a result, I currently avoid using prepared statements
altogether and hence obviously greatly degrade the overall performance.
Best Regards,
CN
--
http://www.fastmail.com - Choose from over 50 domains or use your own
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs